473,322 Members | 1,614 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Building Query on the fly...stuck!

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
3 2076
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Bruno Alexandre | last post by:
Hi guys, withou using SP, I want to be able to add a Parameter to the SQL Query and retrive the Recordset so I can use the Paging property under the recorset object.... how can I do this? I'm...
4
by: Steve Jorgensen | last post by:
A while ago, I posted a 1/2 formed idea for what was probably an overcomplicated scheme for improving how we create content using the DOM API. Since then, I've been refactoring some real-world code...
3
by: Blue Bell Trading - Customer Services | last post by:
Hi, I need to export all data that was despatched on a specified date to an excel file. I tried creating a query but I am stuck? Thanks...
5
by: Bec | last post by:
I'm in desperate need of your help.. I need to build an access database and have NO idea how to do this.. Not even where to start.. It IS for school, and am not asking anyone to do my...
8
by: Jacob Arthur | last post by:
How would I go about using a custom select string that is passed from a form to the SelectCommand parameter of SqlDataSource? I tried: SelectCommand = "<% Request.Form("hdnSelect") %>" but I...
4
by: frizzle | last post by:
Hi there, Still building my forum. I have a certain mysql-query i just can't figure out. These are my tables (simplified): categories -> id, description forums -> id, cat_id,...
7
by: KoliPoki | last post by:
Hello every body. I have a small issue. Problem: I have a table with 4 descriptor columns (type). I need to formulate a query to retrieve a count for each type so I can group by...etc. The...
1
by: colleen1980 | last post by:
When I run my query it stuck is it possible that i can debug it SELECT TOP 1 tblCredits.OPR, Min(tbl_CRCDollars.SumOfAMOUNT) AS MinOfSumOfAMOUNT, Avg(tblCredits.Credit) AS AvgOfCredit,...
3
by: wsox66 | last post by:
I am new to Access and need some help building a report. I have looked through previous posts on reports but none of them seem to answer my question completely. I am using Access 2003 and I have...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.