By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,037 Members | 1,839 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,037 IT Pros & Developers. It's quick & easy.

Building Query on the fly...stuck!

P: n/a
Hi All,
I have used the following article to help me build a query 'on the
fly':
http://www.fontstuff.com/access/acctut17.htm

It's a very useful article and is exactly what I was looking for to use
in my current project.
To give you a brief rundown I am basically creating a Training Database
which would allow users to choose 1 or more options from a drop down
box to select the criteria I need.

The following is my code:

Option Compare Database
Option Explicit

Private Sub Command2_Click()
DoCmd.Close
End Sub

Private Sub Command5_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strDept As String
Dim strSurname As String
Dim strForename As String
Dim strTeam As String
Dim strAttending As String
Dim strTraining As String
Set db = CurrentDb
Set qdf = db.QueryDefs("TestQuery")

If IsNull(Me.ComboDept.Value) Then
strDept = " Like '*' "
Else
strDept = "='" & Me.ComboDept.Value & "' "
End If

If IsNull(Me.ComboSurname.Value) Then
strSurname = " Like '*' "
Else
strSurname = "='" & Me.ComboSurname.Value & "' "
End If

If IsNull(Me.ComboForename.Value) Then
strForename = " Like '*' "
Else
strForename = "='" & Me.ComboForename.Value & "' "
End If

If IsNull(Me.ComboTeam.Value) Then
strTeam = " Like '*' "
Else
strTeam = "='" & Me.ComboTeam.Value & "' "
End If

If IsNull(Me.ComboAttending.Value) Then
strAttending = " Like '*' "
Else
strAttending = "='" & Me.ComboAttending.Value & "' "
End If

If IsNull(Me.ComboTraining.Value) Then
strTraining = " Like '*' "
Else
strTraining = "='" & Me.ComboTraining.Value & "' "
End If

strSQL = "SELECT StaffTeamQuery.* " & _
"FROM StaffTeamQuery " & _
"WHERE StaffTeamQuery.Dept='" & strDept & _
"AND StaffTeamQuery.Surname='" & strSurname & _
"AND StaffTeamQuery.Forename='" & strForename & _
"AND StaffTeamQuery.Team='" & strTeam & _
"AND StaffTeamQuery.Attending='" & strAttending & _
"AND StaffTeamQuery.Training='" & strTraining & _
"ORDER BY StaffTeamQuery.Surname,
StaffTeamQuery.Forename;"
qdf.SQL = strSQL
DoCmd.OpenQuery "TestQuery"
DoCmd.Close acForm, Me.Name
Set qdf = Nothing
Set db = Nothing

End Sub

I am getting a runtime error in the following section of code:

strSQL = "SELECT StaffTeamQuery.* " & _
"FROM StaffTeamQuery " & _
"WHERE StaffTeamQuery.Dept='" & strDept & _
"AND StaffTeamQuery.Surname='" & strSurname & _
"AND StaffTeamQuery.Forename='" & strForename & _
"AND StaffTeamQuery.Team='" & strTeam & _
"AND StaffTeamQuery.Attending='" & strAttending & _
"AND StaffTeamQuery.Training='" & strTraining & _
"ORDER BY StaffTeamQuery.Surname,
StaffTeamQuery.Forename;"
qdf.SQL = strSQL

with VB highlighting the qdf.SQL = strSQL code.
Does anyone have any ideas why this may be?
Its so close to finishing...I'm ripping my hair out!
Many Thanks!

Rob.

Jun 7 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
vi*********@googlemail.com wrote:
Hi All,
I have used the following article to help me build a query 'on the
fly':
http://www.fontstuff.com/access/acctut17.htm

It's a very useful article and is exactly what I was looking for to use
in my current project.
To give you a brief rundown I am basically creating a Training Database
which would allow users to choose 1 or more options from a drop down
box to select the criteria I need.

The following is my code:

Option Compare Database
Option Explicit

Private Sub Command2_Click()
DoCmd.Close
End Sub

