Date Dilema!

[Kagsy]

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

 

[Jeff Boyce]

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>

 

[Kagsy]

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

 

[JohnFol]

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#

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>

.

 

[Kagsy]

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#

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>

.

.

 

[John Vinson]

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.

 

[Jeff Boyce]

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)?

 

[Stephen Hough]

I have a similar problem with US dates in VBA.

I have a date variable which I set to the current date/time
(dtExtractTimeStamp = Now) so that I can use the same value in several
queries to make sure that I select the same records each time.

If I step through the code as it runs, the little yellow box (pardon
my lack of technical speak!) shows the value of the variable in
dd/mm/yyyy hh:mm format, but when I create a query using this variable
it saves the query with the date twisted into US format - 9th July is
saved in query as 07/09/2004, which the query understands to be the
7th September when it runs. The relevant part of the SQL string is

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

How can I get the right date into the query?


thanks
Stephen

 

[John Vinson]

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") & "#))"