Type Mismatch

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anyone tell me what I am doing wrong here?

strsql = "Insert INTO tbltemp (strdriver) SELECT distinct
tblpurchaseorders.[strdriver] FROM tblpurchaseorders where
tblpurchaseorders.[strdriver] like " & Name & "" * ""
 
If [strdriver] is, as implied, a text character, your SQL statement has not
instructed the WHERE clause to look for a string/text value.

And by the way, if the title/name of your field/control is actually "Name",
consider changing it. Access treats that word (and others) as a reserved
word -- when you specify "Name", Access needs to figure out whether you mean
YOUR meaning of Name or its own.

Regards

Jeff Boyce
<Office/Access MVP>
 
strsql = "Insert INTO tbltemp (strdriver) SELECT distinct
tblpurchaseorders.[strdriver] FROM tblpurchaseorders where
tblpurchaseorders.[strdriver] like " & Name & "" * ""
Click to expand...

Quotes are really tricky. If the [Name] variable contains, say,
O'Henry, you'll need to delimit the criterion with " characters - and
to insert a " into a string which is itself delimited by ", you need
to use "".

Try

strsql = "Insert INTO tbltemp (strdriver) SELECT distinct
tblpurchaseorders.[strdriver] FROM tblpurchaseorders where
tblpurchaseorders.[strdriver] like """ & Name & "*"""

This will make strSQL end up as


Insert INTO tbltemp (strdriver) SELECT distinct
tblpurchaseorders.[strdriver] FROM tblpurchaseorders where
tblpurchaseorders.[strdriver] like "O'Henry*"


John W. Vinson[MVP]
 
John it worked like a charm, thank you so much.
It's very frustrating, to struggle with a set of quotation marks for over an
hour and still not be able to get it right. Thanks for the help, I am onto
the next step.... I hope :)

Todd

John Vinson said:
strsql = "Insert INTO tbltemp (strdriver) SELECT distinct
tblpurchaseorders.[strdriver] FROM tblpurchaseorders where
tblpurchaseorders.[strdriver] like " & Name & "" * ""
Click to expand...

Quotes are really tricky. If the [Name] variable contains, say,
O'Henry, you'll need to delimit the criterion with " characters - and
to insert a " into a string which is itself delimited by ", you need
to use "".

Try

strsql = "Insert INTO tbltemp (strdriver) SELECT distinct
tblpurchaseorders.[strdriver] FROM tblpurchaseorders where
tblpurchaseorders.[strdriver] like """ & Name & "*"""

This will make strSQL end up as


Insert INTO tbltemp (strdriver) SELECT distinct
tblpurchaseorders.[strdriver] FROM tblpurchaseorders where
tblpurchaseorders.[strdriver] like "O'Henry*"


John W. Vinson[MVP]
Click to expand...
 
Back
Top