Private Sub Command5_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strDept As String
Dim strSurname As String
Dim strForename As String
Dim strTeam As String
Dim strAttending As String
Dim strTraining As String
Set db = CurrentDb
Set qdf = db.QueryDefs("TestQuery")

If IsNull(Me.ComboDept.Value) Then
strDept = " Like '*' " Why do you need this filter if Null? Else
strDept = "='" & Me.ComboDept.Value & "' "
End If

If IsNull(Me.ComboSurname.Value) Then
strSurname = " Like '*' " Why do you need this filter if surname is null? Else
strSurname = "='" & Me.ComboSurname.Value & "' "
End If

If IsNull(Me.ComboForename.Value) Then
strForename = " Like '*' " Why do you need this filter if Forename is null? Else
strForename = "='" & Me.ComboForename.Value & "' "
End If

If IsNull(Me.ComboTeam.Value) Then
strTeam = " Like '*' " Who do you need this if the Team is Null? Else
strTeam = "='" & Me.ComboTeam.Value & "' "
End If

If IsNull(Me.ComboAttending.Value) Then
strAttending = " Like '*' " Who do you need this if Attending is Null? Else
strAttending = "='" & Me.ComboAttending.Value & "' "
End If

If IsNull(Me.ComboTraining.Value) Then
strTraining = " Like '*' "
Else
strTraining = "='" & Me.ComboTraining.Value & "' " Who do you need this if the Training Is Null? End If

strSQL = "SELECT StaffTeamQuery.* " & _
"FROM StaffTeamQuery " & _
"WHERE StaffTeamQuery.Dept='" & strDept & _
"AND StaffTeamQuery.Surname='" & strSurname & _
"AND StaffTeamQuery.Forename='" & strForename & _
"AND StaffTeamQuery.Team='" & strTeam & _
"AND StaffTeamQuery.Attending='" & strAttending & _
"AND StaffTeamQuery.Training='" & strTraining & _
"ORDER BY StaffTeamQuery.Surname,
StaffTeamQuery.Forename;"
Why do you have equal = signs in the where clause since the variables
have equal signs?
qdf.SQL = strSQL
DoCmd.OpenQuery "TestQuery"
DoCmd.Close acForm, Me.Name
Set qdf = Nothing
Set db = Nothing

End Sub

I am getting a runtime error in the following section of code:

strSQL = "SELECT StaffTeamQuery.* " & _
"FROM StaffTeamQuery " & _
"WHERE StaffTeamQuery.Dept='" & strDept & _
"AND StaffTeamQuery.Surname='" & strSurname & _
"AND StaffTeamQuery.Forename='" & strForename & _
"AND StaffTeamQuery.Team='" & strTeam & _
"AND StaffTeamQuery.Attending='" & strAttending & _
"AND StaffTeamQuery.Training='" & strTraining & _
"ORDER BY StaffTeamQuery.Surname,
StaffTeamQuery.Forename;"
qdf.SQL = strSQL

with VB highlighting the qdf.SQL = strSQL code.
Does anyone have any ideas why this may be?
Its so close to finishing...I'm ripping my hair out!
Many Thanks!

Rob.

Try something like this....

Dim strFilter As String
If Not IsNull(Me.ComboDept.Value) Then
strFilter = "Dept ='" & Me.ComboDept.Value & "' And "
End If
If Not IsNull(Me.ComboSurname.Value) Then
strFilter = strFilter & "Surname = '" & _
Me.ComboSurname.Value & "' And "
End If
....complete rest of your filters checks

'remove the word "And" if there is something to filter
If strFilter > "" Then strFilter = Left(strFilter,len(strFilter)-5)

strSQL = "SELECT StaffTeamQuery.* " & _
"FROM StaffTeamQuery " & _
IIF(strFilter > "","WHERE " & strFilter,"") & " " & _
"Order By "...
Jun 7 '06 #2

P: n/a
Many thanks for your prompt reply.
I have tried the code you suggested and it just returns a blank query.
I have used the following code to see if it would work:

Dim strFilter As String
Set db = CurrentDb
Set qdf = db.QueryDefs("TestQuery")

