Punctuation Aggravation

[barboza]

It took forever to get the following line to work in VBA:

Set rst = dbs.OpenRecordset("Select [LocationName] from PWS where [LocationNumber] = '" & Me.LocationNumber & "';")


Now I am trying to get the following line of code to run with no success:
Set rst = dbs.OpenRecordset("Select [LocationName] from PWS where [LocationNumber] = '" & Me.LocationNumber And Me.PWSName = PWS.PWSName & "';")

I am having the hardest time figuring out quotes, semicolons etc.. using vba.
No problem just writing the sql statement in sql designer, and running the saved query. Punctuating the sql statement so it will run in VBA has been frustrating to put it mildly. Can anyone write this line of code so it will run in vba.

Can someone point me to a clear and understandable explanation of how punctuation works in vba??

Thanks,

B

 

[The_Doc_Man]

Set rst = dbs.OpenRecordset("Select [LocationName] from PWS where [LocationNumber] = '" & Me.LocationNumber And Me.PWSName = PWS.PWSName & "';")

I'm gonna guess at your intent...

Set rst = dbs.OpenRecordset("Select [LocationName] from PWS

where [LocationNumber] = " & CStr( Me.LocationNumber ) &

" And Me.PWSName = '" & PWS.PWSName & "';")

I have to guess at your intent. Your issues are:

1. That had better be a unique SELECT

2. You have the number to convert - but you DON'T need the apostrophes that you had because (I'm presuming) that [LocationNumber] is a number. So here is the syntax you want to result.

SELECT [LocationName] FROM PWS WHERE [LocationNumber] = a numeric string AND Me.PWSName = 'a text value';

Where you have the two substitutions (as noted by the Italics), you need the ampersands to concatenate the values that you re going to substitute. The number doesn't need quotes because of the rules about numeric comparisons. You don't need quotes around numbers. But you DO need to covert the numeric field into a numeric string. Hence CStr.

The text value needs to be quoted because of the rules about text string comparisons. You need the quotes - but the substitution cannot be inside the quoted part. So you have to break the string and drop that concatenation in the middle.

 

[barboza]

Punctuation Problems

Location number is actually a text field - there are some characters in a few of the location numbers.

If you look at the first line of code - it works.

In the second line I am trying to select based on two criteria:
the location number AND the location name - both text.

Thanks,
B

 

[WayneRyan]

Hi Barboza,

You're trying to build a string (the SQL statement)

And you're passing it (with embedded VBA variables) so that IT can make a string.

That's not easy.

Visualize what you want at the end:

Select [LocationName]
From   PWS 
Where [LocationNumber] = 'Loc#' And  <-- 'Loc#' is your Me.[LocationNumber] string
      [PWSName] = 'SomePwsName';"    <-- 'SomePwsName' is your Me.'PwsName' string

How do you easily put the stuff between the single-quotes?

This suffices:

Set rst = dbs.OpenRecordset("Select [LocationName] " & _
                            "From PWS " & _
                            "Where [LocationNumber] = '" & Me.LocationNumber & "' And " & _
                            "      [PWSName] = '" & PWS.PWSName & "';")

But this gives you a way to troubleshoot it:

Dim SQL As String    <-- Use a string variable to track your work

SQL = "Select [LocationName] " & vbCrLf & _   <-- Add a CrLf to make code look like model
      "From PWS " & vbCrLf & _                <-- Add another CrLf
      "Where [LocationNumber] = '[B]" & Me.LocationNumber & "[/B]' And " & vbCrLf & _  <-- Add your local variables
      "      [PWSName] = '[B]" & PWS.PWSName & "[/B]';")                               <-- into the SQL string
Set rst = dbs.OpenRecordset(SQL)
Debug.Print SQL                               <-- Lets you see how close you came to your target SQL

hth
Wayne

 

[barboza]

Simplest Solution

Set rst = dbs.OpenRecordset("SELECT LocationName FROM PWS WHERE LocationNumber='" & Me.LocationNumber & "' AND PWSName='" & Me.PWSName & "';")


Thanks to all for your help.
The code above works, and is easiest for me to grasp.

Thanks again to all!!

 

[WayneRyan]

Glad it works B.

The most important thing is to put the SQL into a string so that you can see
how it compares to what you want.

See ya,
Wayne