Access Date String

[GordonLiq]

Hi ,
Could anyone please tell me the different between date string
#25/02/08# and '25/02/08' ?
when I use
INSERT INTO tfyMixed (FeedDate, RationID, LoadNo) VALUES (#25/02/08#, NULL,
NULL)
The real Date has been saved is Feb,2,2025
and when I use
INSERT INTO tfyMixed (FeedDate, RationID, LoadNo) VALUES ('25/02/08', NULL,
NULL)
The real Date is Feb,25 ,2008
The result Feb,25,2008 is what want , but all the code in my problem that
write by the previous are using the #25/02/08#.

is there any where that I donot have to modify all the codes?

 

[Jeff Boyce]

Gordon

If you tell Access to insert #xx/xx/xx#, it interprets the "#"s to mean you
are using a date/time data type.

If you tell Access to insert 'xx/xx/xx', it interprets the " ' "s to mean
you are using a string of characters (NOT a "real" date).

What is the underlying field's data type?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[GordonLiq]

HI Jeff,
Thanks very much for your reply.
the Date Type for that field is Date/Time.
I am in austrailia so the expected date format is dd/MM/yy
could you please tell me why when I use the #25/02/08# I get Feb,08,2025
and when I use '25/02/08' I get the correct result Feb,25,2008.
my system Date format is the default for australia , d/MM/yyyy

 

[John W. Vinson]

Hi ,
Could anyone please tell me the different between date string
#25/02/08# and '25/02/08' ?
when I use
INSERT INTO tfyMixed (FeedDate, RationID, LoadNo) VALUES (#25/02/08#, NULL,
NULL)
The real Date has been saved is Feb,2,2025
and when I use
INSERT INTO tfyMixed (FeedDate, RationID, LoadNo) VALUES ('25/02/08', NULL,
NULL)
The real Date is Feb,25 ,2008
The result Feb,25,2008 is what want , but all the code in my problem that
write by the previous are using the #25/02/08#.

is there any where that I donot have to modify all the codes?

# is a date/time delimiter; ' is a text delimiter. If you insert a text string
into a date field, Access will attempt to convert it to a date; in this case
it will probably succeed... but see below!

Dates are not strings, and are stored as Double Float numbers, a count of days
and fractions of a day since midnight, December 30, 1899. The date field can
be displayed in many different formats.

Date literals must be in either American mm/dd/yy (or mm/dd/yyyy) format, or
in an unambiguous format such as yyyy-mm-dd. Since there is no 25th month your
#25/02/08# will be interpreted correctly, but just be warned that inserting
#5/2/08# will insert May 2, not February 5!

 

[John W. Vinson]

HI Jeff,
Thanks very much for your reply.
the Date Type for that field is Date/Time.
I am in austrailia so the expected date format is dd/MM/yy
could you please tell me why when I use the #25/02/08# I get Feb,08,2025
and when I use '25/02/08' I get the correct result Feb,25,2008.
my system Date format is the default for australia , d/MM/yyyy

Access *IGNORES* the system date/time format in this context.

Literal dates (whether entered with a date delimiter or converted from a
string) *WILL* be interpreted as mm/dd/yy if that is possible. Since there is
no month 25, Access is turning your expression around to years first.

Solution? Insert an unambiguous date format such as yyyy-mm-dd or yyyy-mm-dd,
or go with the American month-day-year format.

One way to do so is to explicitly convert the date using the CDate() function
- which DOES honor Windows regional settings - and format the result:

Format(CDate('25/02/08'), "yyyy-mm-dd")

 

[GordonLiq]

Thanks very much John,
that is the answer for me.

 

[Douglas J. Steele]

Check Allen Browne's "International Dates in Access" at
http://allenbrowne.com/ser-36.html
or what I had in my September 2003 Access Answers column for Pinnacle
Publication's "Smart Access" newsletter. (The column and accompanying
database can be downloaded for free at
http://www.accessmvp.com/djsteele/SmartAccess.html)