473,320 Members | 1,900 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Date/Time criteria for a Report

283 100+
Hello,

Im having some trouble trying to figure out how to make a report run based on a date and time range.

What I have is a Form with 4 text boxes and a print report button. What I want to do is enter in a date and time range and then run a report based on that information. So I have Two text boxes that are start date and end date and two that are start time and end time. I have a query Criteria set to look at the form and the boxes problem is that I can not get it to look at all 4 boxes its only 2 or 2 any way i can get it to look at all 4?

Appreciate the help on this.

Here is the code I have so far in the query criteria
Expand|Select|Wrap|Line Numbers
  1.  
  2. Between [Forms]![DateReport]![StartDate]And[Forms]![DateReport]![EndDate]'this works
  3.  
  4. 'have tried variations but no luck so far
  5.  
  6. Between [Forms]![DateReport]![StartDate]And[Forms]![DateReport]![StartTime]And [Forms]![DateReport]![EndDate]And[Forms]![DateReport]![EndTime]
  7.  
  8. Between [Forms]![DateReport]![StartDate]And[Forms]![DateReport]![StartTime]And Between [Forms]![DateReport]![EndDate]And[Forms]![DateReport]![EndTime]
  9.  
  10.  
Sep 18 '10 #1
4 1781
liimra
119 100+
There is a problem in the two variations you stated. You are trying to get information between timefield and datefield!

This how it should be logically speaking

Expand|Select|Wrap|Line Numbers
  1. Between [Forms]![DateReport]![StartDate]And[Forms]![DateReport]![EndDate]And Between [Forms]![DateReport]![StartTime]And[Forms]![DateReport]![EndTime]

Regards,
Ali
Sep 18 '10 #2
slenish
283 100+
Hi liimra,

Thanks for the reply. Well your suggestion half worked. When I input the information on the form and run the report it pulls the report with no problem now but for some reason the report always comes back with no information.

Any ideas as to why? I have tried a lot of ideas such as adding code in to the date field on the report, I tried reformating the column in the table to date/time, just date, military time and still nothing. Cant figure out what the problem is.

Thanks again for the help.
Sep 21 '10 #3
colintis
255 100+
how about setting clear the date format from vba code? such as
Expand|Select|Wrap|Line Numbers
  1. Dim StartDate As Date
  2. Dim EndDate As Date
  3. StartDate = Date([Forms]![DateReport]![StartDate], "mm/dd/yyyy")
  4. EndDate = Date([Forms]![DateReport]![EndDate], "mm/dd/yyyy")
  5.  
  6. ....Between #" & StartDate & "# AND #" & EndDate & "#"
And the time
Expand|Select|Wrap|Line Numbers
  1. Dim StartTime as Date
  2. Dim EndTime as Date
  3. StartTime = TimeValue([Forms]![DateReport]![StartTime])
  4. EndTime = TimeValue([Forms]![DateReport]![EndTime])
  5.  
  6. ....Between '" & StartTime & "' AND '" & EndTime & "'"
Notice TimeValue will convert the time to format (HH:MM:SS AM/PM), so for example:
Expand|Select|Wrap|Line Numbers
  1. TimeValue ("3:12:57 PM")would return 3:12:57 PM
  2. TimeValue ("15:12:57")    would return 3:12:57 PM
Sep 21 '10 #4
liimra
119 100+
The problem is not with the format or context but with the filters & scenarios.

This would happen if you are leaving one of the two criteria null. Usually when you have more than one criteria you have to assume different scenarios. For example If date is selected then filter based on date or If date & time are selected then filter based on them.

In order to overcome this, you have to define the other scenarios you might have. In your example they are three: The fitler can be date, time or both. Since you only have two time/date filters you can use simple approach:

Expand|Select|Wrap|Line Numbers
  1.  If [DateOne] Is Not Null And [DateTwo] Is Not Null and [TimeOne] Is Not Null And [TimeTwo] Is Not Null Then
  2. DoCmd.OpenReport .... Criteria = Time & Date
  3. ElseIF [DateOne] Is Not Null And [DateTwo] Is Not Null and [TimeOne] Is Null And [TimeTwo] Is Null Then
  4. DoCmd.OpenReport .... Criteria = Only Date
  5.  
  6. ....and so on 
  7. Else
  8. MsgBox "Choose Criteria"
  9. End If
Please find this approach in the attached database. Note that you have to select "from & to" Date or "from & to" Time or Both, otherwise if you choose just "From" date only you will have MsgBox "Choose Criteria".



Regards,
Ali
Attached Files
File Type: zip Time&Date.zip (23.4 KB, 79 views)
Sep 21 '10 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: Adrian Parker | last post by:
Hi. I have a date time picker in my program which uses ADO to read from an Access database. It works perfectly, unless the database is empty (no records) when opened. When you try to open an...
1
by: Scott Dunham | last post by:
I'm trying to select records between 2 different times, but I can't seem to get Access to see anything other than the date. For instance, criteria "Between #11/12/2001 1:45:00 PM# and #11/12/2001...
5
by: Javier | last post by:
I have a field type Date/Time that automatically defaults to Now(). I'm having a problem writing a query that will retrieve all the records for a specific date the user to enters when prompted. I...
1
by: Jacquo Johnson | last post by:
I am receiving the following error: MS Access Date/Time DBD::ODBC::st execute failed: , , " . "FROM WHERE > '2004/02/01 11:16:16'"; $sth = $dbh->prepare($sqlstatement); $sth->execute ||...
1
by: John Feeley | last post by:
am tring to add a number of years to a dob. im doing this by adding my date+years*365.26 I get a string of numbers. I then convert the number in the next column to actual date again. I'm getting...
11
by: Dixie | last post by:
How can I programatically, take some Date/Time fields present in a table in the current database and change their type to text? dixie
2
by: john | last post by:
From an Excel-import I have an access table with a datetime field of wich both date and time are entered. I've set the field's notation properties to Short Date. When I view the table in tableview...
1
by: Susan Bricker | last post by:
Greetings. I have a report (actually all of my reports in an MDB) that I want to date/time stamp at the bottom. Previously, I had used the builtin function of Now(). I thought that would give...
5
by: Stan | last post by:
I am working on a database in ACCESS 2003. Six of the fields provide for entry of service dates 1 to 6 progressively recording when the client was serviced. The data type is Date/Time. I need to...
4
by: Midsy | last post by:
I simply want the Criteria code to have a query show data that is more than one year old. Simple enough in Excel =NOW()-365 but Data()-365 does not work in MS Access Thank you for the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.