SQL Query def by vba not working.....please help

  • Thread starter Thread starter satyareddy.iitm
  • Start date Start date
S

satyareddy.iitm

hi
i have a table LS1018c.that has fields..config, boomlength, opra. i
defiened a query that takes input from two text boxs (txt_or and
txt_bl)and combo box(combo_config).
now i could not get the input referenced to the sql. it is again asking
the perameters ..please tell me where i am going wrong..
thanks in advance..........
ASTY

Private Sub cmd_capacity_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim operatingradius As Double
Dim boomlength As Double
Dim strconfig As String
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("getcapQuery")

If IsNull(Combo_config) Then
MsgBox "You must enter operating Radius, boomlength and config."
_
& vbCrLf & "Please try again.", vbExclamation, _
"More information required."
Exit Sub
End If

strconfig = "='" & Me.Combo_config & "'"
operatingradius = Val(txt_or.Text)
boomlength = Val(txt_bl.Text)
strSQL = "SELECT LS1018c.[capacity]" & _
" FROM LS1018c" & _
" WHERE LS1018c.config" & strconfig & _
" AND LS1018c.boomlength = operatingradius" & _
" AND LS1018c.oprad = boomlength;"

qdf.SQL = strSQL

DoCmd.GoToRecord , , acFirst
DoCmd.Echo False
If Application.SysCmd(acSysCmdGetObjectState, acQuery,
"getcapQuery") = acObjStateOpen Then
DoCmd.Close acQuery, "getcapQuery"
End If

DoCmd.OpenQuery "getcapQuery"

DoCmd.Echo True



Set qdf = Nothing
Set db = Nothing




End Sub
 
[email protected] said:
strSQL = "SELECT LS1018c.[capacity]" & _
" FROM LS1018c" & _
" WHERE LS1018c.config" & strconfig & _
" AND LS1018c.boomlength = operatingradius" & _
" AND LS1018c.oprad = boomlength;"
Click to expand...

I think your problem is in the Where clause. You're trying to using local
variables embedded in the string. It probably should be:
strSQL = "SELECT LS1018c.[capacity]" & _
" FROM LS1018c" & _
" WHERE LS1018c.config" & strconfig & _
" AND LS1018c.boomlength = " & operatingradius & _
" AND LS1018c.oprad = " & boomlength

Also, I would recommend naming your variables with some form of Hungarian
notation to make the code easier to read and to help differentiate variables
from table fields. For example, you might change operatingradius to
dblOperatingRadius.

Barry
 
Barry said:
[email protected] said:
strSQL = "SELECT LS1018c.[capacity]" & _
" FROM LS1018c" & _
" WHERE LS1018c.config" & strconfig & _
" AND LS1018c.boomlength = operatingradius" & _
" AND LS1018c.oprad = boomlength;"
Click to expand...

I think your problem is in the Where clause. You're trying to using local
variables embedded in the string. It probably should be:
strSQL = "SELECT LS1018c.[capacity]" & _
" FROM LS1018c" & _
" WHERE LS1018c.config" & strconfig & _
" AND LS1018c.boomlength = " & operatingradius & _
" AND LS1018c.oprad = " & boomlength

Also, I would recommend naming your variables with some form of Hungarian
notation to make the code easier to read and to help differentiate variables
from table fields. For example, you might change operatingradius to
dblOperatingRadius.

Barry
Click to expand...



Thanks alot
Asty
 
Back
Top