Moderate Question: Using "s within a string in VBA

[Bill Bisco]

Alright, I want to use the QueryDefs property to change the SQL of one of my queries to a different SQL. For Example:

Dim dbsCurrent As Database
Dim qryName As QueryDef
Dim strSQL As String

Set dbsCurrent = CurrentDb
Set qryName = dbsCurrent.QueryDefs("qryProcessSelectLSSubform")

strSQL = "SELECT tblProcesses.[Process Name], tblProcesses.ProcessSEQ, tblProcesses.StationID, tblStations.[Station Name], tblStations.Side, tblStations.StationSEQ, tblZones.[Zone Name], tblZones.ZoneSEQ, tblProcesses.Time, tblProcesses.ProcessType, tblProcesses.ProcessSubType, tblZones.LineSpeed, tblStations.StationID, [Station Name] & " " & [Side] AS Station, Sum(tblElements.ElementTime) AS SumOfElementTime"

Here's the Problem: Within my code I have used " ". [Station Name] & " " & [Side] AS Station

As a result I can't get VBA to work cause VBA wants " " to mean a beginning or end to a String But SQL wants " " to mean an additional word that I'm adding.

Is there any way to get VBA to process this SQL code without overly changing my database?

Sincerely,
Bill

 

[Joe4]

You can get around it by doubling, tripling, or quadrupling up the quotes marks (can't remember which), but quite frankly I find that very confusing. If you want to denote literal quotes marks, you can do so by using the ASCII code number for quote marks, which is 34.

So in VBA, if you wanted to place quotes marks around an entry that is coming off a Text field on a Form, it would look something like this:

... = Chr(34) & [MyForm]![MyTextField] & Chr(34) ...

Just remember that the Chr(34) must be located outside of the quotes, or else it will be treated as literal text like everything found between quotes.

 

[Bill Bisco]

Joe4,

Thank you sir. I will be sure to use this.

Many thanks,
Bill