SQL in VBA code

  • Thread starter Thread starter Mike Waller
  • Start date Start date
M

Mike Waller

I have been trying to perform a simple Select query using
an SQL statement in VBA. However, one element is not
working. At the end of my Select statement I am trying to
compare a table value (Battery) to an entry on a form
(variable rptType) and it isn't working. I get an
error "No value given for one or more required
parameters". If I substitute a literal value, the query
works fine. The code is attached;

rptType = Me!Type
Set rsReport = New ADODB.Recordset

rptTable = "SELECT Pin1, Temp1, Spec1 FROM Test WHERE
Battery = rptType "
rsReport.Open rptTable, cnCurrent


Thanks,
Mike
 
If rptType has a numeric value in it:

rptTable = "SELECT Pin1, Temp1, Spec1 " & _
"FROM Test WHERE Battery = " & rptType & ";"

If rptType has a text value in it:

rptTable = "SELECT Pin1, Temp1, Spec1 " & _
"FROM Test WHERE Battery = '" & rptType & "';"
 
well you first need to have RptType outside the quotes its comparing to
the string rptType
second if you are trying to do a Where a textfield = something you need
tick marks (single quotes) to denote the beggining and end of the text


rptTable = "SELECT Pin1, Temp1, Spec1 FROM Test WHERE Battery = '" &_
rptType & "'"
Hope this helps
mark
 
Thanks, I forgot the syntax for excluding the variable
from the quotes. It has been a while since I have used
this type of statement.

Mike
 
Back
Top