If Not IsNull(Me.ComboDept.Value) Then
strFilter = "Dept ='" & Me.ComboDept.Value
End If

If strFilter > "" Then strFilter = Left(strFilter,
Len(strFilter) - 5)
strSQL = "SELECT StaffTeamQuery.* " & _
"FROM StaffTeamQuery " & _
IIf(strFilter > "", "WHERE " & strFilter, "") & " "
DoCmd.OpenQuery "TestQuery"
DoCmd.Close acForm, Me.Name
Set qdf = Nothing
Set db = Nothing

End Sub

I'm afraid I'm fairly new to access and still on the learning curve so
the more simply explained, the better I'll get it!
Thanks again!
salad wrote:
vi*********@googlemail.com wrote:
Hi All,
I have used the following article to help me build a query 'on the
fly':
http://www.fontstuff.com/access/acctut17.htm

It's a very useful article and is exactly what I was looking for to use
in my current project.
To give you a brief rundown I am basically creating a Training Database
which would allow users to choose 1 or more options from a drop down
box to select the criteria I need.

The following is my code:

Option Compare Database
Option Explicit

Private Sub Command2_Click()
DoCmd.Close
End Sub

Private Sub Command5_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strDept As String
Dim strSurname As String
Dim strForename As String
Dim strTeam As String
Dim strAttending As String
Dim strTraining As String
Set db = CurrentDb
Set qdf = db.QueryDefs("TestQuery")

If IsNull(Me.ComboDept.Value) Then
strDept = " Like '*' "

Why do you need this filter if Null?
Else
strDept = "='" & Me.ComboDept.Value & "' "
End If

If IsNull(Me.ComboSurname.Value) Then
strSurname = " Like '*' "

Why do you need this filter if surname is null?
Else
strSurname = "='" & Me.ComboSurname.Value & "' "
End If

If IsNull(Me.ComboForename.Value) Then
strForename = " Like '*' "

Why do you need this filter if Forename is null?
Else
strForename = "='" & Me.ComboForename.Value & "' "
End If

If IsNull(Me.ComboTeam.Value) Then
strTeam = " Like '*' "

Who do you need this if the Team is Null?
Else
strTeam = "='" & Me.ComboTeam.Value & "' "
End If

If IsNull(Me.ComboAttending.Value) Then
strAttending = " Like '*' "

Who do you need this if Attending is Null?
Else
strAttending = "='" & Me.ComboAttending.Value & "' "
End If

If IsNull(Me.ComboTraining.Value) Then
strTraining = " Like '*' "
Else
strTraining = "='" & Me.ComboTraining.Value & "' "

Who do you need this if the Training Is Null?
End If

strSQL = "SELECT StaffTeamQuery.* " & _
"FROM StaffTeamQuery " & _
"WHERE StaffTeamQuery.Dept='" & strDept & _
"AND StaffTeamQuery.Surname='" & strSurname & _
"AND StaffTeamQuery.Forename='" & strForename & _
"AND StaffTeamQuery.Team='" & strTeam & _
"AND StaffTeamQuery.Attending='" & strAttending & _
"AND StaffTeamQuery.Training='" & strTraining & _
"ORDER BY StaffTeamQuery.Surname,
StaffTeamQuery.Forename;"


Why do you have equal = signs in the where clause since the variables
have equal signs?
qdf.SQL = strSQL
DoCmd.OpenQuery "TestQuery"
DoCmd.Close acForm, Me.Name
Set qdf = Nothing
Set db = Nothing

End Sub

I am getting a runtime error in the following section of code:

strSQL = "SELECT StaffTeamQuery.* " & _
"FROM StaffTeamQuery " & _
"WHERE StaffTeamQuery.Dept='" & strDept & _
"AND StaffTeamQuery.Surname='" & strSurname & _
"AND StaffTeamQuery.Forename='" & strForename & _
"AND StaffTeamQuery.Team='" & strTeam & _
"AND StaffTeamQuery.Attending='" & strAttending & _
"AND StaffTeamQuery.Training='" & strTraining & _
"ORDER BY StaffTeamQuery.Surname,
StaffTeamQuery.Forename;"
qdf.SQL = strSQL

