Solved can't figure where I'm going wrong

[Gasman]

Well times could cause an issue, but it did look like the dates were backwards from what was expected from what you had said.

Congrats on getting to the bottom of it.

The conts is handy rather than having to muck around with # all the time.

I just need to find it to see what it was called all the time (when I was coding) as my memory is so bad.

Fixed the problem...
when I looked at what dates were not showing up all of January and half of February would not work and a few days from the previous year.



I looked at the underlying table and found that some of the dates had times attached to them so 02/01/2019 10:32:55. I removed all the times as they are not required, don't know where they come from (likely a Now() function rather than a TODAY() function)

Now the Public Const thing works a treat.

Moral of the story, if you code looks like its working most of the time, check the the table data

 

[Cliff67]

Thanks for the Help Gasman your a star

 

[Gasman]

Thanks for the Help Gasman your a star

Not really, just been there, done that.

 

[Mark_]

Cliff67,

Something very important to remember when dealing with dates, and I only bring this up because you had posted

the data type is a short date so maybe that has been my problem all along. Does it convert it into a text type when formatting?

Dates are internally stored as numbers where the integer portion is the "Date" with a decimal for "Time". The format itself is never stored unless you are assigning a date to a text field.

Once you remember that ACCESS (or any other DB) is just storing a number and relying upon you to format it (intentionally or by default) you'll have an easier time understanding why saving it as a "Short date" doesn't impact how you can access/display the date.

SQL expects dates to be formatted as MM/DD/YYYY when you do queries so you have to make sure your formatting dates properly when writing them into the text string you are passing. It can be rather annoying, but once you become habituated to doing it some things become much easier.

 

[isladogs]

SQL expects dates to be formatted as MM/DD/YYYY when you do queries so you have to make sure your formatting dates properly when writing them into the text string you are passing.

Just to clarify, you can write dates in your default format such as dd/mm/yyyy in Access queries
However they need to be in mm/dd/yyyy or yyyy-mm-dd format for sql strings in VBA

 

[Cliff67]

Hi Mark
The original coding that created the record use the now() function to insert the date and time the record was raised. I changed it to the TODAY() function as the time the record was raised was irrelevant I just didn't use that field too much until now

 

[isladogs]

Access doesn't have a Today function. Did you mean Date()?

 

[Cliff67]

Access doesn't have a Today function. Did you mean Date()?

Isladogs yes of course I've been helping my son with Excel recently for his job and got it mixed up

Cliff