Concatenating fields in SQL statement

[BusMgr]

I use a Select SQL statment to get the details to build a table in a word document that I output from a form. The SQl statement is based on a query. I need to concatenate 6 fields together and add hyphens between the fields. I have it all except the punctuation working.

My code as follows:

'BIT TABLE

'Get details from DB and create a table in the document
'Build SQL string for bit details

strSQL1 = "SELECT [Bit].[Bit No] as [Bit No], [Bit].[Bit Size] as [Size]," & _
"[Bit].[Bit Mfgr] as [Mfg], [Bit].[Bit Type] as [Type], [Bit].[Bit Serial No]" & _
"as [Ser No], Nozzle1 & Nozzle2 & Nozzle3 & Nozzle4 & Nozzle5 & Nozzle6 as [Nozzles]," & _
"[Bit].[DepthIn] as [In], [DepthOut] as [Out], [CumHrs] as Hours," & _
"[CumFtg] as Footage, Round(([CumFtg]/[CumHrs]),1) " & _
"as ROP from [qryDailyBitRuns] WHERE [Bit].[DayID] =" & [Forms]![frmDay]![Status].[Form]![DayID]


'Get details from database and create a table
'in the document
Set rst1 = dbs.OpenRecordset(strSQL1, dbOpenSnapshot)
colCount = 11
With CreateTableFromRecordset(objWord.ActiveDocument.Bookmarks("Details&quot.Range, rst1, colCount, True)

I am concatenating Nozzle1 & Nozzle2 & ... as Nozzle, but I want a hyphen between each Nozzle, so instead of 121212000, I get 12-12-12-0-0-0.

I've tried Nozzle1 & "-" & Nozzle2 & ...

Any help is appreciated.

Thanks in advance

BusMgr

 

[Frink]

Hallo,

This is the old quotes within quotes problem.

To get a single set of double quotes in a string you need to specify them twice.
ie. instead of

Nozzle1 & "-" & Nozzle2

try

Nozzle1 & ""-"" & Nozzle2

It all gets very confusing after a while, so an easier way is to use single quotes for quotes in SQL strings, double quotes for quotes in VBA strings:

Nozzle1 & '-' & Nozzle2

should do the trick

- Frink

 

[BusMgr]

Frink

Is there an issue with using the 'hyphen'? It appears to work for any other character but the hyphen.

Thanks

Karl aka BusMgr

 

[Frink]

Hallo,

Seems like madness. A query works ok for me a standard access table with - or anything else. You could try

Nozzle1 & chr$(45) & Nozzle2

Which should give you a -, although I'd be surprised if that was any different.

Have you tried stopping the code and looking at strSQL1 in the immediate window? Maybe even paste it into a query, that's what I do to check SQL if it seems ok, but doesn't work.

The only other thing is where are your tables accessed by qryDailyBitRuns? If they're a different format or something maybe that doesn't like the -.

- Frink

 

[BusMgr]

Frink
Thanks for your help. I need to look at the whole thing. Yesterday when I delivered it, we tried running the code, and there was a serial number that had the '-' in it and it screwed up the formatting of the table. So it's not just adding a '-' to the code as I initially thought. I need to dig deeper through the code to see what's causing it.

Thanks
Karl aka BusMgr

 

[JoyInOK]

If this is in VBA code, you need double quotes for VBA, and single quotes for the query in Access. It will look like this:
Nozzle1 & "'-'" & Nozzle2 & "'-'" Nozzle3
Using only single quotes,
Nozzle1 & '-' & Nozzle2
VBA reads everything on the line past the first single quote as a comment.