• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 443
  • Last Modified:

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
0
jtflex
Asked:
jtflex
  • 3
  • 2
  • 2
  • +3
2 Solutions
 
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
 
jerryb30Commented:
Nope.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
Gustav BrockCIOCommented:
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
 
Rey Obrero (Capricorn1)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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now