SQL query Run & stop without reason 1

[monoDeveloper]

Hello
I wrote this SQL & it was working properly, but when I added extra search parameter always return no result in search even table data is exist ....I want to know the reason please:

strSQL = " SELECT supp_Req_SN  FROM SupplyRequest  " & _
" WHERE( supp_Req_SN = " & [txtRequestNumber].Value & " and " & _
" supplierID=" & cboSupplier & " and " & _
" creator_U_ID=" & txtEmpNumber & " and " & _
" supp_Req_Date =" & [txtDate].Value & ")"

Debug.Print strSQL

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
Debug.Print rs.RecordCount

If (rs.RecordCount > 0) Then
MsgBox "Data process done successfully", vbInformation + vbOKOnly


inResult = DMax("supp_Req_SN", "SupplyRequest") + 1
Me.txtRequestNumber = inResult
Else
MsgBox "Data process failed", vbInformation
cboSupplier.SetFocus
cboSupplier.SelStart = 0
cboSupplier.SelLength = Len(cboSupplier.Text)

End If ' End IF after insert data & check it

after adding this part to Code: I face no result returned in my query.

 " supp_Req_Date =" & [txtDate].Value & ")"

even when I run same full query as MS.Access saved query it works , feel confused.

 

[Andrzejek]

My crystal ball does not show anything....

How is supp_Req_Date declared? As a Date?

What do you get from: [tt]Debug.Print strSQL[/tt] line?

Just a guess here:
[tt]
" supp_Req_Date =[red]#[/red]" & [txtDate].Value & "[red]#[/red])"
[/tt]
You may need to Format your Date....

Am I getting closer...?

Have fun.

---- Andy

 

[monoDeveloper]

Yes " supp_Req_Date" declared as a Date

I added the [highlight #FCE94F]"#"[/highlight] & it works now, but I have question why I didn't get error or problem at insertion SQL statement using same quot without formating or using the [highlight #FCE94F]#[/highlight] ?

 

[dhookom]

Dates are actually numbers. You first strSQL assumed a date like 4/17/2013 was 4 divided by 17 divided by 2013 which is approximately 0.000117.

Duane
Hook'D on Access
MS Access MVP

 

[monoDeveloper]

thanks for your answers