Date Mismatch error (1 Viewer)

[Bechert]

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

 

[isladogs]

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

Sub TESTDate()

Dim strDate As String, dteDate As Date

strDate = "20170128"

'EITHER of the next 2 lines will work
'strDate = Left(strDate, 4) & "-" & Mid(strDate, 5, 2) & "-" & Right(strDate, 2)
'OR
strDate = Mid(strDate, 5, 2) & "/" & Right(strDate, 2) & "/" & Left(strDate, 4)

dteDate = CDate(Format(strDate, "mm/dd/yyyy"))

Debug.Print dteDate

End Sub

I expect someone else will simplify further

 

[plog]

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

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.

 

[arnelgp]

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

FileDate:ConvertCenturyMonthDayToStandardDateFunction([fieldFromFile])
or
ConvertCenturyMonthDayToStandardDateFunction([fieldFromFile]) As FileDate

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

"SELECT *,ConvertCenturyMonthDayToStandardDateFunction([fieldFromFile]) AS FileDate FROM yourfile WHERE ConvertCenturyMonthDayToStandardDateFunction([fieldFromFile]) = [ActivityDate] ..."

 

[ashleedawg]

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:

debug.Print format(cdate("12/1/2017"),"mmm d, yyyy")  ' returns:  Dec 1, 2017
debug.Print format(cdate("13/1/2017"),"mmm d, yyyy")  ' returns:  Jan 13, 2017

'What does this function return for you? 
debug.Print format("Dec 13, 2014","Short Date")  '(I get 2014-12-13)

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:

debug.print CDate(Format("20170128", "@@@@/@@/@@"))

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

K, I'm done.