Date Mismatch in Query

[richlocus]

Hello:

I have a query that filters records from a huge database to only select the current year. I'm comparing the year from the linked table database to the actual current year which I store in a global variable as an integer (i.e. 2022). Records on that database number in the millions and they start in 2010.

I only want the current year.

In my query, I use the following query builder statement shown below to hopefully convert a date/time field coming from the external database to an integer year so that it would compare properly with the Criteria which contains 2022 as an integer. The function in the criteria definitely creates an integer 2022.

shpdate: CInt(Year([dbo_invhdr].[shpdate]))

Unfortunately, it doesn't work, so I have to wait until millions of records are accumulated (I use the Ctrl-Shift-Break to stop it).

I attached a few examples that might help.

What is wrong with the statement shown above?

Thanks,
Rich Locus

[HansV]

As an experiment, add a condition on another field temporarily that is guaranteed to make the query return only a few records, and remove the criteria from shpdate.
What do you see in the shpdate column?

[richlocus]

Hans.. I just read your note. I will take your suggestion immediately. I actually wrote a VBA loop to examine and compare the values. It all checked out, yet when I run it for whatever reason the dates don't match. The code below showed they are equal.

Option Explicit

' ***********************************************************************
' Review Date Formats
' ***********************************************************************
Public Function ReviewDateFunctions()

Dim db As DAO.Database
Dim recIn As DAO.Recordset
Dim intDateFromFunction As Integer
Dim intDateFromRecordIn As Integer

' ***********************************************************************
' Open the Files
' ***********************************************************************
Set db = CurrentDb()
Set recIn = db.OpenRecordset("qryInvoiceForYearlyReport")

' ***********************************************************************
' Loop Through Each Record And Calculate The Percentage Distribution
' ***********************************************************************
Do

' ***********************************************************************
' Show The Dates
' ***********************************************************************
MsgBox (recIn!shpDate)
MsgBox (GetSalesDBCurrentYear())
intDateFromFunction = GetSalesDBCurrentYear()
intDateFromRecordIn = recIn!shpDate

If intDateFromFunction = intDateFromRecordIn Then
MsgBox ("Date Equal") ' This was displayed showing the date were equal. I'm baffled.
Else
MsgBox ("Dates Not Equal")
End If


Skip_Record:
Loop Until recIn.EOF

recIn.Close
Set recIn = Nothing
Set db = Nothing

End Function

[richlocus]

Hans:
Thanks for the debugging tip. The code was correct, but I did not image that this database contained over 50 million records. It took a huge amount of time so I was misled. When I cut the size down to a test version, everything worked.
Rich