Problem with SQL -criteria coming from form

[Gina]

Hi

I have a problem similar to the one Jack mentioned earlier here in this
group ... so I changed my SQL statement ... without success
___________________________________
....
Dim ctl as Integer

ctl = Form__frmDetails.txtWork_ID.Value

SQL = "SELECT Details.Material, Details.Total FROM Details"
SQL = SQL & " WHERE Details.Work_ID = '" & ctl & "'"
SQL = SQL & " ORDER BY Details.Detail_ID"

Dim DB As DAO.Database
Dim rs As DAO.Recordset

Set DB = CurrentDb()
--> Set rs = DB.OpenRecordset(SQL)
____________________________________

the criteria comes as input from a form ...
stepping through the code the current value of the forms control shows
correctly
--> where the recordset is openend I get the message that types wouldn't
match

but when I directly type in a '1' rather than '" & ctl & "'" into the SQL
statement
it works fine ...
I checked the underlying table and the field type is long integer
(autoincrement)

what could be the wrong bit ??

Thanks Gina

 

[Roger Carlson]

You are delimiting your variable value with apostrophes which is only
necessary when the value is Text. Since your value is a Long, it should
look like this:

SQL = "SELECT Details.Material, Details.Total FROM Details"
SQL = SQL & " WHERE Details.Work_ID = " & ctl &
SQL = SQL & " ORDER BY Details.Detail_ID"

--
--Roger Carlson
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

 

[Gina]

Thank you .... thank you .... so much

it works!!!!