Data Mismatch but can't find where (1 Viewer)

[BeardedSith]

I ran into a data mismatch error and for some reason, I just can't seem to find where it is. I "think" I've narrowed it down to one column in one table, and it's a date entry. So...

Here is the function that keeps bugging out on me:

Public Function IssueRewards()
Dim IssueSQL As String
IssueSQL = "INSERT INTO tblRewards ( CustomerID, IssueDate, IssueAmount )" _
            & " SELECT qryEligibility.ID, Dateserial(Year(Now()),Month(Now()),Day(Now())) AS Today, qryEligibility.RealAmount" _
            & " FROM qryEligibility" _
            & " WHERE (((qryEligibility.IsEligible)=1))"

CurrentDb.Execute IssueSQL, dbFailOnError
Debug.Print IssueSQL
End Function

To get here, we go through several subqueries to create a list of eligibles for this INSERT statement:

sqryCalc0 (This isn't giving me any errors):

SELECT tbPurchases.MemberID, DateValue([PurchaseDate]) AS DatePurchase, tbPurchases.PurchaseAmount
FROM tblMembers INNER JOIN tbPurchases ON tblMembers.ID = tbPurchases.MemberID;

sqryCalc1 (This also doesn't give any errors):

SELECT tblMembers.ID, Nz(IIf(Max([tblRewards]![IssueDate])<DateSerial(Year(Now()),1,1),DateSerial(Year(Now()),1,1),Max([tblRewards]![IssueDate])),DateSerial(Year(Now()),1,1)) AS RealDate
FROM tblRewards RIGHT JOIN tblMembers ON tblRewards.CustomerID = tblMembers.ID
GROUP BY tblMembers.ID;

sqryCalc2 (This is where the errors start):

SELECT sqryCalc0.MemberID, sqryCalc0.DatePurchase, Sum(sqryCalc0.PurchaseAmount) AS DailyPurchaseTotal
FROM sqryCalc0 INNER JOIN sqryCalc1 ON sqryCalc0.MemberID = sqryCalc1.ID
WHERE (((sqryCalc0.DatePurchase)>=[RealDate] And (sqryCalc0.DatePurchase)>=CDate("1/1/" & Year(Date()))))
GROUP BY sqryCalc0.MemberID, sqryCalc0.DatePurchase;

sqryCalc3 (This also has the same error):

SELECT sqryCalc1.ID, Count(sqryCalc2.MemberID) AS NumPurchases, Sum(Nz([DailyPurchaseTotal],0)) AS SumOfPurchaseAmount, sqryCalc1.RealDate
FROM sqryCalc1 LEFT JOIN sqryCalc2 ON sqryCalc1.ID = sqryCalc2.MemberID
GROUP BY sqryCalc1.ID, sqryCalc1.RealDate;

Looking at the two queries that are firing back errors, I had narrowed it down to either a date column or a currency column. So I surfed through the data and as far as I can tell, they're all in the tables correctly. It's only a handful of records, so it was pretty easy to fish through them looking for coherency. Which leads me to believe it's something to do with how these two queries are parsing dates and currency (more likely to be dates).

Something I tried while fishing through the data was the modify sqryCalc2 one column at a time until the mismatch error went away. Only when DatePurchase and the WHERE clause are removed do the errors cease. That leads me to believe that there's something fishy with the date columns. With some assumptions in tow, I feel like it has to do with how I'm handling CDate and RealDate(Which uses DateSerial and a row in the table).

Any ideas? More importantly, any tips and tricks on how to track this bug down a little easier?

I'm also including an image of the database setup for PurchaseDate within tbPurchases:

 

[BeardedSith]

Some more testing leaves me even more perplexed. I don't get the error all the time. Only on some computers. Could there be an add-in I'm missing?

 

[CJ_London]

why are you use dateserial and now?

this
Dateserial(Year(Now()),Month(Now()),Day(Now())) AS Today

could just be

Date AS Today

similarly

DateValue([PurchaseDate]) AS DatePurchase

could just be

[PurchaseDate] AS DatePurchase


what does your formatdatevalue return in your default value? if a string, this may be causing problems

it could just be Date

You may not be aware but dates are stored as doubles - the bit before the decimal point represents the day as the number of days since 31/12/1899 - today is 44039. The bit after the decimal point represents the number of seconds divided by 86400 (the number of seconds in a day) - so the time now is

?now()
27/07/2020 16:58:02
?cdbl(now)
44039.7069907407

so yesterdays date is date-1

 

[BeardedSith]

Moving down your list:
1) The different uses of Dateserial/Now/Date were all troubleshooting from ages ago. I'll give your suggestions to whirl to see if they work.
2) FormatDateValue puts out today's Small Date (7/27/2020) so it too could probably just be Date.

After trying a couple of your ideas, I THINK I got it working correctly. So it was one of those date formatting things I had in there probably messing with stuff. I won't be for sure if it worked until I head back into the office on Wednesday. Much appreciated your help, thanks!