Help with placement of quotes

[MaggieLeatherman]

HI, I need help with the following code. I need to create my table name on the fly (varAreaTable) and then use it within a query (qdfTest).. But have huge problems with placement of the quotes!! Any help is appreciated.. And if you could point me to any articles talking about using quotes in VBA, that'd be great. Thanks, Maggie

varAreaTable = "Area" & varProjNum
Set qdfTest = .CreateQueryDef("testing", "select * from & '" varAreaTable & "' ")

 

[Remou]

Table names are not quoted in queries. If there are spaces in the name you need braces.

[tt]"select * from " & varAreaTable[/tt]

 

[PHV]

Either this:
Set qdfTest = .CreateQueryDef("testing", "select * from " & varAreaTable)
Or (safer) this:
Set qdfTest = .CreateQueryDef("testing", "select * from [" & varAreaTable & "]")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MaggieLeatherman]

Thanks for your replies... now I need to take the same question a step further...

varAreaTable = "Area" & txtProjNum
varMaterialsTable = "Materials" & txtProjNum

Set qdfTest = .CreateQueryDef("testing", "select varAreaTable.AreaIId, varAreaTable.APSBldgNumb, " _
& "varAreaTable.ConstrUnit, varMaterialsTable.Surveyor " _
& "from " & varAreaTable LEFT JOIN varMaterialsTable on varAreaTable.AreaIID = varMaterialsTable.AreaIID)

 

[Remou]

Make it easier by using an alias, perhaps:
[tt]"select a.AreaIId, a.APSBldgNumb, " _
& "a.ConstrUnit, b.Surveyor " _
& "from " & varAreaTable & " A LEFT JOIN " & varMaterialsTable & " B on A.AreaIID = B.AreaIID[/tt]

 

[PHV]

So, you really have a set of tables for each ProjNum ?
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MaggieLeatherman]

Remou, please stick with me!!!! Here's my syntax but still getting Compile Error, Expected list separator or )

Set qdfTest = .CreateQueryDef("testing", "select a.AreaIId, a.APSBldgNumb, " _
& "a.ConstrUnit, a.RoomNum, b.Matiid " _
& "from " & varAreaTable & " A LEFT JOIN " & varMaterialsTable & " B on A.areaiid = b.areaiid)

Thanks so much!

 

[Remou]

OOps. Try:
Set qdfTest = .CreateQueryDef("testing", "select a.AreaIId, a.APSBldgNumb, " _
& "a.ConstrUnit, a.RoomNum, b.Matiid " _
& "from " & varAreaTable & " A LEFT JOIN " & varMaterialsTable & " B on A.areaiid = b.areaiid[red]"[/red])

PHV makes a very good point indeed.

 

[MaggieLeatherman]

Thanks so much for your help... I really struggle with quotes!!!!! And I'm reading the article that was forwarded to me. Maggie