data type mismatch in expression

[muyBN]

I'm trying to update an Access record with the following code but get the
above error when I run the code (on the line with "Execute"). Any suggestions?

Set dbDB = OpenDatabase("C:\Data\0\jbDB.mdb")
strSQL = "UPDATE jbDBTable SET sent = 'strDate' WHERE jbID='" & intjbID & "'
and CO='" & strCo & "';"
dbDB.Execute strSQL
dbDB.Close

 

[Jezebel]

At a guess, your 'sent' field is a date, not a string, so you need something
like

"UPDATE jbDBTable SET sent = %" & strDate & "% WHERE (jbID='" & intjb ...

and strDate will have to be formatted in a form that Access is happy with ..
yyyy-mm-dd is the most reliable.

 

[muyBN]

Jezebel, I just realized a while ago that I should have stated my variable
definitions in my original post. Good thought on the SENT field, but actually
I defined it as string/text so I could either enter a date as string or put
an X in the field on the fly or if I didn't know the exact date. I suspect
that it's a problem with the integer. I changed the code to pick up the value
of intjbId to:

intjbID = val(????????)

Anyway, following is how I have the variables defined. Any more thoughts?

Dim strCo As String, strSQL As String, strDate As String
Dim intjbID As Integer
Dim dbDB As Database

 

[Jezebel]

I think it more likely that the problem is at the Access end. Have a look at
the data types of the fields in jbDBTable. The SQL statement you send to the
database engine is a string, regardless of the data types of the variables
from which you construct it.

Switching from long to integer does nothing. Integer is a relic from the
days of 16 bit processors. The minimum word size in VBA is 32 bit -- so an
integer is just a long with the top 16 bits zeroed out.

 

[Jezebel]

Here's a method to diagnose the problem --

1. Step through the code until strSQL has been created.

2. Print strSQL in the immediate window and copy it to clipboard.

3. Start up Access, open the database, and design a new query. Switch to the
SQL pane, paste in your SQL statement, and execute it. Access will tell you
exactly what it doesn't like about it.

Or conversely, create the update using Access's query builder, check that it
works, then switch to the SQL pane: that's what your SQL string should look
like. Now fix the macro to match.

 

[muyBN]

Thanks, I finally got it to work with this SQL string:

strSQL = "UPDATE jbDBTable SET sent = '" & Date & "' where '" & intjbID
& "' and CO = '" & strCo & "' ;"

You mentioned a date format of "yyyy-mm-dd." I would like to use that format
but after a couple hours of searching, I can't find exactly how to set that
format. Everything I try brings up an error.

 

[Jezebel]

strDate = format(Date, "yyyy-mm-dd")

Thanks, I finally got it to work with this SQL string:

strSQL = "UPDATE jbDBTable SET sent = '" & Date & "' where '" &
intjbID
& "' and CO = '" & strCo & "' ;"

You mentioned a date format of "yyyy-mm-dd." I would like to use that
format
but after a couple hours of searching, I can't find exactly how to set
that
format. Everything I try brings up an error.

 

[muyBN]

Jezebel,
All my problems are solved, thanks to you.