with VB highlighting the qdf.SQL = strSQL code.
Does anyone have any ideas why this may be?
Its so close to finishing...I'm ripping my hair out!
Many Thanks!

Rob.

Try something like this....

Dim strFilter As String
If Not IsNull(Me.ComboDept.Value) Then
strFilter = "Dept ='" & Me.ComboDept.Value & "' And "
End If
If Not IsNull(Me.ComboSurname.Value) Then
strFilter = strFilter & "Surname = '" & _
Me.ComboSurname.Value & "' And "
End If
...complete rest of your filters checks

'remove the word "And" if there is something to filter
If strFilter > "" Then strFilter = Left(strFilter,len(strFilter)-5)

strSQL = "SELECT StaffTeamQuery.* " & _
"FROM StaffTeamQuery " & _
IIF(strFilter > "","WHERE " & strFilter,"") & " " & _
"Order By "...


Jun 7 '06 #3

P: n/a
vi*********@googlemail.com wrote:
Many thanks for your prompt reply.
I have tried the code you suggested and it just returns a blank query.
I have used the following code to see if it would work:

Dim strFilter As String
Set db = CurrentDb
Set qdf = db.QueryDefs("TestQuery")

If Not IsNull(Me.ComboDept.Value) Then
strFilter = "Dept ='" & Me.ComboDept.Value
End If

If strFilter > "" Then strFilter = Left(strFilter,
Len(strFilter) - 5)
strSQL = "SELECT StaffTeamQuery.* " & _
"FROM StaffTeamQuery " & _
IIf(strFilter > "", "WHERE " & strFilter, "") & " "
DoCmd.OpenQuery "TestQuery"
DoCmd.Close acForm, Me.Name
Set qdf = Nothing
Set db = Nothing

End Sub

I'm afraid I'm fairly new to access and still on the learning curve so
the more simply explained, the better I'll get it!
Thanks again!


If I don't get a result I oftentimes will use Debug to help me out. Ex:
strSQL = ...
Debug.Print strSQL

Then after I run the offending code, I open a code module window, go to
the Debug window, copy the result to the clipboard, create a new query
in design mode, don't add any tables, click View/SQL from the menu and
paste and run the SQL statement.

For example
strFilter = "Dept ='" & Me.ComboDept.Value
might return an error since you don't enclose it with a quote.
strFilter = "Dept ='" & Me.ComboDept.Value & "'"
might work better.

What does the following line do?
DoCmd.OpenQuery "TestQuery"
All I see is that you are opening a query. Maybe it returns no records.
You aren't opening the query with the new SQL statement.

Now...if you had a qdf set to TestQuery and you updated the SQL for the
qdf with your new SQL and closed it, you'd get it to work most likely. Ex:

Sub qit()
Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs("TestQuery")

'display the current SQL string for your benefit
MsgBox "SQL Is " & qdf.SQL

'change the SQL string
qdf.SQL = "Select * From Employees"
Set qdf = Nothing

'for grins, display the new SQL string
Set qdf = CurrentDb.QueryDefs("TestQuery")
MsgBox "SQL Is " & qdf.SQL
Set qdf = Nothing

'open the query with the new SQL
DoCmd.OpenQuery "TestQuery"

End Sub

Oftentimes tho people use the results with
Dim strSQL As String
Dim rst As Recordset
strSQL = "Select * From Employees"
Set rst = Currentdb.Openrecordset(strSQL,dbopendynaset)
If rst.RecordCount > 0 then
....and process data in the returned recordset. It does not display a
datasheet. You are working with the data returned from the SQL
statement and can manipulate with code.
OR
you open a form or report filtered to strSQL. Ex:
strSQL = "EmployeeID = " & Me.ComboEmp
Docmd.Openform "Test",,,strSQL
You can see that strSQL doesn't contain the Select,From,Order by clause
or the word Where. It's the Where statement with out the word Where.
ANd this filters the form or if called, the report, to whatever you
selected.

Hope this helps.
Jun 7 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.