dml -sql syntax

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

B McD - 21 Apr 2004 23:27 GMT

hi group

I am trying to write a select statement with a where condition and an "and"
operator and need some help.

my app uses two combo boxes to solicit input.
I am using the cbo.itemdata(cbo.listindex) from each in my select statement.
Vb is choking on my many many attempts.

"Select * from Query where " & _
" Supplierkey = " & cboSupplier.ItemData(cboSupplier.ListIndex) And " & _
"(ModelsKey = " & cboModels.ItemData(cboModels.ListIndex))

To test the routine I inserted numeric values for the
cbo.itemdata(cbo.listindex) and
was able to make it work. However, I need some expert help on getting this
statement
to function.

Any ideas greatly appreciated

B

Reply to this Message

George Copeland - 29 Apr 2004 23:27 GMT

> my app uses two combo boxes to solicit input.
> I am using the cbo.itemdata(cbo.listindex) from each in my select statement.
[quoted text clipped - 9 lines]
> statement
> to function.

Yike. First of all, move the control values to local variables and take
them out of the middle of your SQL statement. Otherwise, you will never
know what SQL you are executing.

Second of all, you have some SQL syntax errors, and they are hard to see
because of the way you have written your SQL. I rewrote yours the way I do
it:

Dim ls_SQL As String
Dim ll_SupplierKey As Long
Dim ll_ModelsKey As Long

With Me.cboSupplier
If .ListIndex = ml_CBO_NONE_SELECTED Then
'no data
GoTo Routine_Exit
Else
ll_SupplierKey = .ItemData(.ListIndex)
End If
End With

With Me.cboModels
If .ListIndex = ml_CBO_NONE_SELECTED Then
'no data
GoTo Routine_Exit
Else
ll_ModelsKey = .ItemData(.ListIndex)
End If
End With

ls_SQL = ls_SQL & "SELECT "
ls_SQL = ls_SQL & "* "
ls_SQL = ls_SQL & "FROM "
ls_SQL = ls_SQL & "Query "
ls_SQL = ls_SQL & "WHERE "
ls_SQL = ls_SQL & "Supplierkey "
ls_SQL = ls_SQL & "= "
ls_SQL = ls_SQL & CStr(ll_SupplierKey)
ls_SQL = ls_SQL & " AND "
ls_SQL = ls_SQL & "ModelsKey "
ls_SQL = ls_SQL & "= "
ls_SQL = ls_SQL & CStr(ll_ModelsKey)

Debug.Print ls_SQL

At this point you can grab the SQL out of the debug window and execute it
separate from your app. This will tell you where your SQL problems are.

Good luck with your app.

Reply to this Message

VB Forum / General 2 / April 2004

dml -sql syntax