Problem with SQL -criteria coming from form

  • Thread starter Thread starter Gina
  • Start date Start date
G

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
 
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
 
Back
Top