Incorrect Date posting Access 2000 SQL statement

[m_t_hill]

Running MS Access 2000
MS Windows XP Pro

This has caused me a lot of hair loss in the last few days so would
appreciate any help.

I am running code to append/update a local access database from a
remote MS SQL database. The method of determining which records to
append is primarily using the last date an update was carried out; I
appreciate that there is probably a better way of doing this but time
contraints prevent me from doing extensive research and dev at the
moment. Below is a section of code from the procedure where
tblLastUpdate is a 2 field single record table used to store the date
the database was last updated.

Dim dateLastUpdate As Date
Dim dateNewUpdate As Date
Dim dateCurrentDate As Date
Dim stUpdateLastUpdateSQL As String
dateLastUpdate = DLookup("LastUpdate", "tblLastUpdate", "DateID = 1")
dateNewUpdate = dateLastUpdate - 1
dateCurrentDate = Date

stUpdateLastUpdateSQL = "UPDATE tblLastUpdate SET
tblLastUpdate.LastUpdate = #" & dateCurrentDate & "#" & _
"WHERE (((tblLastUpdate.DateID)=1));"

DoCmd.RunSQL stUpdateLastUpdateSQL

Now the problem is that when this code runs the dateCurrentDate
variable is set correctly at e.g. 08/04/05 and having stepped through
the code this remains correct all the way through, but after the SQL
statement is executed the LastUpdate field is populated with 04/08/05.
This is not simply a case of date formatting on the table as when this
new date is run through the code again the dateNewUpdate variable is
set to 03/08/05.

I have checked all regional settings bith in access and windows and
they all appear to be fine.

Thanks in advance
Matt

[John Webb via AccessMonster.com]

Matt,

To the best of my knowledge, I believe that SQL always treats dates in the
format mm/dd/yy.

However, I tend to use date serials wherever possible; then convert these
for the users to view - it stops me having to worry about what SQL does to
dates, and the users date / time formatting etc etc

Cheers

John Webb

--
Message posted via http://www.accessmonster.com

[Van T. Dinh]

It sounds like your default date format is "dd/mm/yyyy". In this case, you
need to format your date to the format "mm/dd/yyyy" as explicit date value
enclosed by # must be in the format "mm/dd/yyyy" regardless of your Regional
Settings.

Try

stUpdateLastUpdateSQL = "UPDATE tblLastUpdate SET
tblLastUpdate.LastUpdate = " & Format(dateCurrentDate, '\#mm/dd/yyyy\#') & _
"WHERE (((tblLastUpdate.DateID)=1));"

BTW, if tblLastUpdate is a one-Record Table, then you don't need the 3rd
argument in the DLookUp() and the WHERE clause above is unnecessary.

Not sure of the second part of your second last paragraph but try the above
and see how it comes out.

--
Van T. Dinh

<m_******@hotmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Running MS Access 2000
MS Windows XP Pro

This has caused me a lot of hair loss in the last few days so would
appreciate any help.

I am running code to append/update a local access database from a
remote MS SQL database. The method of determining which records to
append is primarily using the last date an update was carried out; I
appreciate that there is probably a better way of doing this but time
contraints prevent me from doing extensive research and dev at the
moment. Below is a section of code from the procedure where
tblLastUpdate is a 2 field single record table used to store the date
the database was last updated.

Dim dateLastUpdate As Date
Dim dateNewUpdate As Date
Dim dateCurrentDate As Date
Dim stUpdateLastUpdateSQL As String
dateLastUpdate = DLookup("LastUpdate", "tblLastUpdate", "DateID = 1")
dateNewUpdate = dateLastUpdate - 1
dateCurrentDate = Date

stUpdateLastUpdateSQL = "UPDATE tblLastUpdate SET
tblLastUpdate.LastUpdate = #" & dateCurrentDate & "#" & _
"WHERE (((tblLastUpdate.DateID)=1));"

DoCmd.RunSQL stUpdateLastUpdateSQL

Now the problem is that when this code runs the dateCurrentDate
variable is set correctly at e.g. 08/04/05 and having stepped through
the code this remains correct all the way through, but after the SQL
statement is executed the LastUpdate field is populated with 04/08/05.
This is not simply a case of date formatting on the table as when this
new date is run through the code again the dateNewUpdate variable is
set to 03/08/05.

I have checked all regional settings bith in access and windows and
they all appear to be fine.

Thanks in advance
Matt

[Matt]

Thanks for the suggestions, haven't had chance to try yet but will get
back when I have.

"Van T. Dinh" <Va***********@discussions.microsoft.com> wrote in message news:<8V*****************@news-server.bigpond.net.au>...

