Date Query

Microsoft Access 2003 (full product)
September 20, 2009 at 03:40:14
Specs: Windows XP
This is embarrassing from the complex questions I have seen. I am doing a query and need to be able to select a specific date. I am using the input [enter date]. Problem is the data is stored in dd/mm/yyy hh:mm:ss AM/PM format. Finally figured out that when I only input a date, it comes back with nothing. I need to have all records from 00:00:00 am to 23:59:59 for that date. How do I format the criteria. Thanks so much from a novice.

See More: Date Query

Report •

September 20, 2009 at 05:18:43

There are three main things to know about Dates and Times in Excel
1. Excel holds dates and times as a single decimal number, with the decimal part as the time from 0 to almost 1.
0.5 is 12 noon , i.e. half way through the day.
The integer or whole number part counts days since the start of 1900. 20 September 2009 is held as 40076 and 21 September 2009 will be 40077

2. What you see in a cell depends on how you format it. Formating such as "dd/mm/yyy hh:mm:ss AM/PM"
does not change the value stored.

3. When you enter data into a cell, Excel tests it for several things, including whether it looks like a date or time. If you enter 10:15 Excel will consider it a time and save it as 0.42708333 and format the cell as "h:mm". (Format may vary depending on default date and time settings - but you get the basic idea)

In your case you need to enter dates/times in a way that Excel recognizes. Time is pretty easy - stick to 10:30 or 10:30:02 if you want seconds as well.

Dates entered in a format such as 20-Sep-09 work fine, and Excel considers this to be 2009.

To find all entries that have times within a certain day try something like this:
In A1 enter 20-Sep-09 00:00:00
In B1 enter 20-Sep-09 23:59:59
Format both as "dd-mmm-yyyy"
A1 & B1 will read 20-Sep-2009
Just to check it worked, in cell C1 put =B1-A1
Format C1 as a number with two decimal places
C1 will show 1.00
This means that there is one day difference between A1 and B1
Now you have the start and end value for 20 September 09

With those two values you can test any date and time entered to see if it is a time on 20 September

Any calculations of dates and times can use the standard + - = < > operators. Excel will do the calculation using the numbers it stored.

For example if you entered 20-Sep-09 12:00 in cell A2
enter this formula in cell B2
=IF(AND(A2>=$A$1,A2<=$B$1),"Its Today","Its Not today")
The result will be "Its Today"
Try changing the date/time in Cell A2

I hope this gives you enough information to solve your issue.

Remember that what you see in a cell depends on the formatting, and Excel may have something different stored in the cell - and this is particularly true for date and time.


Report •

September 20, 2009 at 09:43:56
Hi Guys. A little more digging into the book and I found this. It really solved the problem.
Between [Please Enter Starting Date] And [Please Enter Ending Date]

Report •

September 20, 2009 at 10:58:50

I missed the point that your question was about Access not Excel


Report •

Related Solutions

Ask Question