delete statement dosen't work on Form

[monoDeveloper]

Hello everybody

This delete statement dosen't work on Form , but works from Saved Query:

Supplier_OrderSN is Text
Supp_Order_Date is Date
remain columns are Numbers

 DoCmd.RunSQL ("Delete * from SupplyOrder  " & _
" Where (  " & _
" Supp_Order_SN =" & [txtReceiveNumber].Value & "  and " & _
" From_Supplier_ID=" & [cboSupplier].Value & "  and " & _
" Supp_Order_Date= # " & [txtReceiveDate].Value & " #   and " & _
" Receive_U_ID=" & [txtEmpNumber].Value & "  and " & _
" Supplier_OrderSN=' " & [txtSupplierNumber].Value & " ')")

Can anybody help ?

 

[monoDeveloper]

That is what I see in "Immediate window"

 Delete * from SupplyOrder   Where (   Supp_Order_SN =25  and  From_Supplier_ID=9  and  Supp_Order_Date=  4/19/2013   and  Receive_U_ID=4  and  Supplier_OrderSN=' 521/2013 ')

 

[Andrzejek]

try this:

Dim strSQL As String

strSQL = "Delete * from SupplyOrder  " & _
" Where (  " & _
" Supp_Order_SN = " & [txtReceiveNumber].Value & " and " & _
" From_Supplier_ID = " & [cboSupplier].Value & " and " & _
" Supp_Order_Date = # " & [txtReceiveDate].Value & " # and " & _
" Receive_U_ID = " & [txtEmpNumber].Value & " and " & _
" Supplier_OrderSN = ' " & [txtSupplierNumber].Value & " ')"

Debug.Print strSQL

DoCmd.RunSQL strSQL

And show us what you have in Immediate Window

Have fun.

---- Andy

 

[monoDeveloper]

It works now

just delete space between Single quotes to have exact string : )

 

[Andrzejek]

So what happened to your #'s around the Supp_Order_Date in
[tt]and Supp_Order_Date= 4/19/2013 [/tt]
?

Have fun.

---- Andy

 

[dhookom]

Andy,
I would have removed the spaces inside the delimiters:

Dim strSQL As String
strSQL = "Delete * from SupplyOrder  " & _
" Where (  " & _
" Supp_Order_SN = " & [txtReceiveNumber].Value & " and " & _
" From_Supplier_ID = " & [cboSupplier].Value & " and " & _
" Supp_Order_Date = #[highlight #CC0000] [/highlight]" & [txtReceiveDate].Value & "[highlight #EF2929] [/highlight]# and " & _
" Receive_U_ID = " & [txtEmpNumber].Value & " and " & _
" Supplier_OrderSN = '[highlight #EF2929] [/highlight]" & [txtSupplierNumber].Value & "[highlight #EF2929] [/highlight]')"

Debug.Print strSQL

DoCmd.RunSQL strSQL

Duane
Hook'D on Access
MS Access MVP

 

[monoDeveloper]

I already did remove the spaces , thanks