sql syntax comparing fields

[stupot1987]

hey im new to this site but found it has helped me in the past with other similar problems. i want to be able to compare 2 fields in my sql my code is as follows:

 
Private Sub Command1_Click()
Exit Sub
Adodc1.RecordSource = "SELECT Stock.[Product Code], Stock.Description, Stock.[Quantity in Stock], Stock.[Re Order Level] FROM Stock; 
 WHERE Stock.[Quantity in Stock]) < Stock.[Re Order Level];"
Adodc1.Recordset.Requery
Adodc1.Refresh
DataGrid1.Refresh
End Sub
 

The bold part is where i appear to be having problems. am i missing quotations or brackets or something?
thnx in advance

[jasperz01]

hey im new to this site but found it has helped me in the past with other similar problems. i want to be able to compare 2 fields in my sql my code is as follows:

 
Private Sub Command1_Click()
Exit Sub
Adodc1.RecordSource = "SELECT Stock.[Product Code], Stock.Description, Stock.[Quantity in Stock], Stock.[Re Order Level] FROM Stock; 
 WHERE Stock.[Quantity in Stock]) < Stock.[Re Order Level];"
Adodc1.Recordset.Requery
Adodc1.Refresh
DataGrid1.Refresh
End Sub
 

The bold part is where i appear to be having problems. am i missing quotations or brackets or something?
thnx in advance

Lose the semicolon ( ; ) at the end of your FROM Stock; but leave it at the end of your sentence.

like this:

Adodc1.RecordSource = "SELECT Stock.[Product Code], Stock.Description, Stock.[Quantity in Stock], Stock.[Re Order Level] FROM Stock WHERE Stock.[Quantity in Stock]) < Stock.[Re Order Level];"
 

Jasper

[stupot1987]

cheers, but it still aint doin nothing! datagrid doesnt show the output from the query. :(

[jasperz01]

cheers, but it still aint doin nothing! datagrid doesnt show the output from the query. :(

Ok, try using Access Query Analyzer to troubleshoot the query I'd say:

http://www.sliver.com/dotnet/AccessQueryAnalyzer/

Good luck,

Jasper

[stupot1987]

guys im stil struggling on this one i feel its somethin to do with quotation marks!! took me ages to find out how to use them in a text field! any suggestions?