WHERE clause with Multiple Criteria (1 Viewer)

[Rhino999]

I hope someone can help me. I have spent a lot of time on this and yet I have not been able to find a solution.
I’m trying to execute a VBA SELECT, FROM, with a WHERE Clause.
I first created this in a Query and thought I could just move the Code over to VBA and I’ll be done, NOT!
Firstly, VBA actually required me to place Double Quotes around the entire SELECT Statement. The SQL that the Query generated does not. Additionally, the Query used Double Quotes around the Text values and SQL doesn’t, as far as I can tell. This all took me a bit of time and Frustration to figure out.
Now that I have that figured out, I’m getting a problem with my WHERE Clause.
I’m getting a Run-time error ‘3131’
Syntax error in FROM Clause.
Here is what I’m trying to do.
As a TEST I’m using Numbers and TEXT values before I move on to Fields or Controls with those Values in them.

Here is the Code I have.

Private Sub CheckFor()

Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT tblPODetail.[POID], tblPODetail.[PODetailJobID], tblPODetail.[PODetailMFG], tblPODetail.[PODetailItem], tblPODetail.[PODetailStyleName], tblPODetail.[PODetailQty], tblPODetail.[PODetailStyle#], tblPODetail.[PODetailBK], tblPODetail.[PODetailColor#], tblPODetail.[PODetailC Name], tblPODetail.[PODetailSize], tblPODetail.[PODetailSizeDesc], tblPODetail.[PODetailSqFtBox], tblPODetail.[PODetailItemDesc]" & _
"FROM tblPODetail" & _
"WHERE (((tblPODetail.POID) = 54) And ((tblPODetail.PoDetailJobID) = 173) And ((tblPODetail.PODetailMFG) = 'Mohawk') And ((tblPODetail.PODetailItem) = 'ITem012345') And ((tblPODetail.PODetailQty) = 100) And ((tblPODetail.[PODetailStyle#]) = 'LKPO') And ((tblPODetail.PODetailBK) = '66') And ((tblPODetail.[PODetailColor#]) = '66') And ((tblPODetail.[PODetailC Name]) = 'OrangXX') And ((tblPODetail.PODetailSize) = '7070') And ((tblPODetail.PODetailItemDesc) = 'Carpet Desc'));"

Set rs = CurrentDb.OpenRecordset(strSQL) <----- This is where the Error occurs

Eventually I’d like to replace all of this with loaded Fields as defined in the sub with below
Dim CheckForItem As String
Dim CheckForMFG As String
Dim CheckForPOID As Integer
Dim CheckForJobID As Integer
Dim CheckForStyleName As String
Dim CheckStyle_ As String
Dim CheckForBK As String
Dim CheckForColor_ As String
Dim CheckForC_Name As String
Dim CheckForSize As String
Dim CheckForSizeDesc As String
Dim CheckForSqFtBox As String
Dim CheckForItemDesc As String

I have also tried to simplify that code above and just code simply "WHERE (((tblPODetail.[POID]) = 54));"
and I still can ‘t get it to work!

If someone can tell me what I’ve been doing wrong I would greatly appreciate it.

 

[plog]

You took your SQL from your query and put it in your VBA, that's a good idea. However, once you do that, and because you have to modify it like you did, you also need to do the reverse. You need to take the SQL string that is produced by VBA and put it in a query to see if it works. When you do that, you shouldn't have to clean it up any--just copy and paste and see if it runs.


If you do that you will see that you ever so slightly messed up your clauses:

"...tblPODetail.[PODetailItemDesc]" & _
"FROM tblPODetail" & _
"WHERE (((tblPODetail.POID) = 54) And..."

The above turns into this:

"...tblPODetail.[PODetailItemDesc]FROM tblPODetailWHERE (((tblPODetail.POID) = 54) And..."

You have no spaces in front of your FROM or WHERE keywords.

 

[Rhino999]

plog, thank you very much for helping me and giving me great advice.

I did exactly as you stated and of course, it worked.

Thanks again, you saved me countless hours of frustration!

Regards,

Rhino999