ansaurus

Question

select statement within selected days MS Access

Answer 1

A:

I don't know the internal format for dates in MS Access, but the discussion is the same whether it's a float (like Excel) or an integer (like a Unix-date). Assuming they're represented as integers, a number of seconds...

RegDate is some number, xxx.

TmpStamp would be xxx + 13*3600.

#01/01/2001# is the same number xxx. #02/01/2001# is xxx + 24*3600.

Comparing TmpStamp to a date won't truncate its value to a date (i.e. xxx).

pascal 2010-09-21 02:52:05

both are date/time format

william 2010-09-21 03:03:22

and.. the correct way to solve the problem?

william 2010-09-21 03:04:24

Try using [Int()](http://support.microsoft.com/kb/210276)?

pascal 2010-09-21 03:41:22

While dates are stored as numbers (float), Access is quite happy to accept date format input, as long as the input is ether US or an unambiguous format. Microsoft are not so mean as to force you to calculate the number of days for a simple query :)

Remou 2010-09-21 10:17:07

To draw out what @Remou said in his comment: you don't need to use Int() to work with date values without dates. All you need to do is exactly what he said, which is to format the string representations of date values (whatever you put between ##) either in US format or in an unambiguous format, like Long Date or ISO format. Using Int() will cause performance problems because it won't be able to use the indexes on the date fields.

David-W-Fenton 2010-09-22 18:31:10

Answer 2

+1 A:

In MS Access a Date/Time field always has a Time component. The RegData field has time of 00:00:00 so Access is not displaying it to you and the following query will work

SELECT * FROM TABLE WHERE RegDate>=#01/01/2001# and RegDate<=#01/01/2001#

You first TmStamp query is actually running this

SELECT * FROM TABLE WHERE TmStamp >=#01/01/2001 00:00:00# and TmStamp <=#01/01/2001 00:00:00#

As the TmStamp field has times it will not show any results.

What you need to do for Date/Time fields with a time other than 00:00:00 is run this query

SELECT * FROM TABLE WHERE TmStamp >=#01/01/2001# and TmStamp <#02/01/2001#

Croberts 2010-09-21 09:22:54

The problem is date format, not timestamp. This is a standard problem for those of us not in a US time format locale.

Remou 2010-09-21 10:14:34

actually nothing to do with format since I hav aldy solved Formatting problem. So.. wht I do is.. I get the Date from TmStamp and change it to integer. Then I check.. That's not a gd way but.. it works.. At least for now.. So I think the Time part is the correct one..

william 2010-09-22 06:08:51

You don't need to be mucking about with extracting the Int() value. That will cause performance problems because it can't use the indexes.

David-W-Fenton 2010-09-22 18:32:16

Answer 3

A:

Queries in Access must either use US date or an unambiguous format such as ANSI/ISO. A yyyy/mm/dd format is to be preferred. That being said, if you build your query in the query design window, you have a little more latitude, in that Access will change the date format for you. The time component is nether here nor there in this matter. Dates are all stored as numbers, so even if your dates show as dd/mm/yyyy, your SQL should read:

SELECT * FROM TABLE WHERE TmStamp >=#2001/01/01# and TmStamp <=#2001/01/02#

Remou 2010-09-21 10:13:14

ansaurus

tags:

views:

answers:

select statement within selected days MS Access

related questions