VBA for looking up a record between Start and End Data. (1 Viewer)

tucker61

Registered User.
Local time
Yesterday, 21:06
Joined
Jan 13, 2008
Messages
327
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
Code:
 Me.tbRate = Nz(DLookup("Unit_Cost", "TblQCPreRetailSuppliers", "Supp_Code='" & Forms!FrmPreRetailJobs!CboxSupplier & "' AND Forms!FrmPreRetailcharges.tbChargeDate >StartDate"), "")

I have tried
Code:
 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.
1714121855006.png


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

1714122027353.png



Any help appreciated.
 

Attachments

  • 1714121959489.png
    1714121959489.png
    35.8 KB · Views: 30

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 12:06
Joined
May 7, 2009
Messages
19,249
maybe:
Code:
..Forms!FrmPreRetailJobs!CboxSupplier & "' AND " & Format$(Forms!FrmPreRetailcharges.tbChargeDate,"#mm/dd/yyyy#") & " BETWEEN [StartDate] And [EndDate]"), "")
 

tucker61

Registered User.
Local time
Yesterday, 21:06
Joined
Jan 13, 2008
Messages
327
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.
1714137069862.png
 

tucker61

Registered User.
Local time
Yesterday, 21:06
Joined
Jan 13, 2008
Messages
327
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.

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

Gasman

Enthusiastic Amateur
Local time
Today, 05:06
Joined
Sep 21, 2011
Messages
14,607
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

Code:
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

AWF VIP
Local time
Today, 05:06
Joined
Jul 21, 2014
Messages
2,378
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
Code:
?Format(Forms!FrmPreRetailcharges.tbChargeDate, "\#mm/dd/yyyy\#")
 

tucker61

Registered User.
Local time
Yesterday, 21:06
Joined
Jan 13, 2008
Messages
327
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
Code:
?Format(Forms!FrmPreRetailcharges.tbChargeDate, "\#mm/dd/yyyy\#")
Thanks, this resolved the issue.
 

Users who are viewing this thread

Top Bottom