Problem with SQL looking at field names in database.

[mansonpj]

Hi All

I am having a problem running a piece of SQL code for a multiselect box that needs to run a query to generate a report, i think i know what the problem is but cannot get passed it and i really need to. I cannot change the field name because it is linked to another db that is for other business use. Here is the code :

Private Sub response_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("responsecodes")
If Me!response.ItemsSelected.Count > 0 Then
For Each varItem In Me!response.ItemsSelected
strCriteria = strCriteria & "MAXIMO_V_WORKORDERS_FA.WORKORDER-RESPONSIBILITY = " & Chr(34) _
& Me!response.ItemData(varItem) & Chr(34) & "OR "
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)

End If
strSQL = "SELECT * FROM MAXIMO_V_WORKORDERS_FA " & _
"WHERE " & strCriteria & ";"
qdf.SQL = strSQL
End Sub

The problem is in the

strCriteria = strCriteria & "MAXIMO_V_WORKORDERS_FA.WORKORDER-RESPONSIBILITY = " & Chr(34) _
& Me!response.ItemData(varItem) & Chr(34) & "OR "

The dash between WORKORDER and RESPONSIBILITY is the problem but that is the field name, when i run the query to hold the data of the multiselect box it hold the data but put's the following statement into the query field name, so i cannot access this in my report query.

[MAXIMO_V_WORKORDERS_FA].[WORKORDER]-[RESPONSIBILITY]

What i need is for the [MAXIMO_V_WORKORDERS_FA] to be the table name and [WORKORDER]-[RESPONSIBILITY] to be [WORKORDER-RESPONSIBILITY] to be the field name then i know it will work.

Any help would be greatly appreciated.

Paul

 

[pbaldy]

Maybe I'm missing something, but since you're building the SQL, you have it in your power to add the brackets as appropriate. I suspect you're also going to run into a space problem.

 

[mansonpj]

Paul

For some reason though the brackets are being put in automatically in the query every time you click another responsibility it put the brackets in the field name splitting the WORKORDERS and RESPONSIBILITY causing problems

 

[pbaldy]

Even if you do this?

strCriteria = strCriteria & "MAXIMO_V_WORKORDERS_FA.[WORKORDER-RESPONSIBILITY] = " & Chr(34) _

 

[mansonpj]

Paul,

Thanks again for the reply but putting brackets in then gives me an type mismatch error, any more ideas would be appreciated.

Paul

 

[mansonpj]

The thing is Paul i have another piece of code directly above it and instead of WORKORDER-RESPONSIBILITY, it say LEADCRAFT and it works a treat.

Paul

 

[pbaldy]

A type mismatch error implies that the field is numeric rather than text, in which case you don't want to surround it with Chr(34).

 

[mansonpj]

The field is definetly text, as when i select a responsibility it goes into the criteria for the query, the problem is the the table field in the query instead of saying [MAXIMO_WORKORDERS_FA] and the field saying [WORKORDER-RESPONSIBILITY] the table name says [MAXIMO_WORKORDERS_FA][WORKORDER]-[RESPONSIBILITY].

Then when i got to run my query for my report i cannot build to find the field that doesn't exist it is just blank because all the info required is in the table name.

Paul

 

[pbaldy]

Maybe I'm just dense today. Can you post a sample db to play with?

 

[mansonpj]

Thanks for the offer Paul, here you go this should help.

Paul

 

[pbaldy]

Perhaps I need a clear path on how to recreate the problem. I made the change I recommended in post 4 above, and the query then runs fine. Looks like this:

SELECT *
FROM MAXIMO_V_WORKORDERS_FA
WHERE MAXIMO_V_WORKORDERS_FA.[WORKORDER-RESPONSIBILITY] = "INTG"OR MAXIMO_V_WORKORDERS_FA.[WORKORDER-RESPONSIBILITY] = "PMAIN";

I thought the lack of a space before the "OR" would cause a problem, but the query runs fine. I'd fix it anyway if it were me.

 

[mansonpj]

I must be an idiot, your right your step 4 works, now i am getting a qdf.SQL = strSQL
error, any ideas. The error that it causes is

"Runtime Error 3145
Syntax error in WHERE clause"

Help

Paul

 

[mansonpj]

Paul,

Just a quick note to say thanks for the help, got everything working this morning, and it's looking good.

Thanks
Again

Paul

 

[pbaldy]

Glad to hear it; we Paul's have to look out for each other.