It sounds like your default date format is "dd/mm/yyyy". In this case, you
need to format your date to the format "mm/dd/yyyy" as explicit date value
enclosed by # must be in the format "mm/dd/yyyy" regardless of your Regional
Settings.

Try

stUpdateLastUpdateSQL = "UPDATE tblLastUpdate SET
tblLastUpdate.LastUpdate = " & Format(dateCurrentDate, '\#mm/dd/yyyy\#') & _
"WHERE (((tblLastUpdate.DateID)=1));"

BTW, if tblLastUpdate is a one-Record Table, then you don't need the 3rd
argument in the DLookUp() and the WHERE clause above is unnecessary.

Not sure of the second part of your second last paragraph but try the above
and see how it comes out.

--
Van T. Dinh

<m_******@hotmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Running MS Access 2000
MS Windows XP Pro

This has caused me a lot of hair loss in the last few days so would
appreciate any help.

I am running code to append/update a local access database from a
remote MS SQL database. The method of determining which records to
append is primarily using the last date an update was carried out; I
appreciate that there is probably a better way of doing this but time
contraints prevent me from doing extensive research and dev at the
moment. Below is a section of code from the procedure where
tblLastUpdate is a 2 field single record table used to store the date
the database was last updated.

>

Dim dateLastUpdate As Date
Dim dateNewUpdate As Date
Dim dateCurrentDate As Date
Dim stUpdateLastUpdateSQL As String
dateLastUpdate = DLookup("LastUpdate", "tblLastUpdate", "DateID = 1")
dateNewUpdate = dateLastUpdate - 1
dateCurrentDate = Date

stUpdateLastUpdateSQL = "UPDATE tblLastUpdate SET
tblLastUpdate.LastUpdate = #" & dateCurrentDate & "#" & _
"WHERE (((tblLastUpdate.DateID)=1));"

DoCmd.RunSQL stUpdateLastUpdateSQL

>

Now the problem is that when this code runs the dateCurrentDate
variable is set correctly at e.g. 08/04/05 and having stepped through
the code this remains correct all the way through, but after the SQL
statement is executed the LastUpdate field is populated with 04/08/05.
This is not simply a case of date formatting on the table as when this
new date is run through the code again the dateNewUpdate variable is
set to 03/08/05.

I have checked all regional settings bith in access and windows and
they all appear to be fine.

Thanks in advance
Matt

[Matt]

m_******@hotmail.com (Matt) wrote in message news:<dd**************************@posting.google. com>...

I chickened out and have resorted to using the DoCmd.OpenQuery
Statement elliminating the use of SQL for the time being. So now have
a query to update the LastUpdate in tblLastUpdate to todays date and,
to limit the records retrieved, the date dependant query has the
criteria: >=((DLookUp("[LastUpdate]","tblLastUpdate","[DateID]=1"))-1).
This seems to do the trick. All suggestions very much appreciated and
I will certainly be making use of them in the future when I make
improvements in the future.

Thanks again,
Matt

Thanks for the suggestions, haven't had chance to try yet but will get
back when I have.

"Van T. Dinh" <Va***********@discussions.microsoft.com> wrote in message news:<8V*****************@news-server.bigpond.net.au>...

It sounds like your default date format is "dd/mm/yyyy". In this case, you
need to format your date to the format "mm/dd/yyyy" as explicit date value
enclosed by # must be in the format "mm/dd/yyyy" regardless of your Regional
Settings.

Try

stUpdateLastUpdateSQL = "UPDATE tblLastUpdate SET
tblLastUpdate.LastUpdate = " & Format(dateCurrentDate, '\#mm/dd/yyyy\#') & _
"WHERE (((tblLastUpdate.DateID)=1));"

BTW, if tblLastUpdate is a one-Record Table, then you don't need the 3rd
argument in the DLookUp() and the WHERE clause above is unnecessary.

Not sure of the second part of your second last paragraph but try the above
and see how it comes out.

--
Van T. Dinh

<m_******@hotmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Running MS Access 2000
MS Windows XP Pro

This has caused me a lot of hair loss in the last few days so would
appreciate any help.

I am running code to append/update a local access database from a
remote MS SQL database. The method of determining which records to
append is primarily using the last date an update was carried out; I
appreciate that there is probably a better way of doing this but time
contraints prevent me from doing extensive research and dev at the
moment. Below is a section of code from the procedure where
tblLastUpdate is a 2 field single record table used to store the date
the database was last updated.

>>
Dim dateLastUpdate As Date
Dim dateNewUpdate As Date
Dim dateCurrentDate As Date
Dim stUpdateLastUpdateSQL As String
dateLastUpdate = DLookup("LastUpdate", "tblLastUpdate", "DateID = 1")
dateNewUpdate = dateLastUpdate - 1
dateCurrentDate = Date

