Link to home
Start Free TrialLog in
Avatar of Joseph LaFlex
Joseph LaFlexFlag for United States of America

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].[WhatDate]

The other field has the following:
Field: TypeCall
Table: Incident
Sort:
Show: (is checked)
Criteria: Like [Forms]![QBF_Incident].[WhatIncident] & "*"

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
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

If I am understanding your request here:
<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].[WhatIncident] & "*"


Does this *Have* to be done by query by form?
There are many other ways to do what you are asking...
Maybe
Criteria:Iif(isnull([Forms]![QBF_Incident].[WhatDate]), >#1/1/1900#, [Forms]![QBF_Incident].[WhatDate])
Nope.
try this criteria


criteria : nz([Forms]![QBF_Incident].[WhatDate],>#1/1/1900#)
Upload a sample database.
You can use:

Criteria: IIf([Forms]![QBF_Incident].[WhatDate] Is Null,[YourQueryDateField],[Forms]![QBF_Incident].[WhatDate])

/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
Avatar of Joseph LaFlex

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].[WhatDate] Is Null,([Incident].[ReportedDate])>#1/1/1900#,[Forms]![QBF_Incident].[WhatDate])

IIf(IsNull([Forms]![QBF_Incident].[WhatDate]),([Incident].[ReportedDate])>#1/1/1900#,[Forms]![QBF_Incident].[WhatDate])

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
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
You are welcome!

/gustav