Solved can't figure where I'm going wrong (1 Viewer)

Gasman

Enthusiastic Amateur
Local time
Today, 20:22
Joined
Sep 21, 2011
Messages
14,047
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. :D

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. :eek:
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 :mad::eek:
 

Cliff67

Registered User.
Local time
Today, 13:22
Joined
Oct 16, 2018
Messages
175
Thanks for the Help Gasman your a star
 

Mark_

Longboard on the internet
Local time
Today, 13:22
Joined
Sep 12, 2017
Messages
2,111
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

MVP / VIP
Local time
Today, 20:22
Joined
Jan 14, 2017
Messages
18,186
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

Registered User.
Local time
Today, 13:22
Joined
Oct 16, 2018
Messages
175
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

MVP / VIP
Local time
Today, 20:22
Joined
Jan 14, 2017
Messages
18,186
Access doesn't have a Today function. Did you mean Date()?
 

Cliff67

Registered User.
Local time
Today, 13:22
Joined
Oct 16, 2018
Messages
175
Access doesn't have a Today function. Did you mean Date()?
Isladogs yes of course :eek: I've been helping my son with Excel recently for his job and got it mixed up

Cliff
 

Users who are viewing this thread

Top Bottom