Date Mismatch error

Hello, I am processing a file from another application.
All is going well except the date comparison within my application (ActivityDate as Date/Time with an input mask of 99/99/0000;0;_).
The date field in the incoming file is text: "20170128". It will always be 8 digits.
I want to convert this date to #01/28/2017# so I can compare ActivityDate to the incoming date.

I run a query that calls the function below to convert the incoming date to a standard Access date. In a second query each date is in a different table. I join the two tables on the dates .
I have created a subroutine to do the conversion. The converted date looks correct but I receive a Mismatch error when comparing the dates in the query.
Here is the code in a function:
I split the incoming field into century / month / day
DtDate as Date
VarDate = DateSerial(VarCentury, VarMonth, VarDay)
If IsDate(VarDate) Then
DtDate = VarDate
ConvertCenturyMonthDayToStandardDateFunction = DtDate
Else
Error
The code never goes to Error.

Thanks for help.
Bill

I think you're making this too complicated...
No idea why you had varCentury for example

Try this - it has 2 possible methods - both work


I expect someone else will simplify further

The converted date looks correct but I receive a Mismatch error when comparing the dates in the query

Click to expand...

Then you probably aren't comparing dates. The mistake most people make is thinking that text that looks like a date to human eyes is a date in the eyes of the computer.

My guess is one of those "dates" is a string and the other is an actual date. I would use DateValue() to convert the string to a date so that you can compare them.

if you are using an expression in your query, ie:




you should include the function in your comparison and not the calculated field (FileDate on my example)

Hi Bechert,

There must be some reason that you are specifically referring to the century? Perhaps you're dealing with historical dates or working towards Julian?


Beyond that I have some thoughts -- but I'll likely just confuse matters, so everyone stop reading now.


Ridder's function above works properly for me with your example date of 20170128. However he's in UK, I'm in Canada, and you're in USA, and it's not unheard of for a user's location (and therefore, Regional Settings) to affect the behaviour of CDATE and DATESERIAL. [or the spelling of 'behaviour' ]

Both functions operate internally based on the regional setting for 'Short Date':

• UK: dd/mm/yyyy
• USA: mm/dd/yyyy
• Canada: dd/mm/yyyy - but mine's set to yyyy/mm/dd

Unfortunately, even that has its exceptions depending on the value of each m-d-y datepart. For example, for me:

But back to the Century thing... If that's a level of detail that you need, you might require something completely different. How about this one:

Either way, DATESERIAL won't work with a Century argument.

K, I'm done.