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.