SQL Statement syntax error

[Binnit]

I have the following SELECT sql statement which is part of a Make table statement. This part is concatenating 2 fields with a "\" and creating the new field NewPolicy.

The syntax creates a type mismatch error, I have tried various combinations to resolve this but I cannot get it right, if I build this using a standard query the SQL syntax below works without a hitch.

strSQL = "SELECT tblPortfolioDetails.PolPolicyno, [PolPolicyNo] & " \ " & [folioref] AS NewPolicy, etc etc

Any suggestions most welcomed.


Happiness is...not getting what you want but wanting what you have already got

 

[ZmrAbdulla]

strSQL = "SELECT tblPortfolioDetails.PolPolicyno, [PolPolicyNo] & chr(47) &  [folioref] AS NewPolicy, etc etc

________________________________________________________
Zameer Abdulla
Help to find Missing people
Sharp acids corrode their own containers.

 

[Remou]

Or perhaps:
[tt]strSQL = "SELECT tblPortfolioDetails.PolPolicyno, [PolPolicyNo] & '\' & [folioref] AS NewPolicy, etc etc"[/tt]

 

[Binnit]

Thanks zmrAbdulla / Remou both worked although it was not only this bit that was wrong with my complete SQL statement.

There was another part that was causing errors too, but having got your suggestions, both bits are now working correctly. I do however like zmrAbdulla's use of the chr set, something that had not crossed my mind.

Thanks to both



Happiness is...not getting what you want but wanting what you have already got

 

[JoeAtWork]

Personally, I would suggest avoiding using characters like forward and backslash for field names. They cause unnecessary headaches, as you have experienced.

 

[OhioSteve]

Yeah stay with a-z, 0-9, and the underscore character. Your life will be MUCH less stressful.

 

[Binnit]

Joe / Ohio

Just so you know, this was a concatenation of 2 bits of data from 2 fields in to one new field (NewPolicy) for a make table SQL statement - not the creation of a conjoined fieldname.

Comments appreciated & understood all the same.

Happiness is...not getting what you want but wanting what you have already got

 

[JoeAtWork]

Oh, sorry, I misread your SQL statement.