stUpdateLastUpdateSQL = "UPDATE tblLastUpdate SET
tblLastUpdate.LastUpdate = #" & dateCurrentDate & "#" & _
"WHERE (((tblLastUpdate.DateID)=1));"

DoCmd.RunSQL stUpdateLastUpdateSQL
>>

Now the problem is that when this code runs the dateCurrentDate
variable is set correctly at e.g. 08/04/05 and having stepped through
the code this remains correct all the way through, but after the SQL
statement is executed the LastUpdate field is populated with 04/08/05.
This is not simply a case of date formatting on the table as when this
new date is run through the code again the dateNewUpdate variable is
set to 03/08/05.

I have checked all regional settings bith in access and windows and
they all appear to be fine.

Thanks in advance
Matt

[Matt]

I found some time last night and had a go with the suggestion and it
worked, my SQL string now looks like this.

stUpdateLastUpdateSQL = "UPDATE tblLastUpdate SET
tblLastUpdate.LastUpdate = #" & Format(CDate(Date), "mm-dd-yyyy") &
"#" & "WHERE (((tblLastUpdate.DateID)=1));"

And the LastUpdate field in tblLastUpdate is now populated in the
correct format. Many thanks again

Matt

m_******@hotmail.com (Matt) wrote in message news:<dd**************************@posting.google. com>...

m_******@hotmail.com (Matt) wrote in message news:<dd**************************@posting.google. com>...

I chickened out and have resorted to using the DoCmd.OpenQuery
Statement elliminating the use of SQL for the time being. So now have
a query to update the LastUpdate in tblLastUpdate to todays date and,
to limit the records retrieved, the date dependant query has the
criteria: >=((DLookUp("[LastUpdate]","tblLastUpdate","[DateID]=1"))-1).
This seems to do the trick. All suggestions very much appreciated and
I will certainly be making use of them in the future when I make
improvements in the future.

Thanks again,
Matt

Thanks for the suggestions, haven't had chance to try yet but will get
back when I have.

"Van T. Dinh" <Va***********@discussions.microsoft.com> wrote in message news:<8V*****************@news-server.bigpond.net.au>...

It sounds like your default date format is "dd/mm/yyyy". In this case, you
need to format your date to the format "mm/dd/yyyy" as explicit date value
enclosed by # must be in the format "mm/dd/yyyy" regardless of your Regional
Settings.

Try

stUpdateLastUpdateSQL = "UPDATE tblLastUpdate SET
tblLastUpdate.LastUpdate = " & Format(dateCurrentDate, '\#mm/dd/yyyy\#') & _
"WHERE (((tblLastUpdate.DateID)=1));"

BTW, if tblLastUpdate is a one-Record Table, then you don't need the 3rd
argument in the DLookUp() and the WHERE clause above is unnecessary.

Not sure of the second part of your second last paragraph but try the above
and see how it comes out.

--
Van T. Dinh

<m_******@hotmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
> Running MS Access 2000
> MS Windows XP Pro
>
> This has caused me a lot of hair loss in the last few days so would
> appreciate any help.
>
> I am running code to append/update a local access database from a
> remote MS SQL database. The method of determining which records to
> append is primarily using the last date an update was carried out; I
> appreciate that there is probably a better way of doing this but time
> contraints prevent me from doing extensive research and dev at the
> moment. Below is a section of code from the procedure where
> tblLastUpdate is a 2 field single record table used to store the date
> the database was last updated.
>
> >>
> Dim dateLastUpdate As Date
> Dim dateNewUpdate As Date
> Dim dateCurrentDate As Date
> Dim stUpdateLastUpdateSQL As String
>
>
> dateLastUpdate = DLookup("LastUpdate", "tblLastUpdate", "DateID = 1")
> dateNewUpdate = dateLastUpdate - 1
> dateCurrentDate = Date
>
> stUpdateLastUpdateSQL = "UPDATE tblLastUpdate SET
> tblLastUpdate.LastUpdate = #" & dateCurrentDate & "#" & _
> "WHERE (((tblLastUpdate.DateID)=1));"
>
> DoCmd.RunSQL stUpdateLastUpdateSQL
> >>
>
> Now the problem is that when this code runs the dateCurrentDate
> variable is set correctly at e.g. 08/04/05 and having stepped through
> the code this remains correct all the way through, but after the SQL
> statement is executed the LastUpdate field is populated with 04/08/05.
> This is not simply a case of date formatting on the table as when this
> new date is run through the code again the dateNewUpdate variable is
> set to 03/08/05.
>
> I have checked all regional settings bith in access and windows and
> they all appear to be fine.
>
> Thanks in advance
> Matt
>