CreateQueryDef Problem

[Guest]

Here's my problem:

I need to be able to input a value (accidentnum) as a string into the where
clause of an SQL statement. The problem occurs with a leading zero in
accidentnum.

Accidentnum has been declared as a string value and when i debug.print the
value, it displays properly. When I create the SQL string using the
accidentnum value and then debug.print the SQL string, it displays properly.
When I create the query, the criteria box shows that the criteria has dropped
the leading zero. After creating the query, if I check the SQL string, it
still displays properly. Any ideas?

Here's my code:
Public Function DataQuery(accidentnum As String)
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strsql As String

Debug.Print accidentnum

strsql = "SELECT tblConcatenatedData.* INTO tblOutput FROM
tblConcatenatedData WHERE (((tblConcatenatedData.ACCIDENT_NUM)=" &
accidentnum & "));"
Debug.Print strsql
Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("TempQuery", strsql)
Debug.Print strsql

End Function

When I go to the query and view the SQL, this is what I see:
SELECT tblConcatenatedData.* INTO tblOutput
FROM tblConcatenatedData
WHERE (((tblConcatenatedData.ACCIDENT_NUM)="6-440"));

 

[Guest]

By the way, the SQL clause below resulted from an accidentnum input of "06-440"

I called the code in the immediate window by typing
?Dataquery("06-440")

 

[Roger Carlson]

Do either this:

strsql = "SELECT tblConcatenatedData.* INTO tblOutput FROM
tblConcatenatedData WHERE (((tblConcatenatedData.ACCIDENT_NUM)='" &
accidentnum & "'));"

or this:

strsql = "SELECT tblConcatenatedData.* INTO tblOutput FROM
tblConcatenatedData WHERE (((tblConcatenatedData.ACCIDENT_NUM)=""" &
accidentnum & """));"


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L