Access VBA Run-time Error 3075

Question

Before I get started, I know there are a TON of Error 3075 questions on here, and I've had a good look through them to find one that can help me, but I have been unable to.

I have an Access database written in VBA. I am attempting to execute an SQL statement which is simply querying the database. I am getting the classic 3075 error, which suggests that my SQL query is invalid. I have printed the query to a message box and to me it looks fine, but I'm using Inner Joins and I'm not too familiar with them.

Could someone please check over my code to give me a hand?

My SQL related VBA code is below:

MsgBox strSQL

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount > 0 Then
    rs.MoveLast
    lblTotal.Caption = rs.RecordCount
Else
    lblTotal.Caption = "0"
End If

Pretty standard stuff. I haven't included how I make up strSQL because it is based on a complex set of if statements. The value of strSQL when I try to set the Recordset is:

My suspicion is that there is something wrong with the way I am referencing the dates. Just to give you the whole picture, below are the three tables referenced:

tblHistory:

tblBooks: Continued:

tblBookTypes:

I've tried isolating the problem by cutting the SQL statement right down. I could get it to work with a simple statement like SELECT * FROM tblHistory, as well as adding a WHERE parameter to that, but once I introduced the Inner Join, I got the 3075 error again.

Does anyone know where I'm going wrong? This is driving me mad!

Make that WHERE clause the last piece of your query statement. I suspect you might have better luck building a similar query from scratch using Design View of the Access query designer. When it works, switch to SQL View to see the valid SELECT statement and use that as the model when you revise your VBA code. — HansUp
You're a bloody lifesaver, it was all a matter of putting the WHERE statement right at the end after the Inner Join. If you want to put that as an answer, I'll accept it. Thanks so much! — Matt Kelly

HansUp HansUp · Accepted Answer · 2016-04-13T15:20:47

Access complained about the query because the WHERE clause is in the wrong position.

Instead of this pattern, SELECT ... FROM ... WHERE ... INNER JOIN, you need a pattern like SELECT ... FROM ... INNER JOIN ... WHERE

In other words, move that WHERE clause to the end of the query statement.

Access VBA Run-time Error 3075

1 Answers