Date format not firing code correctly

[papic1972]

Hi All,

I am really struggling with getting this code to work correctly. I have the code in the after update event of combo box called cboDriver. The idea is when the cboDriver is updated & if DeliveryDate <= 10/11/2009 then the driver rates should be extracted from qryContractRatesAdbri1. Likewise if the DeliveryDate >= 11/11/2009 then the rates should be extracted from qryContractRatesAdbri2.

Can anyone tell me if i've syntaxed this code correctly. It's doing my head in!


If Me.Qty >= 3 And Me.DeliveryDate <= #10/11/2009# Then
Forms!frmCandM!subfrmAdbriMasonry!JDRate = DLookup("[JDRate]", "qryContractRatesAdbri1", "

=Forms!frmCandM!subfrmAdbriMasonry!Code")
Forms!frmCandM!subfrmAdbriMasonry!SubyRate = DLookup("[SubyRate]", "qryContractRatesAdbri1", "[Code]=Forms!frmCandM!subfrmAdbriMasonry!Code")
Forms!frmCandM!subfrmAdbriMasonry!MinQty = Me.Qty
Forms!frmCandM!subfrmAdbriMasonry!JDPayment = Me.JDRate * Me.MinQty
Forms!frmCandM!subfrmAdbriMasonry!SubPayment = Me.SubyRate * Me.MinQty
Forms!frmCandM!subfrmAdbriMasonry!Balance = Me.JDPayment - Me.SubPayment



Me.Suby = Me.cboDriver.Column(2)
Me.Mobile = Me.cboDriver.Column(5)
Me.Code = Me.Yard + Me.cboType + Me.Area + Me.Suby

Me.Qty.Requery
Me.TruckNo = Me.cboDriver.Column(3)
Me.Rego = Me.cboDriver.Column(4)
Else



If Me.Qty < 3 And Me.DeliveryDate >= #11/11/2009# Then


Forms!frmCandM!subfrmAdbriMasonry!JDRate = DLookup("[JDRate]", "qryContractRatesAdbri2", "[Code]=Forms!frmCandM!subfrmAdbriMasonry!Code")
Forms!frmCandM!subfrmAdbriMasonry!SubyRate = DLookup("[SubyRate]", "qryContractRatesAdbri2", "[Code]=Forms!frmCandM!subfrmAdbriMasonry!Code")
Forms!frmCandM!subfrmAdbriMasonry!MinQty = 3
Forms!frmCandM!subfrmAdbriMasonry!JDPayment = Me.JDRate * Me.MinQty
Forms!frmCandM!subfrmAdbriMasonry!SubPayment = Me.SubyRate * Me.MinQty
Forms!frmCandM!subfrmAdbriMasonry!Balance = Me.JDPayment - Me.SubPayment



Me.Suby = Me.cboDriver.Column(2)
Me.Mobile = Me.cboDriver.Column(5)
Me.Code = Me.Yard + Me.cboType + Me.Area + Me.Suby

Me.Qty.Requery
Me.TruckNo = Me.cboDriver.Column(3)
Me.Rego = Me.cboDriver.Column(4)

End If

 

[missinglinq]

Assuming that 10/11/2009 and 11/11/2009 are supposed to be one day apart, as your post suggests, you're using a non-US date format, and this leads to all kinds of problems in Access!

Allen Browne, from "Down Under," where they also use the dd/mm/yyyy format for dates, has an excellent white paper on the subject that can be found here:

http://www.allenbrowne.com/ser-36.html

 

[papic1972]

Thank you!