Solved

Access 2003 Query By Form Using Mulitiple Choices for Date Range

Posted on 2013-01-09
10
301 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

706 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

15 Experts available now in Live!

Get 1:1 Help Now