Solved

Access 2003:  Query By Forms When Date Field Is Skipped by User

Posted on 2013-01-07
12
435 Views
Last Modified: 2013-01-09
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
Comment
Question by:jtflex
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
12 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38753374
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 38753376
Maybe
Criteria:Iif(isnull([Forms]![QBF_Incident].[WhatDate]), >#1/1/1900#, [Forms]![QBF_Incident].[WhatDate])
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38753405
Nope.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38753448
try this criteria


criteria : nz([Forms]![QBF_Incident].[WhatDate],>#1/1/1900#)
0
 
LVL 30

Expert Comment

by:hnasr
ID: 38753517
Upload a sample database.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 38753796
You can use:

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

/gustav
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38755231
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
 

Author Comment

by:jtflex
ID: 38755719
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
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 400 total points
ID: 38755741
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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 100 total points
ID: 38755745
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
 

Author Closing Comment

by:jtflex
ID: 38756262
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
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 38758045
You are welcome!

/gustav
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Email question 19 89
Access creating new db - relationships 8 54
MS Access - Autofill field with item description 6 49
ACCESS / VBA - Count of Rows in Table 4 43
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question