SQL problem

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

Guest

I keep getting a data conversion error with the following code:

Set tmpRecset = db.CreateQueryDef("")
With tmpRecset

.SQL = "SELECT * FROM tblCustXRef WHERE tblCustXRef!ID = ID"
Set recCustXRef = .OpenRecordset("tblCustXRef")
recCustXRef.MoveFirst

End With

Anyhelp would be appreciated. FYI, both ID fields are strings.
 
What is ID? (i.e.: is it a variable, a control on a form or what?)

If it's a variable or a control, it needs to be placed outside of the
quotes, so that its value is in the string, not its name:

.SQL = "SELECT * FROM tblCustXRef WHERE tblCustXRef!ID = " & Chr$(34) &
ID & Chr$(34)
 
Well I tried this and while it may be closer I am still getting a "Data type
conversion error". ID is a variable.
 
.SQL = "SELECT * FROM tblCustXRef WHERE tblCustXRef!ID = ID"
Click to expand...

The whole WHERE clause has problems. Douglas has pointed out that you need
to pass the actual value of ID, but I think that the bang notation is also
incorrect. Try this:

.SQL = "SELECT *" & vbNewLine & _
"FROM tblCustXRef" & vbNewLine & _
"WHERE ID = " & Format(ID, "0000")

MsgBox .sql



Strictly speaking the vbNewLine and Format() are unnecessary, but are worth
gold when you are trying to debug the stuff.

All the best


Tim F
 
Back
Top