Solved What is going on here?

[Gasman]

I need to lookup a field that holds date and time, but with only the date part.
So I used a constant that I use all the time

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

My code was

Me.BloodPressure = DMin("Systolic", "tblPressure", "DateValue(PressureDateTime) = " & Format(Me.HealthDate, "strcJetDate"))

but that produces a date of 0tr30/04/2025Jet30ate ???

So I tried using a variable

Dim strDate As String

strDate = Format(Me.HealthDate, "strcJetDate")
Me.BloodPressure = DMin("Systolic", "tblPressure", "DateValue(PressureDateTime) = " & strDate)

but get the same value?

If I try in the Immediate Window with todays date

? format(date,strcJetDate)
#2025-04-30#

So what is going on? Any ideas please?
Field is DateTime and a Short Date.

 

[June7]

strcJetDate is a declared constant, don't enclose in quote marks. You didn't in Immediate Window so don't in your procedure.

 

[Pat Hartman]

Dates need to be encased in #

Me.BloodPressure = DMin("Systolic", "tblPressure", "DateValue(PressureDateTime) = #" & Format(Me.HealthDate, "strcJetDate") & "#")

 

[June7]

Pat, the #'s are included in the strcJetDate constant. See post #2. Tested and works.

 

[Pat Hartman]

Pat, the #'s are included in the strcJetDate constant. See post #2. Tested and works.

are you sure it is not "#date#" so the # are inside a string rather than delimiting it?

Build the string in a variable so you can see what is being passed.

 

[Gasman]

strcJetDate is a declared constant, don't enclose in quote marks. You didn't in Immediate Window so don't in your procedure.

Doh!
Thank you @June7

Just been through a 2.5 hour Diabetes Virtual presentation and I think my brain is fried for today.

 

[Gasman]

are you sure it is not "#date#" so the # are inside a string rather than delimiting it?

Build the string in a variable so you can see what is being passed.

@Pat Hartman, @June7 hit the nail on the head. my stupidity.

 

[Pat Hartman]

Good reason to build strings into variables so you can look at what your code has wrought

 

[Gasman]

Good reason to build strings into variables so you can look at what your code has wrought

I tried that as you can see, but still made the mistake of surrounding the constant with double quotes.
Stupid error on my part.

 

[cheekybuddha]

Public Const strcJetDate = "\#yyyy-mm-dd\#"    ' wrong

Public Const strcJetDate = "\#yyyy\-mm\-dd\#"  'correct

 

[cheekybuddha]

@June7 hit the nail on the head. my stupidity.

Oops! Missed that too!

 

[Gasman]

I d

Public Const strcJetDate = "\#yyyy-mm-dd\#"    ' wrong

Public Const strcJetDate = "\#yyyy\-mm\-dd\#"  'correct

I do not know what to tell you, but it works.
Providing I do not surround it with quotes of course.

 

[cheekybuddha]

I *think* that without the extra backslashes the dashes are treated as minus operators instead of string literals

 

[June7]

Seems Access recognizes hyphen as date part separator when enclosed with # or within string in CDate().

?#2-2-2025#
2/2/2025

?CDate("2-2-2025")
2/2/2025

Otherwise, you get subtraction.
?CDate(2-2-2025)
6/14/1894

Allen Browne shows forcing a date to U.S. MM/DD/YYYY arrangement http://allenbrowne.com/ser-36.html

 

[CJ_London]

Don’t know why you use backslash’s. I never have and never had a problem

 

[Gasman]

Don’t know why you use backslash’s. I never have and never had a problem

Something I found on a search. It worked when it was mm/dd/yyyy format and then I changed it to the current format after various mentions of that format here.
It worked, so never touched it after that.

 

[cheekybuddha]

Backslashes escape the literal characters to be paced in the pattern.

Otherwise, hash and dash have significance in the Format() pattern

 

[Gasman]

Backslashes escape the literal characters to be paced in the pattern.

Otherwise, hash and dash have significance in the Format() pattern

Certainly need it for the #

? format(date,"#yyyy-mm-dd#")
4577yyyy-mm-dd8
? format(date,"\#yyyy\-mm\-dd\#")
#2025-05-01#
? format(date,"\#yyyy-mm-dd\#")
#2025-05-01#

 

[cheekybuddha]

The dash is not a natural separator for VBA dates. It is part of a standard that MS doesn't follow. Best to be explicit just in case.

But if it works without, then all good

 

[Gasman]

The dash is not a natural separator for VBA dates. It is part of a standard that MS doesn't follow. Best to be explicit just in case.

But if it works without, then all good

I did have this at first

'Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

but have seen many times mentioned here, that the international format is also accepted, so changed it to that.

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