10-20-09, 23:03 #1Registered User
- Join Date
- Feb 2004
- Alpine Califormia
Unanswered: Parameters not giving correct values
I dont want the users to have to put all the parameters in if they dont want to, I would like to have a choice between them. If they want to put in last name then it gives me the correct results or with the date parameters or all the above
something is wrong with my select statement cause I can get the date range but not the last name
gives the correct date range but nto the last name. I use this in RS
SELECT TanfActivity_tbl.EventDate, TanfActivity_tbl.[Type of Contact], TanfActivity_tbl.[Purpose of Contact], TanfActivity_tbl.[Earned hours], TanfActivity_tbl.[Catagory for hours], People_tbl.[Parent ID], People_tbl.[Student First Name], People_tbl.[Student Last Name] FROM TanfActivity_tbl INNER JOIN People_tbl ON TanfActivity_tbl.[Parent ID] = People_tbl.[Parent ID] WHERE (TanfActivity_tbl.EventDate BETWEEN @Beginning_EventDate AND @End_EventDate) OR (People_tbl.[Student Last Name] = @Enter_StudentLastName)
10-21-09, 01:29 #2L33t Helpa Munky
- Join Date
- Nov 2007
- Adelaide, South Australia
I'd say you need some IF's happening.
I'd also recommend you use a custom dialog rather than using query parameters... it's a whole lot more friendly for your users.
A combination of both makes it flexible and user-friendly.
You can build your SQL based on your dialog box content with code such as:
If Me.txtStudentLastName > "" Then strSQL = strSQL & " AND StudentLastName = """ & Me.txtStudentLastName
That kind of thing.
If not you'll have to make use of IIf() but I think that may end up with worse situations, such as the system prompting for parameters more than once.Owner and Manager of
CypherBYTE, Microsoft Access Development Specialists.
Microsoft Access MCP.
And all around nice guy!
"Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
"...teach a man to code and he'll be frustrated for life! " -- georgev
10-21-09, 22:01 #3Moderator
- Join Date
- Dec 2004
- Madison, WI
I sometimes break these types of queries into separate queries. One filtering the date criteria and the other query using that query to further filter the data with additional criteria (ie. nested queries).Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)