Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

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
0
jtflex
Asked:
jtflex
  • 5
  • 5
1 Solution
 
Jeffrey CoachmanCommented:
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
 
jtflexAuthor Commented:
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
 
Jeffrey CoachmanCommented:
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
jtflexAuthor Commented:
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
 
Jeffrey CoachmanCommented:
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
 
jtflexAuthor Commented:
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
 
jtflexAuthor Commented:
Sorry, I finished attaching it now.

Joe
Incident.mdb
0
 
Jeffrey CoachmanCommented:
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
 
jtflexAuthor Commented:
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
 
Jeffrey CoachmanCommented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now