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_t_hill@hotmail.com (Matt) wrote in message news:<dddcf615.0504100850.106a2911@posting.google. com>...[color=blue]
>
m_t_hill@hotmail.com (Matt) wrote in message news:<dddcf615.0504090315.5b351f77@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
>
>
>[color=green]
> > Thanks for the suggestions, haven't had chance to try yet but will get
> > back when I have.
> >
> > "Van T. Dinh" <VanThien.Dinh@discussions.microsoft.com> wrote in message news:<8Vu5e.3938$5F3.1475@news-server.bigpond.net.au>...[color=darkred]
> > > 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_t_hill@hotmail.com> wrote in message
> > > news:1112958029.560140.288640@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
> > > >[/color][/color][/color]
