Date Dilema!

  • Thread starter Thread starter Kagsy
  • Start date Start date
K

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
 
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
 
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 said:
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>

.
Click to expand...
Click to expand...
 
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>

.
Click to expand...
Click to expand...


.
Click to expand...
 
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.
Click to expand...

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.
 
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)?
 
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
 
9th July is
saved in query as 07/09/2004, which the query understands to be the
7th September when it runs.
Click to expand...

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?
Click to expand...

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