VBA for looking up a record between Start and End Data.

[tucker61]

Trying to get my head around this VBA - This code currently works - but i need to introduce a new charge in my table starting from 01-07-2024 - So i need to change this code to look for the StartDate and The EndDate.

Initial Code

 Me.tbRate = Nz(DLookup("Unit_Cost", "TblQCPreRetailSuppliers", "Supp_Code='" & Forms!FrmPreRetailJobs!CboxSupplier & "' AND Forms!FrmPreRetailcharges.tbChargeDate >StartDate"), "")

I have tried

 Me.tbRate = Nz(DLookup("Unit_Cost", "TblQCPreRetailSuppliers", "Supp_Code='" & Forms!FrmPreRetailJobs!CboxSupplier & "' AND Forms!FrmPreRetailcharges.tbChargeDate BETWEEN StartDate EndDate"), "")

but get a error (Below)

The underlying table looks like this.

Error message - i think this is due to the end date being blank - but not 100% sure.

Any help appreciated.

 

[arnelgp]

maybe:

..Forms!FrmPreRetailJobs!CboxSupplier & "' AND " & Format$(Forms!FrmPreRetailcharges.tbChargeDate,"#mm/dd/yyyy#") & " BETWEEN [StartDate] And [EndDate]"), "")

 

[tucker61]

This gives me a strange error - when i debug i get the below.
? Format(Forms!FrmPreRetailcharges.tbChargeDate, "#mm/dd/yyyy#")
4540mm/dd/yyyy7

Not sure if it matters but the back end is stored in SQL Sever, and data type is DateTime.

 

[tucker61]

This gives me a strange error - when i debug i get the below.
? Format(Forms!FrmPreRetailcharges.tbChargeDate, "#mm/dd/yyyy#")
4540mm/dd/yyyy7

Not sure if it matters but the back end is stored in SQL Sever, and data type is DateTime. View attachment 113849

Think i have sort of sorted it - when changing the code to look at todays date it works. So for some reason does not like the TbChargeDate field.

Me.tbRate = Nz(DLookup("Unit_Cost", "TblQCPreRetailSuppliers", "Supp_Code='" & Forms!FrmPreRetailJobs!CboxSupplier & "' AND DATE() BETWEEN [StartDate] And [EndDate]"), "")

 

[Gasman]

I used a constant for formatting my dates, it used to be in the mm/dd/yyyy format, but recently changed it to as shown

Public Const strcJetDate = "\#yyyy-mm-dd\#"  'Needed for dates in queries as Access expects USA but will accept ISO format.

This gives me a strange error - when i debug i get the below.
? Format(Forms!FrmPreRetailcharges.tbChargeDate, "#mm/dd/yyyy#")
4540mm/dd/yyyy7

Not sure if it matters but the back end is stored in SQL Sever, and data type is DateTime. View attachment 113849

 

[cheekybuddha]

This gives me a strange error - when i debug i get the below.
? Format(Forms!FrmPreRetailcharges.tbChargeDate, "#mm/dd/yyyy#")
4540mm/dd/yyyy7

If you include the octothorpes in the format string they must be escaped as literals using a backslash - otherwise, they have a meaning in terms of formatting

?Format(Forms!FrmPreRetailcharges.tbChargeDate, "\#mm/dd/yyyy\#")

 

[tucker61]

If you include the octothorpes in the format string they must be escaped as literals using a backslash - otherwise, they have a meaning in terms of formatting

?Format(Forms!FrmPreRetailcharges.tbChargeDate, "\#mm/dd/yyyy\#")

Thanks, this resolved the issue.