Date Dilema!

Dear Guys

I am now officially at my wit's end, so I really need your
guy’s help.

I am using VBA to find a record in table by a date field.
Sounds straightforward, but VBA converts the date variable
to the US format and the date in my table is in UK format
(it finds by "mm/dd/yy" rather than "dd/mm/yy"). Because
the format is different the record is not found.

I then changed the date to #01/April/2004# which was then
changed to #04/01/2004# but when I tried to use the
dateadd function the date became #05/01/04#, which is
correct but I am now searching for 1st May rather than the
2nd April.

How can I declare variables called startDate and curDate
that is set to #01/April/2004# with the EndDate of
#23/June/2004# and tries to find a record in a table (set
to UK date format - code for searching not needed) with a
UK date and also when I add a day with the DateAdd
function to curDate, the value becomes #02/04/2004#?

If I keep the startDate variable to #1/4/04#, this is
changed to the US format and the record is found but the
DataAdd function adds 1 day in US format to become
#05/01/2004# which is the 1st May not the 2nd of April as
I would like.

Where do I go from here? I would like to thank all those
who have read the post through the end and not died of old
age or boredom.

Thanks again.

Kagsy.

This code snipet may help:

Private Sub FindRecord()
Dim StartDate As Date
Dim endDate As Date
Dim curDate As Date
StartDate = #1/4/2004#
endDate = Date - 1
curDate = #1/4/2004#
do until rs.eof
'Function to find record for that date
curDate = DateAdd("d", 1, curDate)
Loop
End Sub

Kagsy

DateAdd() includes a parameter to describe which date component you are
adding. If I recall correctly, use the 'm' (month) "interval" to add one
month (something like):

DateAdd("m",1,[YourDateField])

Good luck

Jeff Boyce
Access MVP

Thanks for the reply.

The problem with this approach is that it will add a month
to the date but what I am trying to is increment the date
by one day between the startDate and endDate to UK format.

If the startDate is #01/04/04# (changes to #4/1/04#) and
add a month, this becomes #5/2/04#, missing out all the
other dates between. Adding a day to #4/1/04# becomes
#5/1/04# which the code assumes is 1st May when searching
for records.

I have a problem that I am sure other non-us users have
had before.

Kagsy

-----Original Message-----
Kagsy

DateAdd() includes a parameter to describe which date
component you are
adding. If I recall correctly, use the 'm'
(month) "interval" to add one
month (something like):

DateAdd("m",1,[YourDateField])

Good luck

Jeff Boyce
Access MVP

.

Yes we have! (god bless america)

If you don't mind the app being used for the English speaking community, you
can explicity avoid the ambiguity by using a different format, ie
#01/APR/04#



"Kagsy" wrote in message
...
Thanks for the reply.

The problem with this approach is that it will add a month
to the date but what I am trying to is increment the date
by one day between the startDate and endDate to UK format.

If the startDate is #01/04/04# (changes to #4/1/04#) and
add a month, this becomes #5/2/04#, missing out all the
other dates between. Adding a day to #4/1/04# becomes
#5/1/04# which the code assumes is 1st May when searching
for records.

I have a problem that I am sure other non-us users have
had before.

Kagsy

-----Original Message-----
Kagsy

DateAdd() includes a parameter to describe which date
component you are
adding. If I recall correctly, use the 'm'
(month) "interval" to add one
month (something like):

DateAdd("m",1,[YourDateField])

Good luck

Jeff Boyce
Access MVP

.

JohnFol,

My understanding and experience of #1/4/04# (changed to
#4/1/04# by the compiler) is that it is identical to
#1/Apr/04# because the compiler will converted the date to
#4/1/04# anyway. Therefore there is no real difference?

Kagsy

-----Original Message-----
Yes we have! (god bless america)

If you don't mind the app being used for the English
speaking community, you
can explicity avoid the ambiguity by using a different
format, ie
#01/APR/04#



"Kagsy" wrote in
message
...
Thanks for the reply.

The problem with this approach is that it will add a
month
to the date but what I am trying to is increment the
date
by one day between the startDate and endDate to UK
format.

If the startDate is #01/04/04# (changes to #4/1/04#) and
add a month, this becomes #5/2/04#, missing out all the
other dates between. Adding a day to #4/1/04# becomes
#5/1/04# which the code assumes is 1st May when
searching
for records.

I have a problem that I am sure other non-us users have
had before.

Kagsy

-----Original Message-----
Kagsy

DateAdd() includes a parameter to describe which date
component you are
adding. If I recall correctly, use the 'm'
(month) "interval" to add one
month (something like):

DateAdd("m",1,[YourDateField])

Good luck

Jeff Boyce
Access MVP

.



.

On Thu, 24 Jun 2004 04:43:43 -0700, "Kagsy"
wrote:

I am using VBA to find a record in table by a date field.
Sounds straightforward, but VBA converts the date variable
to the US format and the date in my table is in UK format
(it finds by "mm/dd/yy" rather than "dd/mm/yy"). Because
the format is different the record is not found.

The date in your table IS NOT stored in UK format. It's stored as a
double float number, a count of days and fractions of a day since
midnight, December 30, 1899. The formatting controls how that number
*is displayed*, but not what is stored in the table.

Since the programmers who developed Access were Americans, they made
the arbitrary decision that nn/nn/nnnn date literals in VBA or in SQL
would ALWAYS - regardless of machine settings - be interpreted as
mm/dd/yyyy dates. Sorry, but that's just the way the program is
written. If you use such date literals in VBA or in a query then you
must - no option - use the American format.

One getaround is to use a criterion of

Format([datefield], "mm\/dd\/yyyy")

to cast the datefield in the necessary format.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Kagsy

My example was only an example. Does the same thing happen if you use one
of the other "intervals" (instead of "m" - for month)?

--
Good luck

Jeff Boyce
Access MVP

On 12 Jul 2004 18:27:00 -0700, (Stephen Hough)
wrote:

9th July is
saved in query as 07/09/2004, which the query understands to be the
7th September when it runs.

Eh? Not in my experience! That's July 9 as a SQL date literal.

The relevant part of the SQL string is

WHERE (((tblServiceOrder.Updated_on) #" & dtExtractTimeStamp & "#)).

How can I get the right date into the query?

WHERE (((tblServiceOrder.Updated_on) #" & Format(dtExtractTimeStamp,
"mm\/dd\/yyyy") & "#))"


John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

On 12 Jul 2004 18:27:00 -0700, (Stephen Hough)
wrote:

9th July is
saved in query as 07/09/2004, which the query understands to be the
7th September when it runs.

Eh? Not in my experience! That's July 9 as a SQL date literal.

The relevant part of the SQL string is

WHERE (((tblServiceOrder.Updated_on) #" & dtExtractTimeStamp & "#)).

How can I get the right date into the query?

WHERE (((tblServiceOrder.Updated_on) #" & Format(dtExtractTimeStamp,
"mm\/dd\/yyyy") & "#))"


John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public