asked on Access 2003: Query By Forms When Date Field Is Skipped by User
I am building some queries using a Query By Form method.
I am building them so the user can select 1 or more unbound texbox fields in the search form (called QBF_Incident) to determine the paramters. Any field left blank shoudl be excluded from the parameters.
The query I am having issues with involves a date paramter. If the user enter in a Date field along with the other fields (or leave the other field empty) all is fine.
However, if the date field is left empty (and the other field do or do not have an entry), a error code is generated (Run-time error '2001'; You canceled the previous operation.) What should happen is if the date is empty, return the records for any date.
The acutal Query Date field consists of the following:
Field: ReportedDate
Table: Incident
Sort:
Show: (Is checked)
Criteria: [Forms]![QBF_Incident].[Wh
The other field has the following:
Field: TypeCall
Table: Incident
Sort:
Show: (is checked)
Criteria: Like [Forms]![QBF_Incident].[Wh
The (QBF_Incident) form used has a search button using VBA to run this query and I have a reset button that resets the values to "".
I have a similar query that doens't include dates that works fine.
What do I have to have different when ignoring dates as parameters?
Thanks,
Joe
I am building them so the user can select 1 or more unbound texbox fields in the search form (called QBF_Incident) to determine the paramters. Any field left blank shoudl be excluded from the parameters.
The query I am having issues with involves a date paramter. If the user enter in a Date field along with the other fields (or leave the other field empty) all is fine.
However, if the date field is left empty (and the other field do or do not have an entry), a error code is generated (Run-time error '2001'; You canceled the previous operation.) What should happen is if the date is empty, return the records for any date.
The acutal Query Date field consists of the following:
Field: ReportedDate
Table: Incident
Sort:
Show: (Is checked)
Criteria: [Forms]![QBF_Incident].[Wh
The other field has the following:
Field: TypeCall
Table: Incident
Sort:
Show: (is checked)
Criteria: Like [Forms]![QBF_Incident].[Wh
The (QBF_Incident) form used has a search button using VBA to run this query and I have a reset button that resets the values to "".
I have a similar query that doens't include dates that works fine.
What do I have to have different when ignoring dates as parameters?
Thanks,
Joe
Microsoft Access
Last Comment
Gustav Brock
Maybe
Criteria:Iif(isnull([Forms
Criteria:Iif(isnull([Forms
Nope.
try this criteria
criteria : nz([Forms]![QBF_Incident].
criteria : nz([Forms]![QBF_Incident].
Upload a sample database.
You can use:
Criteria: IIf([Forms]![QBF_Incident]
/gustav
Criteria: IIf([Forms]![QBF_Incident]
/gustav
jtflex,
As you can see, there are a lot of ways to approach this.
In fact, the posts made after mine were a bit more refined than my quick suggestion.
So I'll back out now and let you continue with the existing experts.
;-)
Jeff
As you can see, there are a lot of ways to approach this.
In fact, the posts made after mine were a bit more refined than my quick suggestion.
So I'll back out now and let you continue with the existing experts.
;-)
Jeff
ASKER
Thanks for all the responses, I tried two variations, one from jerryb30 and the other from cactus-data as follows:
IIf([Forms]![QBF_Incident]
IIf(IsNull([Forms]![QBF_In
I no longer get a error message, however, no data is returned either. If a put a date in the form, I do get data back so I know the 2nd part of this IIF statement is working.
Any thoughts?
Joe
IIf([Forms]![QBF_Incident]
IIf(IsNull([Forms]![QBF_In
I no longer get a error message, however, no data is returned either. If a put a date in the form, I do get data back so I know the 2nd part of this IIF statement is working.
Any thoughts?
Joe
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks both solutions work perfectly, I am giving more points to cactus-data as his solution was posted eariler, I just failed to understand it.
But thank you also capricorn1 and all the others out there who helped me with this also.
But thank you also capricorn1 and all the others out there who helped me with this also.
You are welcome!
/gustav
/gustav
<Any field left blank shoudl be excluded from the parameters.>
...One quick technique to ignore a criteria if it is left empty:
Like "*" & [Forms]![QBF_Incident].[Wh
Does this *Have* to be done by query by form?
There are many other ways to do what you are asking...