sql in VBA for ACCESS-- help .. plz

  • 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
 
I would expect an error on these two lines. In Access, the text property of
a control is only available when the control has the focus. You want the
Value property. The Value property is the default property, so you don't
really have to include it if that is what you want to return.

operatingradius = Val(txt_or.Value)
boomlength = Val(txt_bl.)

Next your SQL statement should read more like the following. You need to
pass the values of the variables and not the names of the variables in the
sql statement.

strSQL = "SELECT LS1018c.[capacity]" & _
" FROM LS1018c" & _
" WHERE LS1018c.config" & strconfig & _
" AND LS1018c.boomlength = " & boomlength & _
" AND LS1018c.oprad = " & operatingradius & ";"

Finally, note that you had switched boomLength and operatingradius in your
comparison.

Suggestion: Add a debug.print StrSQL after you build your SQL statement and
look at it to see if it seems valid. You can cut and paste the statement
into a new query and try to run it. The error message will be better and
will probably highlight the problem areas. Once you have it working
correctly, you can delete or comment out the Debug.Print strSQL statement.
 
does strconfig have a leading blank in it?


John Spencer said:
I would expect an error on these two lines. In Access, the text property of
a control is only available when the control has the focus. You want the
Value property. The Value property is the default property, so you don't
really have to include it if that is what you want to return.

operatingradius = Val(txt_or.Value)
boomlength = Val(txt_bl.)

Next your SQL statement should read more like the following. You need to
pass the values of the variables and not the names of the variables in the
sql statement.

strSQL = "SELECT LS1018c.[capacity]" & _
" FROM LS1018c" & _
" WHERE LS1018c.config" & strconfig & _
" AND LS1018c.boomlength = " & boomlength & _
" AND LS1018c.oprad = " & operatingradius & ";"

Finally, note that you had switched boomLength and operatingradius in your
comparison.

Suggestion: Add a debug.print StrSQL after you build your SQL statement
and look at it to see if it seems valid. You can cut and paste the
statement into a new query and try to run it. The error message will be
better and will probably highlight the problem areas. Once you have it
working correctly, you can delete or comment out the Debug.Print strSQL
statement.

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
Click to expand...
Click to expand...
 
That shouldn't matter, David.

It's being set to

strconfig = "='" & Me.Combo_config & "'"

so that strSQL should look something like

SELECT LS1018c.[capacity] FROM LS1018c WHERE LS1018c.config='abc' AND ...

Spaces on either side of the equal sign are optional.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


David F Cox said:
does strconfig have a leading blank in it?


John Spencer said:
I would expect an error on these two lines. In Access, the text property
of a control is only available when the control has the focus. You want
the Value property. The Value property is the default property, so you
don't really have to include it if that is what you want to return.

operatingradius = Val(txt_or.Value)
boomlength = Val(txt_bl.)

Next your SQL statement should read more like the following. You need to
pass the values of the variables and not the names of the variables in
the sql statement.

strSQL = "SELECT LS1018c.[capacity]" & _
" FROM LS1018c" & _
" WHERE LS1018c.config" & strconfig & _
" AND LS1018c.boomlength = " & boomlength & _
" AND LS1018c.oprad = " & operatingradius & ";"

Finally, note that you had switched boomLength and operatingradius in
your comparison.

Suggestion: Add a debug.print StrSQL after you build your SQL statement
and look at it to see if it seems valid. You can cut and paste the
statement into a new query and try to run it. The error message will be
better and will probably highlight the problem areas. Once you have it
working correctly, you can delete or comment out the Debug.Print strSQL
statement.

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
Click to expand...
Click to expand...
Click to expand...
 
:-<

thinks: maximise the window, read all of the code ....


Douglas J. Steele said:
That shouldn't matter, David.

It's being set to

strconfig = "='" & Me.Combo_config & "'"

so that strSQL should look something like

SELECT LS1018c.[capacity] FROM LS1018c WHERE LS1018c.config='abc' AND ...

Spaces on either side of the equal sign are optional.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


David F Cox said:
does strconfig have a leading blank in it?


John Spencer said:
I would expect an error on these two lines. In Access, the text property
of a control is only available when the control has the focus. You want
the Value property. The Value property is the default property, so you
don't really have to include it if that is what you want to return.

operatingradius = Val(txt_or.Value)
boomlength = Val(txt_bl.)

Next your SQL statement should read more like the following. You need
to pass the values of the variables and not the names of the variables
in the sql statement.

strSQL = "SELECT LS1018c.[capacity]" & _
" FROM LS1018c" & _
" WHERE LS1018c.config" & strconfig & _
" AND LS1018c.boomlength = " & boomlength & _
" AND LS1018c.oprad = " & operatingradius & ";"

Finally, note that you had switched boomLength and operatingradius in
your comparison.

Suggestion: Add a debug.print StrSQL after you build your SQL statement
and look at it to see if it seems valid. You can cut and paste the
statement into a new query and try to run it. The error message will be
better and will probably highlight the problem areas. Once you have it
working correctly, you can delete or comment out the Debug.Print strSQL
statement.

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
Click to expand...
Click to expand...
Click to expand...
 
very thanks for all your kind help...
Asty said:
:-<

thinks: maximise the window, read all of the code ....


Douglas J. Steele said:
That shouldn't matter, David.

It's being set to

strconfig = "='" & Me.Combo_config & "'"

so that strSQL should look something like

SELECT LS1018c.[capacity] FROM LS1018c WHERE LS1018c.config='abc' AND ...

Spaces on either side of the equal sign are optional.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


David F Cox said:
does strconfig have a leading blank in it?


I would expect an error on these two lines. In Access, the text property
of a control is only available when the control has the focus. You want
the Value property. The Value property is the default property, so you
don't really have to include it if that is what you want to return.

operatingradius = Val(txt_or.Value)
boomlength = Val(txt_bl.)

Next your SQL statement should read more like the following. You need
to pass the values of the variables and not the names of the variables
in the sql statement.

strSQL = "SELECT LS1018c.[capacity]" & _
" FROM LS1018c" & _
" WHERE LS1018c.config" & strconfig & _
" AND LS1018c.boomlength = " & boomlength & _
" AND LS1018c.oprad = " & operatingradius & ";"

Finally, note that you had switched boomLength and operatingradius in
your comparison.

Suggestion: Add a debug.print StrSQL after you build your SQL statement
and look at it to see if it seems valid. You can cut and paste the
statement into a new query and try to run it. The error message will be
better and will probably highlight the problem areas. Once you have it
working correctly, you can delete or comment out the Debug.Print strSQL
statement.

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL Query def by vba not working.....please help 2
Code to search all columns in a table for a single search word 4

Back
Top