asked on

Access 2003 Query By Form Using Mulitiple Choices for Date Range

Thanks to the help of this site I am continuing to improve on some queries I have.

An issue I am trying to resolve now is to get a date range working. I have two unbounded textboxes on a form for the user to enter in a start date and end date (GetSDate, and GetEDate).
I also created a 3rd unbounded text to store the results of what is entered by the user (DateSrch).

I want the user to have several options:

1. If no dates are entered bring back all records
2. If a Start Date and End Date are entered, bring back all records between (inclusive)
3. If only a Start Date is entered bring back all records on that date and forward
4. If only a End Date is entered bring back all records on that date and before.

In the form, I built into a run button the following code that populates the "DateSrch" textbox:

Dim strWhere As String
If IsDate(Me.GetSDate) Then
    strWhere = ">=" & "#" & Me.GetSDate & "#"
End If
If IsDate(Me.GetEDate) Then
    If strWhere <> vbNullString Then
        strWhere = strWhere & " And "
    End If
    strWhere = strWhere & "<=" & "#" & Me.GetEDate & "#"
End If
Me.DateSrch = strWhere

Open in new window

This part is working fine and is displaying the different choices as expected:
{empty}
>=#1/1/2013#
<=#1/5/2013#
>=#1/1/2013# And <=#1/5/2013#

In the query I have the following statement under the Criteria:

IIf([Forms]![qbf_Incident_Search].[DateSrch]="",([Incident].[Reported_Date]),[Forms]![qbf_Incident_Search].[DateSrch])

Leaving the dates empty brings back all the records as expected. Entering any dates causes the query to halt with an error. If I copy and paste the data displayed in the "DateSrch" directly to the Criteria of the query it brings back the correct results as well.

I'm sure what I am trying to do should work but I can't see where I am going wrong?

Any help would be greatly appreciated.

Thanks,
Joe

Jeffrey Coachman

Not really a complete solution, but try this instead:

IIf(Isnull([Forms]![qbf_Incident_Search].[DateSrch]),([Incident].[Reported_Date]),[Forms]![qbf_Incident_Search].[DateSrch])

Joseph LaFlex

ASKER

Thanks boag2000, I tried that but I get the same results. Also, the 1st part of my statement did work. If I left the dates empty, it would return all the records. It's the second part of the statement that appears to crash.
I used the { ="" } instead of Is Null becaused my textbox is set with a default value of "".

Joe

Jeffrey Coachman

OK, then I am sure another expert will be along soon.

Just bear in mind that you are really asking 4 questions here, not one (this site was designed to handle one distinct question per post)

So it is not clear what the "One" answer here should be...

"Multi-part" questions are typically more effective if posted as sequential, individual questions.

JeffCoachman

Joseph LaFlex

ASKER

Thanks boag2000,

I guess it could be viewed as multiple questions but it really is one.

The code I wrote works in the sense that it populates the textbox that the query uses correctly (one of 4 options that I listed).

The one issue I have is trying to understand why my query crashes when it reads what is in that textbox.

I hope that helps to make sense for anybody reading this post.

Thanks again

Joe

Jeffrey Coachman

Then it is always a big help if you posted a sample of your database following these guidelines:

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Unhide any hidden database objects
10. Compile the code. (From the VBA code window, click: Debug-->Compile)
11. Run the compact/Repair utility.
12. Remove any Passwords and/or security.
13. If a form is involved in the issue, set the Modal and Popup properties to: No
(Again, unless these properties are associated with the issue)
14. Post the explicit steps to replicate the issue.
15. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

JeffCoachman

Joseph LaFlex

ASKER

I can't send you a sample of the actual datebase I am using as it has confidential data in it. Instead, I created a scalled down data base with the issue I am trying to resolve.

You will see the form I am using to run the query along with the query that has the coding in it as well.

thanks,
Joe

Joseph LaFlex

ASKER

Sorry, I finished attaching it now.

Joe
Incident.mdb

ASKER CERTIFIED SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Joseph LaFlex

ASKER

This is a very nice example. It it does work. Thank you for putting it together. I will use it as a guide to update what I am trying to do.

The example is only dealing with seaching based on a date range. My project will have move choices of data to search from (Nature of Call, Name, Address, etc.). But I think this example really points me in the right direction.

Thanks again.

Joe

Jeffrey Coachman

Just beware that the more criteria you add (to make it easier for the user) the more complex the code and interface will become for you to create.

At some point it is easier to just teach the users how to make their own queries...

JeffCoachman