SQL select statement question

[Guest]

On my form there are 5 search fields with dropdowns: 1) customer_no-number,
2) customer_name-string 3) volume_flag-checkbox integer, 4)
contract_no-numberr, and 5) doc_type-string. The user can select 1 or
multiple fields to search on. When they click on the "search" button,
everything works except this new field, volume_flag, that I just added.

I have looked at my code all day and still can't see why it is not selecting
the volume_flag. Could you please point me in the right direction? Thanks!!

Here it is...
Private Sub Search_button_Click()
Dim findFlag As String
Dim rowCount as integer
Dim rst_contractrec as New ADODB.recordset
Dim SQLstmt as string
Dim I as integer
Dim Imax as integer

Imax = Me!main_list_box.listcount - 1
While Imax >=0
Me!main_list_box.removeItem (Imax)
Imax = Imax - 1
Wend
SQLStmt = "select a.contract_no,
a.doc_type,a.customer_no,b.customer_name, a.volume_flag from EC A, customer B
where a.customer_no=b.customer_no"

I keep getting "variable not defined" on volume_flag. I have looked at my
code all day and still can't see why it is not selecting the volume_flag.
Could you please point me in the right direction? Thanks!!

 

[Guest]

You don't need a ";" at the end of the SQLstmt on a select if it is between "
". Any other ideas, pleeeeeeease? Thank you!

 

[Marshall Barton]

On my form there are 5 search fields with dropdowns: 1) customer_no-number,
2) customer_name-string 3) volume_flag-checkbox integer, 4)
contract_no-numberr, and 5) doc_type-string. The user can select 1 or
multiple fields to search on. When they click on the "search" button,
everything works except this new field, volume_flag, that I just added.

I have looked at my code all day and still can't see why it is not selecting
the volume_flag. Could you please point me in the right direction? Thanks!!

Here it is...
Private Sub Search_button_Click()
Dim findFlag As String
Dim rowCount as integer
Dim rst_contractrec as New ADODB.recordset
Dim SQLstmt as string
Dim I as integer
Dim Imax as integer

Imax = Me!main_list_box.listcount - 1
While Imax >=0
Me!main_list_box.removeItem (Imax)
Imax = Imax - 1
Wend
SQLStmt = "select a.contract_no,
a.doc_type,a.customer_no,b.customer_name, a.volume_flag from EC A, customer B
where a.customer_no=b.customer_no"

I keep getting "variable not defined" on volume_flag. I have looked at my
code all day and still can't see why it is not selecting the volume_flag.
Could you please point me in the right direction? Thanks!!

I have no idea what the list box has to do with anything,
but the SQL statement requires [ ] around names that contain
spaces or any other non alphanumeric character (except _).