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

ions

Access User
Local time
Today, 15:27
Joined
May 23, 2004
Messages
816
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
1700159996290.png


Local Tables Correct Answer
1700159856947.png
 

Attachments

Last edited:
Birthday / BirthDay seems to be inconsistent between the two tables / queries, not sure to what effect it has, if any.
 
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.
 
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?
 
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.
 
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. :)
 
Put an Eval() around the form criteria.
 

Users who are viewing this thread

Back
Top Bottom