SQL Query and Parameters

[m stroup]

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.

 

[Klatuu]

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 & """"

 

[m stroup]

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

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

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.

 

[Klatuu]

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

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

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

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.