Inconsistent SQL Server Query Behavior (Form Reference Criteria vs Hard Coded Date) (1 Viewer)

[ions]

Hello MS Access Expert,

I am experiencing different query results when linked to SQL Server Tables, specifically, when I hard code criteria vs. a Form Reference. Note I don't experience this discrepancy when using Local MS Access Tables. I have attached the example database as a Zip.

I have now also added a video demonstrating the issue.

Thank you for your assistance and explanation for this inconsistent behavior.

1) Open the Database and Open frmA

2) Run qryCorrect and qryCorrectNOT which return the same Results because they are currently querying the local tables.

3) Migrate dbo_tblAccess and dbo_tblBAccess to SQL Server.

4) Run qryCorrect and qryCorrectNOT on the SQL Server Tables. You will now notice the Results are no longer the same.

SQL Server Incorrect Answer

Local Tables Correct Answer

 

[AccessBlaster]

Birthday / BirthDay seems to be inconsistent between the two tables / queries, not sure to what effect it has, if any.

 

[ions]

Birthday / BirthDay seems to be inconsistent between the two tables / queries, not sure to what effect it has, if any.

AccessBlaster if you mean the data within the two tables is not consistent that is by design to demonstrate the issue.

 

[GPGeorge]

AccessBlaster if you mean the data within the two tables is not consistent that is by design to demonstrate the issue.

Wait. The problem is that the two queries return inconsistent results.

You illustrate that by using inconsistent data for the two tables in the two queries?

 

[ions]

Wait. The problem is that the two queries return inconsistent results.

You illustrate that by using inconsistent data for the two tables in the two queries?

Hi George,

I am demonstrating that when I use a Form Parameter (forms]![frmA]![dtmBirthday]) in the Where Clause on Access Tables everything works as expected but when I migrate the two tables to SQL Server I get a different result. I'll try to make a video.

 

[ions]

Wait. The problem is that the two queries return inconsistent results.

You illustrate that by using inconsistent data for the two tables in the two queries?

Let me know if the video explains the issue clearly. I am very curious why this inconsistent behavior is occurring. The recording volume is very dim for some reason so please max out the volume.

 

[Minty]

Put an Eval() around the form criteria.

 

[ions]

Put an Eval() around the form criteria.

Hi Minty,

Eval () resolved the issue. Thank you. I am curious however, why is the Eval necessary?