Access SQL doesn't like table fieldnames (1 Viewer)

[OTWarrior]

I am building a reporting engine that builds queries dynamically, based on imported xlsx files. The fieldnames can be different each time.

One issue I am having, is if the field names have the following:

"23# txt | fld"

It throws a 3075 error - syntax error.

As these characters are in the table names, how else can I reference them in the sql?

This is the vba I am using to set build the sql string, the field in question is created by the first part (strTable & curQues)

"SELECT [" & strTable & "]." & curQues & _
" FROM [" & QueryArray(x) & "] RIGHT JOIN [" & strTable & "] ON [" & QueryArray(x) & "].[" & qFieldDemo & "] = [" & strTable & "].[" & qFieldDemo & "]" & _
" GROUP BY [" & strTable & "].[" & Demofilter & "], [" & strTable & "].[" & qFieldDemo & "], [" & strTable & "]." & curQues & _
" HAVING ((([" & strTable & "].[" & Demofilter & "])=" & Chr(34) & "Employee" & Chr(34) & ") AND (([" & strTable & "].[" & qFieldDemo & "]) Like " & Chr(34) & cleanedqField & Chr(34) & " " & Chr(38) & " " & Chr(34) & "*" & Chr(34) & "));"

 

[Minty]

The # is a date delimiter in Access so will cause issues in your code unless it is enclosed in [ ] brackets.

Edit - noticed you are escaping it... Hmmm

What does a Debug.Print of your SQL String actually equate to?

 

[CJ_London]

you also can't start a field name with a number.

See this link for words and symbols you shouldn't us in table and field names

https://support.office.com/en-us/ar...a9-8baa-4335-9f57-da237c63eabe#__toc272229039

 

[OTWarrior]

you also can't start a field name with a number.

See this link for words and symbols you shouldn't us in table and field names

Thanks for this, that is a useful link.

Access is strange that it allows for the table field names to have those characters, and you can even build queries with them with the query builder, but as soon as you try to build SQL with those names, it doesn't like it.

I have found an alternative way to approach this problem though. After the spreadsheet is imported and the column names are saved to form labels, I rename each field, so they will always be SQL compatible.

Function changefieldnames()

Dim db As Database
Dim tdf As TableDef
Dim n As Object
Dim x As Integer
x = 1


Set db = CurrentDb
Set tdf = db.TableDefs(tablesource) ' Note tablesource is the name of your table
For Each n In tdf.Fields
    n.Name = "tblField" & x ' change to how you want to name them
    x = x + 1
Next n

Set tdf = Nothing
Set db = Nothing

End Function

 

[static]

As Minty said, the fields need sqare brackets.

And I think your Like is wrong.

"SELECT [TBLX]." & curQues & _
" FROM [QAX] RIGHT JOIN [TBLX] ON [QAX].[FLD] = [TBLX].[FLD]" & _
" GROUP BY [TBLX].[FLTR], [TBLX].[FLD], [TBLX]." & curQues & _
" HAVING [TBLX].[FLTR]='Employee' AND [TBLX].[FLD] Like '" & cleanedqField & Chr(34) & " " & Chr(38) & " '*'"



Like '" & cleanedqField & "*'"

 

[OTWarrior]

And I think your Like is wrong.

"SELECT [TBLX]." & curQues & _
" FROM [QAX] RIGHT JOIN [TBLX] ON [QAX].[FLD] = [TBLX].[FLD]" & _
" GROUP BY [TBLX].[FLTR], [TBLX].[FLD], [TBLX]." & curQues & _
" HAVING [TBLX].[FLTR]='Employee' AND [TBLX].[FLD] Like '" & cleanedqField & Chr(34) & " " & Chr(38) & " '*'"


Like '" & cleanedqField & "*'"

You have missed off the end part in the code above. The like statement is

Like " & Chr(34) & cleanedqField & Chr(34) & " " & Chr(38) & " " & Chr(34) & "*" & Chr(34) & "));"

I have put Chr(34) instead of ", as it is much easier than having to put multiple "".

I have solved this by renaming the original table fieldnames when they are imported, which will prevent this from being an issue, rather than having to trap it. When I tried to build the query in the access query builder, it didn't have [] next to the values, yet it worked.

 

[CJ_London]

All the query builder does is generate the SQL and it adds the square brackets regardless. If you click on the sql button at the left end of the ribbon, or bottom right of the window you will see the sql the builder generates.

 

[OTWarrior]

I did, it wasn't putting them in. That's how I checked it.