Date Conversion from General Date to Short Date

[Guest]

I have a table where the DateStamp is imported from an external source as a
General Date (mm/dd/yyyy 00:00:00). I built a query that has date
parameters, but because the date is not a short date, my parameters aren't
recognized. I need a way to convert the date in a query to only return the
short date format (mm/dd/yyyy) so my date parameters will run. I've tried
the DateValue() function, but can't get it to work (haven't seen an
applicable example).

Here is the query I am running:

SELECT tbl_Data.*, tbl_Data.DateStamp
FROM tbl_Data
WHERE (((tbl_Data.DateStamp) Between #3/1/2007# And #4/30/2007#));

Thanks for any help anyone can provide!!

B

 

[Allen Browne]

Brian, the most efficient solution would be to ask for records that are less
than the next day.

For example:
PARAMETERS [What date] DateTime;
SELECT tbl_Data.*, tbl_Data.DateStamp
FROM tbl_Data
WHERE (([tbl_Data].[DateStamp] >= [What date])
And ([tbl_Data].[DateStamp] < ([What date] + 1)));

 

[John Spencer]

What type of field is DateStamp? Is it a string that looks like a date or
is it an actual datetime field?

If the field is a string field or a datetime field you should be able to use
the following.

WHERE DateValue(tbl_Data.DateStamp) Between ... and ...

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John W. Vinson]

I have a table where the DateStamp is imported from an external source as a
General Date (mm/dd/yyyy 00:00:00). I built a query that has date
parameters, but because the date is not a short date, my parameters aren't
recognized. I need a way to convert the date in a query to only return the
short date format (mm/dd/yyyy) so my date parameters will run. I've tried
the DateValue() function, but can't get it to work (haven't seen an
applicable example).

Here is the query I am running:

SELECT tbl_Data.*, tbl_Data.DateStamp
FROM tbl_Data
WHERE (((tbl_Data.DateStamp) Between #3/1/2007# And #4/30/2007#));

Thanks for any help anyone can provide!!

B

It sounds like you may have defined DateStamp as a Text field rather than a
Date/Time. A Date/Time field isn't stored as a string, and the format is
irrelevant - it's actually stored as a Double Float count of days and
fractions of a day since midnight, December 30, 1899. You can format it any
way you please and the searches will work.

Try using CDate([DateStamp]) or, better, change the fieldtype from text to
date.

John W. Vinson [MVP]