SQL Query Help

Acropolis

Registered User.
Local time
Today, 23:42
Joined
Feb 18, 2013
Messages
182
Hi,

I have the following query which runs fine when I run it through SqlYog and returns what I am looking for.

Code:
Set rs2 = db.OpenRecordset("SELECT * FROM inboxvalues_v2 INNER JOIN (SELECT MAX(InBoxRecNo)InBoxRecNo FROM inboxvalues_v2 i  WHERE InboxOboxRecno = 645 AND InboxDateTime < '2014-04-07' GROUP BY InBoxOBoxRecNo)tempTbl ON inboxvalues_v2.InBoxRecNo = tempTbl.InBoxRecNo")

When I run it through Access VBA though, I get a 3075 RunTime Error Syntax error (missing operator) in query expression. 'MAX(InBoxRecNo)InBoxRecNo'.

The table it is looking up is in a MySQL backend, and both SqlYog and Access and connecting to the same table.

Any ideas?
 
Insert a AS keyword between the Closing parenthesis of MAX and InBoxRecNo. Like..
Code:
SELECT Max(InBoxRecNo[B][COLOR=Blue])[/COLOR] [COLOR=Red]As[/COLOR] [COLOR=Blue]I[/COLOR][/B]nBoxRecNo FROM
 
Thanks, that solved that problem, but now have a different problem.

When I run it with that change in place, I then get a Circular reference caused by alias 'InboxRecNo' in query definitions SELECT list.

I chanded it to InboxRecNo1 and at the end of the query as well as below and then get

Data type mismatch in criteria expression :banghead:

Code:
Set rs2 = db.OpenRecordset("SELECT * FROM inboxvalues_v2 INNER JOIN (SELECT MAX(InBoxRecNo) AS InBoxRecNo1 FROM inboxvalues_v2 i  WHERE InboxOboxRecno = 645 AND InboxDateTime < '2014-04-07' GROUP BY InBoxOBoxRecNo)tempTbl ON inboxvalues_v2.InBoxRecNo = tempTbl.InBoxRecNo1")
 
How about this Query?
Code:
Set rs2 = db.OpenRecordset("SELECT * FROM (SELECT MAX(InBoxRecNo) AS MaxInBoxRecNo FROM inboxvalues_v2 WHERE InboxOboxRecno = 645 " & _
                           "AND InboxDateTime < '2014-04-07') As tempTbl INNER JOIN inboxvalues_v2 ON inboxvalues_v2.InBoxRecNo = tempTbl.MaxInBoxRecNo")
 
Works fine in SqlYog, but when run in Access comes up with the error Data type mismatch in criteria expression still.
 
Is InBoxDateTime actually a Date/Time Field? If so rewrite your query as,
Code:
Set rs2 = db.OpenRecordset("SELECT * FROM (SELECT MAX(InBoxRecNo) AS MaxInBoxRecNo FROM inboxvalues_v2 WHERE InboxOboxRecno = 645 " & _
                           "AND InboxDateTime < " & Format(Date, "\#mm\/dd\/yyyy\#") & ") As tempTbl " & _
                           "INNER JOIN inboxvalues_v2 ON inboxvalues_v2.InBoxRecNo = tempTbl.MaxInBoxRecNo")
 
Brilliant thanks working nicely now thank you.

Just one last thing, I have changed the OboxRecNo to a variable set from outside the query and that is working fine, but how can I do the same with the date, as it wont always be Date?

thanks
 
You can just replace the word Date with the actual variable name. As simple as that.
 
Brilliant thanks for you help, got it all sorted and working now, well that part anyway, now for the rest of it lol

Thanks again
 

Users who are viewing this thread

Back
Top Bottom