Solved

Access 2003 Query By Form Using Mulitiple Choices for Date Range

Posted on 2013-01-09
10
302 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
  • 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
 

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
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.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now