Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.
Course Of The Month
7 days, 23 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Access 2003: Query By Forms When Date Field Is Skipped by User
Posted on 2013-01-07
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
[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
Active 3 days ago
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].[Wh
Does this *Have* to be done by query by form?
There are many other ways to do what you are asking...
<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...
Maybe
Criteria:Iif(isnull([Forms
Criteria:Iif(isnull([Forms
Active 1 day ago
You can use:
Criteria: IIf([Forms]![QBF_Incident]
/gustav
Criteria: IIf([Forms]![QBF_Incident]
/gustav
Active 3 days ago
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
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
Active 1 day ago
Try mine:
IIf([Forms]![QBF_Incident]
or just:
IIf([Forms]![QBF_Incident]
/gustav
IIf([Forms]![QBF_Incident]
or just:
IIf([Forms]![QBF_Incident]
/gustav
Active today
try this
IIf(IsNull([Forms]![QBF_In
or
IIf(IsNull([Forms]![QBF_In
IIf(IsNull([Forms]![QBF_In
or
IIf(IsNull([Forms]
Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents. The steps to find the Templates folder path are …
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 Microsoft Access, learn how to specify relationships between tables and set various options on the relationship.
Add the tables:
Create the relationship:
Decide if you’re going to set referential integrity:
Decide if you want cascade upda…
Suggested Courses
Course of the Month7 days, 23 hours left to enroll
729 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.