Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 18th, 2008, 08:55 AM
Registered User
 
Join Date: Feb 2008
Location: College Point, NY, USA.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default Parameter Query Help

I have a table that contains a filed called "Accnt_Code", the data in this field is from 1A to 9Z, what I need to do is create a parameter query, where the user may input, for example a group of Accnt_Codes, for example 1A to 1Z, and also be able to input individual Accnt_Codes, for example 2A,3B,4G - it this possible in access?
 
Old February 19th, 2008, 12:04 AM
Friend of Wrox
 
Join Date: Oct 2007
Location: , , .
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
Send a message via AIM to urtrivedi
Default

I think It is not possible in directly using query. you can do it using VBA.

urt

Help yourself by helping someone.
 
Old February 19th, 2008, 07:54 AM
Registered User
 
Join Date: Feb 2008
Location: College Point, NY, USA.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

is there some place you direct me to, that can help with this?

 
Old February 19th, 2008, 11:24 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I think you need to use a list box, and allow multi select, and then the user can select one or many of the parameters. You will also need to use two combo boxes, one to select the bottom of the range, like 1A, and one to select the top of the range, like 1Z, and then pass the parameters to your report.

For the top and bottom of the ranges, assuming you are running a report called "rptMyReport," try this:

Dim sStart As String
Dim sEnd As String
Dim sLink As String
Dim sDoc As String

sDoc = "rptMyReport"
sStart = Me.cboRangeStart
sEnd = Me.cboRangeEnd

sLink = "[Accnt_Code] Between '" & sStart & "' And '" & sEnd & "'"

DoCmd.OpenReport sDoc, acPreview, , , sLink

The sLink should be in the WHERE clause section of the DoCmd. line.

I can help with the code for a multi-select box if you need that. You should also validate entries in the combo boxes, and you should also use a cascading combo box so that when the user selects the start of the range, they are limited to the end of the range to only those items after the start of the range, to prevent 1C to 1A sort of entries.

Did that help?



mmcdonal

Look it up at: http://wrox.books24x7.com
 
Old February 19th, 2008, 01:36 PM
Registered User
 
Join Date: Feb 2008
Location: College Point, NY, USA.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It is making some kind of sense to me, my knowledge of access is limited..do I enter this in the onclick event of my list box? Thank you for your help and quick response.

 
Old February 19th, 2008, 01:50 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

The code I posted is just for two combo boxes to select the start and end of the range. You should put the code on the on click event of the button the user clicks to run the report.

Use the wizard to create the button, and then modify the button to pass the criteria.

mmcdonal

Look it up at: http://wrox.books24x7.com




Similar Threads
Thread Thread Starter Forum Replies Last Post
passing parameter to query vladimir Access 6 August 30th, 2006 05:39 AM
XSL - Parameter query. Neal XSLT 4 January 24th, 2006 01:27 PM
Parameter Query Teqlump Access 4 November 11th, 2004 07:21 PM
Non query Report Parameter qa BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 0 July 2nd, 2004 02:49 PM
Parameter Query Ben Access VBA 1 June 27th, 2003 12:13 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.