SQL Syntax for Strings in Select Queries

[acoppini]

I need help! I think that my problem is one of syntax using strings. Specifically I have defined a string field called issuername that I am later trying to use as an input into select queries. I have tried different syntaxes because I believe I need to use the & operator in SQL to let it know that it is searching for a string. Despite trying several approaches I have not succeeded. Any ideas? My code is below: Thanks, Adrian

Public Sub RatingCalc()
 
Dim db As Database
Dim bsmatrix As DAO.Recordset
Dim ratinggroup As DAO.Recordset
Dim ratings1 As DAO.Recordset
Dim ratings2 As DAO.Recordset
Dim selldate As String
Dim buydate As String
Dim ratingtypeID As Double
Dim recentrating As Double
Dim previousrating As Double
Dim ratingchange As Double
Dim issuername As String
Dim ratingscount As Double
Set db = CurrentDb
 
'Create recordset of all sell tradedates and corresponding preceding buydates (many-to-many relationship)
 
Dim mySQL As String
mySQL = "SELECT * FROM zBuySellMatrix"
 
Set bsmatrix = db.OpenRecordset(mySQL, dbOpenDynaset)
 
bsmatrix.MoveFirst
Do While Not bsmatrix.EOF
selldate = bsmatrix!selldate
buydate = bsmatrix!buydate
issuername = bsmatrix!Issuer_Name
 
'vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
'Creates ratinggroup recordset for the Issuer
 
Dim mySQL3 As String
 
mySQL3 = "SELECT z_RatingsByIssuer.Rating_RatingType_ID, z_RatingsByIssuer.Rating_RatingType_Name"
mySQL3 = mySQL3 + " FROM z_RatingsByIssuer"
mySQL3 = mySQL3 + " WHERE (((z_RatingsByIssuer.Issuer_Name)= ' & issuername & '))"
mySQL3 = mySQL3 + " GROUP BY z_RatingsByIssuer.Rating_RatingType_ID, z_RatingsByIssuer.Rating_RatingType_Name"
 
Set ratinggroup = db.OpenRecordset(mySQL3, dbOpenDynaset)
 

[QVeen72]

Hi,

A Double Quote to End and Restart the string...

....Issuer_Name)= '" & issuername & "'))"

Regards
Veena

[acoppini]

Hi Veena,

Thanks for your help. I tried your suggestion but got the following error - any ideas? Copy of the new code below:

Run-tim error '3075'

Syntax error (missing operator) in query expression '(((z_RatingsByIssuer.Issuer_Name)='Reader's Digest Association, Inc.'))GROUP BY z_RatingsByIssuer.Rating_RatingType_ID,zRatingsByI ssuer.Rating_Rating_Type_Name'

Dim mySQL3 As String
 
mySQL3 = "SELECT z_RatingsByIssuer.Rating_RatingType_ID, z_RatingsByIssuer.Rating_RatingType_Name"
mySQL3 = mySQL3 + " FROM z_RatingsByIssuer"
mySQL3 = mySQL3 + " WHERE (((z_RatingsByIssuer.Issuer_Name)='" & issuername & "'))"
mySQL3 = mySQL3 + " GROUP BY z_RatingsByIssuer.Rating_RatingType_ID, z_RatingsByIssuer.Rating_RatingType_Name"
 
Set ratinggroup = db.OpenRecordset(mySQL3, dbOpenDynaset)
 

[QVeen72]

Hi,

Ok... Since your String contains Single Quote, you have to write an extra Single quote for each one...

Try this :

issuerName = Replace(issuername,"'","''")
 
mySQL3 = "SELECT z_RatingsByIssuer.Rating_RatingType_ID, z_RatingsByIssuer.Rating_RatingType_Name" 
mySQL3 = mySQL3 + " FROM z_RatingsByIssuer" 
mySQL3 = mySQL3 + " WHERE (((z_RatingsByIssuer.Issuer_Name)='" & issuername & "'))" 
mySQL3 = mySQL3 + " GROUP BY z_RatingsByIssuer.Rating_RatingType_ID, z_RatingsByIssuer.Rating_RatingType_Name" 
 
 

Regards
Veena