Solved

Access 2003 Query By Form Using Mulitiple Choices for Date Range

Posted on 2013-01-09
10
306 Views
Last Modified: 2013-01-10
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
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
  • 5
  • 5
10 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38760879
Not really a complete solution, but try this instead:

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

Author Comment

by:jtflex
ID: 38760914
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38760994
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
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:jtflex
ID: 38761038
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
0
 
LVL 74

Expert Comment

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

Author Comment

by:jtflex
ID: 38761399
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
0
 

Author Comment

by:jtflex
ID: 38761402
Sorry, I finished attaching it now.

Joe
Incident.mdb
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 38761989
Then AFAICT, your current interface will make what you are asking for extremely complex.

This is why you should always state your ultimate goal in terms of just what you need, and perhaps not specify the interface you want to use.

If your interface is lacking, then you force us to use it, even if another interface would be more fitting.

For example, I would dump the ambiguous: "If Textbox A is blank, then..." interface, and replace it with something that makes it blatantly obvious to the user what is needed and what will be returned.
Then use a *Form* to display the results (where using a code generated Where Criteria is easy)

See this sample

I am sure that (based on your experience here) you can tweak this as needed.

JeffCoachman
AccessEEQ27990287MultipleFormDat.mdb
0
 

Author Closing Comment

by:jtflex
ID: 38763566
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
0
 
LVL 74

Expert Comment

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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

615 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