It does look like a lot of double quotes, doesn't it?
Well there is a reason and believe, me, it took me about 2 years to grasp
the concept of how to get the right number of qoutes.
As you know, Jet SQL will accept either single or double quotes to delimit a
text value. That is handy, but if you use a construct like:
strSQL = strSQL & "WHERE LastName = '" & Me.txtLastName & "'"
It is fine until you hit a name like O'Reilly. Now you are in troulbe.
Your string would end up as:
WHERE LastName = 'O'Reilly'
And Jet will choke on that.
So, regardless of whether you think you may or may not hit a single quote in
a string, it is always best to use double quotes.
The rule on how to include a double qoute in a string varialbe is to use a
pair of double qoutes "". But, you also have to delimit your literal with
double qoutes. The headache then is how many do I need?
I tried various tricks I found on the subject by using constants that were
multiple qoute marks, using Chr(34), which is ", but I never could get it
right. Then I hit on an idea that made it very simple for me. After a few
times through the exercise, I got to where I don't very often have to go
through this routine any more. Here is what I do.
First, I write the code using single quotes:
strSQL = strSQL & "WHERE LastName = '" & Me.txtLastName & "'"
Then, since you need two "" to make one " in the string, I go back and edit
the line of code replace each occurance of ' with ""
So replacing the first ' becomes:
strSQL = strSQL & "WHERE LastName = """ & Me.txtLastName & "'"
Then replace the next ' becomes:
strSQL = strSQL & "WHERE LastName = """ & Me.txtLastName & """"
So, here -------------------------------------v
strSQL = strSQL & "WHERE LastName = """ & Me.txtLastName & """"
The first two quotes makes one qoute in the string. The third quote
delimits the first part of the string and becomes:
"WHERE LastName = "
Then the value of the control is concatenated to it:
"Where LastName = "O'Reilly
And this ------------------------------------------------------------------v
strSQL = strSQL & "WHERE LastName = """ & Me.txtLastName & """"
Creates a string with one quote. The first " begins the delimiter for the
string. The next two "" become one " in the string, and the fourth ends the
delimiter for the string. and the outcome is:
"
Which is then concatenated to:
"Where LastName = "O'Reilly
And becomes:
"Where LastName = "O'Reilly"
So, now you know the rules on how it works, but like I said, the easy way to
get it down is to write the code using a single '
Then go back and replace each ' with ""
Hope that is helpful.
--
Dave Hargis, Microsoft Access MVP
"m stroup" wrote:
> Thanks so much, Dave. I felt it was a syntax error and played around with
> quotations, but had not landed on the right mix. It looks like there are too
> many but it is working beautifully.
> --
> Teach me to fish! Thanks for the help.
> Pax, M
>
>
> "Klatuu" wrote:
>
> > ACFT appears to be a text data type and therefore need to be enclosed in
> > quotes.
> > This line:
> >
> > sWHERE = sWHERE & " AND s.ACFT = " & Me.cboAircraft
> >
> > Should be:
> >
> > sWHERE = sWHERE & " AND s.ACFT = """ & Me.cboAircraft & """"
> >
> > --
> > Dave Hargis, Microsoft Access MVP
> >
> >
> > "m stroup" wrote:
> >
> > > I have the following:
> > >
> > > frmQryEvents
> > > chkDate txtStartDate "to" txtEndDate
> > > chkAircraft cboAircraft
> > > etc
> > > tblEvents
> > > EventDate
> > > ACFT
> > > etc..
> > > I am using the following to build my SQL string:
> > >
> > > Function BuildSQLString(sSQL As String) As Boolean
> > >
> > > Dim sSELECT As String
> > > Dim sFROM As String
> > > Dim sWHERE As String
> > >
> > > sSELECT = "s.* "
> > >
> > > sFROM = "tblEvents s "
> > > If chkDate.Value = -1 Then
> > > If Not IsNull(txtBeginDate) Then
> > > sWHERE = sWHERE & " AND s.EventDate >= " & _
> > > "#" & Format(txtBeginDate, "mm/dd/yyyy") & "#"
> > > End If
> > > If Not IsNull(txtEndDate) Then
> > > sWHERE = sWHERE & " AND s.EventDate <= " & _
> > > "#" & Format(txtEndDate, "mm/dd/yyyy") & "#"
> > > End If
> > > End If
> > >
> > > If chkAircraft.Value = -1 Then
> > > sWHERE = sWHERE & " AND s.ACFT = " & Me.cboAircraft
> > > End If
> > >
> > > If chkPilot.Value = -1 Then
> > > sWHERE = sWHERE & " AND s.Pilot = " & Me.cboPilot
> > >
> > > End If
> > >
> > > sSQL = "SELECT " & sSELECT
> > > sSQL = sSQL & "FROM " & sFROM
> > > If sWHERE <> "" Then sSQL = sSQL & "WHERE " & Mid(sWHERE, 6)
> > >
> > > BuildSQLString = True
> > >
> > > msgbox sSQL
> > >
> > > End Function
> > >
> > > The sSQL goes into a querydef function, which is working fine.
> > > The sSQL reads:
> > >
> > > SELECT s.* FROM tblEvents s WHERE s.EventDate >= #03/01/2008# AND _
> > > s.EventDate <= #03/31/2008# AND s.ACFT = BF1.
> > >
> > > This appears to be right. I entered the dates as 03/01/2008 and 3/31/2008
> > > and I selected BF1 from cboAircraft (ACFT is the field name in the table).
> > >
> > > What occurs is that instead of "BF1" showing in the design view as criteria,
> > > [BF1] appears and I am prompted for the BF1 entry (as in a parameter query).
> > >
> > > Any suggestions would be helpful. Running Access 2003.
> > >
> > > --
> > >
> > > Teach me to fish! Thanks for the help.
> > > Pax, M
|
Similar Threads
