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:[color=blue]
>
visionstate@googlemail.com wrote:
>[color=green]
> > 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 '*' "[/color]
> Why do you need this filter if Null?[color=green]
> > Else
> > strDept = "='" & Me.ComboDept.Value & "' "
> > End If
> >
> > If IsNull(Me.ComboSurname.Value) Then
> > strSurname = " Like '*' "[/color]
> Why do you need this filter if surname is null?[color=green]
> > Else
> > strSurname = "='" & Me.ComboSurname.Value & "' "
> > End If
> >
> > If IsNull(Me.ComboForename.Value) Then
> > strForename = " Like '*' "[/color]
> Why do you need this filter if Forename is null?[color=green]
> > Else
> > strForename = "='" & Me.ComboForename.Value & "' "
> > End If
> >
> > If IsNull(Me.ComboTeam.Value) Then
> > strTeam = " Like '*' "[/color]
> Who do you need this if the Team is Null?[color=green]
> > Else
> > strTeam = "='" & Me.ComboTeam.Value & "' "
> > End If
> >
> > If IsNull(Me.ComboAttending.Value) Then
> > strAttending = " Like '*' "[/color]
> Who do you need this if Attending is Null?[color=green]
> > Else
> > strAttending = "='" & Me.ComboAttending.Value & "' "
> > End If
> >
> > If IsNull(Me.ComboTraining.Value) Then
> > strTraining = " Like '*' "
> > Else
> > strTraining = "='" & Me.ComboTraining.Value & "' "[/color]
> Who do you need this if the Training Is Null?[color=green]
> > 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;"[/color]
>
> Why do you have equal = signs in the where clause since the variables
> have equal signs?
>[color=green]
> > 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.
> >[/color]
> 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 "...[/color]
