run a make table query using parameters in multi-select list box

[Marianne]

I need to be able to run a make a table query using the selection in a multi
select list box as the criteria. The code I have tried (along with many
other) gives me Error 3067 - Query input must contain at least one table or
query. Could you please let me know what is wrong with the code I'm using.
Private Sub cmdPreview_Click()

On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.
Dim flgSelectAll As Boolean
Dim strSQL As String
Dim db As Database
Dim Q As QueryDef

strDelim = """" 'Delimiter appropriate to field type. See
note 1.
strDoc = "MYOB_activityslip"


'Loop through the ItemsSelected in the list box.

With Me.lstCarer
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[Carer] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Carer: " & Left$(strDescrip, lngLen)
End If
End If

Set db = CurrentDb()
Set Q = db.QueryDefs("MYOB_activityslip")
strSQL = "SELECT qry_Staff.[Last Name], qry_Staff.[First Name],
IIf(tblService_Date_and_Times!Sleepover=True,'Sleepover',tblContracts![Service
Type]) AS ServiceType,
Format(Sum(IIf(tblService_Date_and_Times!Sleepover=True,4,IIf([Start_Time]>[End_Time]
And
tblService_Date_and_Times!Sleepover=False,(([End_Time]-[Start_Time])*24)+24,([End_Time]-[Start_Time])*24))),'Fixed')
AS Units, IIf(tblContracts![Service No]>100000 And
tblService_Date_and_Times!ServDate>tblContracts![End Date],'Ex' &
tblContracts![Service No],tblContracts![Service No]) AS Job,
tblContracts.Customer, tblContracts.Rate, 'Base Hourly' AS PayCat INTO
tblMYOBActivity" & _
"FROM (tblContracts RIGHT JOIN tblService_Date_and_Times ON
tblContracts.[Service No] = tblService_Date_and_Times.Service_Plan_ID) LEFT
JOIN qry_Staff ON tblService_Date_and_Times.Carer = qry_Staff.Carer" & _
"Where ((((tblService_Date_and_Times.ServDate) >=
[Forms]![frmprStHrsSelect]![StartDate] And
(tblService_Date_and_Times.ServDate) <= [Forms]![frmprStHrsSelect]![EndDate])
And ((tblService_Date_and_Times.ShiftCan) = False) And
((tblService_Date_and_Times.Verified) = True)AND ([Carer] In (" & strWhere &
"))" & _
"GROUP BY qry_Staff.[Last Name], qry_Staff.[First Name],
IIf(tblService_Date_and_Times!Sleepover=True,'Sleepover',tblContracts![Service
Type]), IIf(tblContracts![Service No]>100000 And
tblService_Date_and_Times!ServDate>tblContracts![End Date],'Ex' &
tblContracts![Service No],tblContracts![Service No]), tblContracts.Customer,
tblContracts.Rate, 'Base Hourly'" & _
"ORDER BY
IIf(tblService_Date_and_Times!Sleepover=True,'Sleepover',tblContracts![Service Type]);"

Q.SQL = strSQL

DoCmd.OpenQuery "MYOB_activityslip"


Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler

End Sub

 

[John W. Vinson]

I need to be able to run a make a table query using the selection in a multi
select list box as the criteria.

Why?

MakeTable queries are VERY rarely necessary. I did not try to dig through your
extensive code trying to debug it; have you tried using the VBA debugger to do
so, seeing what strings it is building up?

My main question though: what purpose is served by creating a new table
apparently from data which is already in your tables? You can base a Form, or
a Report, or an Export on a select query; if you're assuming that you must
create a new table to do so, that assumption is incorrect.

John W. Vinson [MVP]

 

[Marianne]

I had to run a make table query as I needed to use a parameter for the date
field. If you use the format function on the date field so as not to get the
time then the parameter returns an incorrect date range.

 

[John W. Vinson]

I had to run a make table query as I needed to use a parameter for the date
field. If you use the format function on the date field so as not to get the
time then the parameter returns an incorrect date range.

I'm sorry, this is making no sense.

MakeTable queries are NOT needed to run date parameter queries. They wouldn't
even help with doing so.

What is the query you're trying to run? I'm sure that a parameter query can be
used.

John W. Vinson [MVP]

 

[Marianne]

Ok, what I need to do is to export to a .txt file. I have a form where the
user selects employees names and a date range. Using vba I run a macro to
export the query. When you export a date field in a query it includes a time
field of 00:00. To exclude the time field you have to use the Format
(),"Short date" function. If you use the format function then the date range
is incorrect because now the field is a text field. To get around this, I
created a make table query for the date range, but now I can't use the multi
select range in vba as this is used for opening a report. I don't know how to
write the vba to open a query using multi select. This is what I need the
help for.

 

[John W. Vinson]

Ok, what I need to do is to export to a .txt file. I have a form where the
user selects employees names and a date range. Using vba I run a macro to
export the query. When you export a date field in a query it includes a time
field of 00:00. To exclude the time field you have to use the Format
(),"Short date" function. If you use the format function then the date range
is incorrect because now the field is a text field. To get around this, I
created a make table query for the date range, but now I can't use the multi
select range in vba as this is used for opening a report. I don't know how to
write the vba to open a query using multi select. This is what I need the
help for.

The trick is to use the date field TWICE: once with the Format() function for
export, and once "as is" for the search criteria. If you uncheck the Show
checkbox in the design grid (or, equivalently, just include the field in the
WHERE clause but not the SELECT), the field being used for criteria will not
be exported.

John W. Vinson [MVP]

 

[Marianne]

Off course, the answer is always something so simple. But, my main question
is how to create a query using the multi-select list box on a form?

 

[John W. Vinson]

Off course, the answer is always something so simple. But, my main question
is how to create a query using the multi-select list box on a form?

Well, I certainly didn't see that brought up in this thread - perhaps I missed
it or it didn't get to my server.

Here's my VBA routine to generate a query based on multiple selections in a
listbox.

Private Sub cmdProcess_Click()
' Comments : Update the AnimalCondition table based on the selections in
' the unbound multiselect listbox lstHealthIssues.
' Newly selected rows will be added to the table, newly
cleared
' rows will be deleted.
' Parameters: None
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Populate the AnimalCondition table with the selected issues
On Error GoTo PROC_ERR

Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("AnimalCondition", dbOpenDynaset)
With Me!lstHealthIssues
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this AnimalID-HealthID combination is
currently
' in the table
rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _
& "[HealthIssueID] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!AnimalID = Me.AnimalID
rs!HealthIssueID = lngCondition
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.subAnimalCondition.Requery

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cmdProcess_Click:" _
& vbCrLf & Err.Description
Resume PROC_EXIT

End Sub



John W. Vinson [MVP]