Hi
I'm having a problem with the output of my SQL query but I dont know if its a syntax problem or whether I've got it totally wrong - I was hoping someone could have a look at it and perhaps tell me where I'm going wrong
I have a table with four fields - 3 of which are text (reqno, type, barcode) and the fourth a number field (period)
I have a form on which I have set up a text box (Reqsearch) and two combo boxes (Typesearch and Yearsearch). I want the user to be able to type in text into Reqsearch and then select type and period. The query is run by a button and the output is sent to another text box called barcode.
The syntax to do this is
Private Sub Command14_Click()
Dim strSQL As String
strSQL = "SELECT DISTINCT tblRecall.Barcode FROM tblRecall WHERE tblRecall.Reqno = '" & Reqsearch & "' AND '" & Typesearch & "' AND '" & Yearsearch & "';"
Barcode.RowSource = strSQL
End Sub
This works fine except in that it'll return all matching reqno and types selected but it returns them for all years i.e. its like its paid no attention to the third criteria and given me the year selected. If I type in 'A1234' into the Reqsearch box, select 'Blocks' from the Typesearch combobox and select the '1997' in the yearsearch combo box, I get all the barcodes which match 'A1234' and 'Blocks' but for years 1997 through to 2005.
I know number fields dont have a single quote and I have removed it so it read only
" & Yearsearch & "
but I got the same result. I decided to keep the period field as a number and not date as I only need the year and thought a number would do just as well and keep things simple. I dont know what else to try
Could someone point me in the right direction
many thanks
Steve
I'm having a problem with the output of my SQL query but I dont know if its a syntax problem or whether I've got it totally wrong - I was hoping someone could have a look at it and perhaps tell me where I'm going wrong
I have a table with four fields - 3 of which are text (reqno, type, barcode) and the fourth a number field (period)
I have a form on which I have set up a text box (Reqsearch) and two combo boxes (Typesearch and Yearsearch). I want the user to be able to type in text into Reqsearch and then select type and period. The query is run by a button and the output is sent to another text box called barcode.
The syntax to do this is
Private Sub Command14_Click()
Dim strSQL As String
strSQL = "SELECT DISTINCT tblRecall.Barcode FROM tblRecall WHERE tblRecall.Reqno = '" & Reqsearch & "' AND '" & Typesearch & "' AND '" & Yearsearch & "';"
Barcode.RowSource = strSQL
End Sub
This works fine except in that it'll return all matching reqno and types selected but it returns them for all years i.e. its like its paid no attention to the third criteria and given me the year selected. If I type in 'A1234' into the Reqsearch box, select 'Blocks' from the Typesearch combobox and select the '1997' in the yearsearch combo box, I get all the barcodes which match 'A1234' and 'Blocks' but for years 1997 through to 2005.
I know number fields dont have a single quote and I have removed it so it read only
" & Yearsearch & "
but I got the same result. I decided to keep the period field as a number and not date as I only need the year and thought a number would do just as well and keep things simple. I dont know what else to try
Could someone point me in the right direction
many thanks
Steve
