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
jtflexAsked:
Who is Participating?
Advertise Here
 
Gustav BrockConnect With a Mentor CIOCommented:
Try mine:

IIf([Forms]![QBF_Incident].[WhatDate] Is Null,[Incident].[ReportedDate],[Forms]![QBF_Incident].[WhatDate])

or just:

IIf([Forms]![QBF_Incident].[WhatDate] Is Null,[ReportedDate],[Forms]![QBF_Incident].[WhatDate])

/gustav
0
 
Jeffrey CoachmanMIS LiasonCommented:
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...
0
 
jerryb30Commented:
Maybe
Criteria:Iif(isnull([Forms]![QBF_Incident].[WhatDate]), >#1/1/1900#, [Forms]![QBF_Incident].[WhatDate])
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Start Now
 
jerryb30Commented:
Nope.
0
 
Rey Obrero (Capricorn1)Commented:
try this criteria


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

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

/gustav
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
jtflexAuthor Commented:
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
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this


IIf(IsNull([Forms]![QBF_Incident].[WhatDate]),([Incident].[ReportedDate]),[Forms]![QBF_Incident].[WhatDate])


or


IIf(IsNull([Forms]![QBF_Incident].[WhatDate]),[ReportedDate],[Forms]![QBF_Incident].[WhatDate])
0
 
jtflexAuthor Commented:
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.
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.