Updating a record in a table! 1

[InkyRich]

Hi chaps,
I am having trouble trying to update a record in a table using UPDATE to update from the value of LastDateVar to the value of TodaysDateVar.
The code I am using is:

    Dim FindLastDate As New ADODB.Recordset
    Dim conn As ADODB.Connection
    Set conn = CurrentProject.Connection
    Dim updateCommand As String


    FindLastDate.Open "SELECT DatabaseDate FROM TBLDatabaseDate", conn
    LastDateVar = FindLastDate("DatabaseDate").Value
    updateCommand = "UPDATE TBLDatabaseDate SET DatabaseDate = " & TodaysDateVar & " WHERE DatabaseDate = '" & LastDateVar & "'"
    
    conn.Execute updateCommand
   
    FindLastDate.Close

The first two line of the code is to open the table TBLDatabaseDate to find the value of the record DatabaseDate, this works fine.
The next line

updateCommand = "UPDATE TBLDatabaseDate SET DatabaseDate = " & TodaysDateVar & " WHERE DatabaseDate = '" & LastDateVar & "'"

gives a Syntax Error and I cannot see where.
What am I doing wrong?
Any suggestions?

Inky

A fool and his money are soon parted - so you might as well send it to me!

 

[PHV]

What is TodaysDateVar ?
If DatabaseDate is defined as a date field in TBLDatabaseDate then you may try this:

updateCommand = "UPDATE TBLDatabaseDate SET DatabaseDate=#" & TodaysDateVar & "# WHERE DatabaseDate=#" & LastDateVar & "#"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[InkyRich]

Ah yes I see where you are coming from. You may have the answer I will give it a try and let you know.
Thanks

A fool and his money are soon parted - so you might as well send it to me!

 

[InkyRich]

Hello phv,
No the TodaysDateVar and LastDateVar variables are both strings, but I tried the # which did not work. Any other suggestion as to the syntax error?
Inky

A fool and his money are soon parted - so you might as well send it to me!

 

[InkyRich]

Hello again phv,
I have made some progress. Where I have played with the code &TodaysDateVar& and '&TodaysDateVar& ' it has actually updated the table but with the actual variable name not its value. Is that a clue?
Inky

A fool and his money are soon parted - so you might as well send it to me!

 

[PHV]

You tried this ?

updateCommand = "UPDATE TBLDatabaseDate SET DatabaseDate='" & TodaysDateVar & "' WHERE DatabaseDate='" & LastDateVar & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[InkyRich]

Yes and it comes up with the message 'Object varaiable or With block variable not set'

A fool and his money are soon parted - so you might as well send it to me!

 

[PHV]

What is your actual code and which line raises the error ?
Again, what is TodaysDateVar and where is it defined/populated ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[InkyRich]

The table is actually getting updated now which is great. It suggests to me that the error is being triggered else where.
The total code is:

'TodaysDateVar and LastDateVar are global strings

    Dim FindLastDate As New ADODB.Recordset
    Dim conn As ADODB.Connection
    Set conn = CurrentProject.Connection
    Dim updateCommand As String

    FindLastDate.Open "SELECT DatabaseDate FROM TBLDatabaseDate", conn
    LastDateVar = FindLastDate("DatabaseDate").Value        
    updateCommand = "UPDATE TBLDatabaseDate SET TBLDatabaseDate.DatabaseDate =  ' " & TodaysDateVar & "  '  WHERE TBLDatabaseDate.DatabaseDate = '" & LastDateVar & "'"
    
    conn.Execute updateCommand
   
    FindLastDate.Close


        If LastDateVar <> TodaysDateVar Then
 Debug.Print "LastDateVar IS NOT equal to TodaysDateVar"
 
            ConstDateTrig = -1  'NOT equal

           
 
        Else
        
Debug.Print "LastDateVar IS equal to TodaysDateVar"
            ConstDateTrig = 0  'IS equal
       
        End If

Hope this helps.

A fool and his money are soon parted - so you might as well send it to me!

 

[InkyRich]

Thanks phv,
I have tidied the code up and it seems to have got rid of the error. Just a stray variable I think. It all seems to be workingok and updating also.
Thanks for your input - take a star.
Regards
Inky

A fool and his money are soon parted - so you might as well send it to me!