Ok, I entered the following code in the OnClick event of a button on the
form. For testing purposes, I am testing using a copy of the original
form
(2 added on the end).
I am now receiving a run-time error '3075': Syntax error in string in
query
expression 'OrderType IN ('0001','0006','BTS')AND"BillDate >= #2009-05-03#
AND BillDate <= #2009-05-09#'.
Here is the code in the OnClick event:
Private Sub Command12_Click()
Dim qdfCurr As DAO.QueryDef
Dim ctlListbox As Control
Dim strSQL As String
Dim strTypes As String
Dim strWhere As String
Dim varSelected As Variant
strSQL = "SELECT OrderType, BillDate, ItemCode, " & _
"Slot FROM tblBookedOrdersWithPOsAssigned "
Set ctlListbox = [Forms]![frmMenuInventory2]![ChooseOrderType]
If ctlListbox.ItemsSelected.Count > 0 Then
For Each varSelected In ctlListbox.ItemsSelected
strTypes = strTypes & "'" & ctlListbox.ItemData(varSelected) & "', "
Next varSelected
strWhere = "OrderTypeCode IN (" & Left(strTypes, Len(strTypes) - 2) &
")
AND """
End If
If IsNull([Forms]![frmMenuInventory2]![BeginningDate]) = False Then
strWhere = strWhere & "BillDate >= " & _
Format([Forms]![frmMenuInventory2]![BeginningDate],
"\#yyyy\-mm\-dd\#") & _
" AND "
End If
If IsNull([Forms]![frmMenuInventory2]![EndingDate]) = False Then
strWhere = strWhere & "BillDate < = " & _
Format([Forms]![frmMenuInventory2]![EndingDate], "\#yyyy\-mm\-dd\#")
& _
" AND "
End If
If Len(strWhere) > 0 Then
strSQL = strSQL & "WHERE " & Left(strWhere, Len(strWhere) - 5)
Set qdfCurr =
CurrentDb.QueryDefs("qryMakeTempTableForInventorySlotVerification")
qdfCurr.SQL = strSQL
End If
End Sub
When I choose to debug, the line that is highlighted is the qdfCurr.SQL =
strSQL
Douglas J. Steele said:
What I gave was an incomplete snippet of VBA code. Incomplete since I
have
no idea how you want to trigger it.
Let's assume you want to have a button on your form that you click when
you
want to run the query. You'd put the code I gave you in the Event
Procedure
linked to the On Click event of that button.
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
AccessIM said:
Let me start by apologizing for my inexperience with code. Could you
please
tell me where I am supposed to put the code you gave me? I tried
putting
it
in the SQL view of a query and in the code view of the form and neither
of
these options worked. Thank you again for your knowledge.
:
Unfortunately, you cannot create a query that refers to a multi-select
list
box the way you can with a single-select one. You have no choice but
to
alter the SQL of your query dynamically.
Assuming OrderType is a text field, you'd use code like:
Dim qdfCurr As DAO.QueryDef
Dim ctlListbox As Control
Dim strSQL As String
Dim strTypes As String
Dim strWhere As String
Dim varSelected As Variant
strSQL = "SELECT OrderType, BillDate, ItemCode, " & _
"Slot FROM tblBookedOrdersWithPOsAssigned "
Set ctlListbox = [forms]![frmMenuInventory]![ChooseOrderType]
If ctlListbox.ItemsSelected.Count > 0 Then
For Each varSelected In ctlListbox.ItemsSelected
strTypes = strTypes & "'" & ctlListbox.ItemData(varSelected) &
"',
"
Next varSelected
strWhere = "OrderType IN (" & Left(strTypes, Len(strTypes) - 2) &
")
AND
"
End If
If IsNull([forms]![frmMenuInventory]![BeginningDate]) = False Then
strWhere = strWhere & "BillDate >= " & _
Format([forms]![frmMenuInventory]![BeginningDate],
"\#yyyy\-mm\-dd\#")
& _
" AND "
End If
If IsNull([forms]![frmMenuInventory]![EndingingDate]) = False Then
strWhere = strWhere & "BillDate <= " & _
Format([forms]![frmMenuInventory]![EndingDate],
"\#yyyy\-mm\-dd\#")
&
_
" AND "
End If
If Len(strWhere) > 0 The
strSQL = strSQL & "WHERE " & Left(strWhere, Len(strWhere) - 5)
Set qdfCurr = CurrentDb.QueryDefs("NameOfYourQuery")
qdfCurr.SQL = strSQL
End If
You can now run your query.
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
Could someone please help me with setting up a multi-select list
box?
I
have
read several threads and supporting documentation regarding the
subject
but
for some reason I am just not getting it. I would say I am at a
beginner
level when using Visual Basic and have never been able to get it
right.
My list box is called OrderType and is on frmMenuInventory. I am
using
the
selections from OrderType as criteria in a query and am not sure who
to
word
it correctly.
Currently, the user select a date range and an order type on the
form
but
I
would like them to be able to select multiple order types. Here is
the
current sql code for the query (I have nothing in here at the time
for
a
multiselect criteria):
SELECT tblBookedOrdersWithPOsAssigned.OrderType,
tblBookedOrdersWithPOsAssigned.BillDate,
tblBookedOrdersWithPOsAssigned.ItemCode,
tblBookedOrdersWithPOsAssigned.Slot
FROM tblBookedOrdersWithPOsAssigned
WHERE
(((tblBookedOrdersWithPOsAssigned.OrderType)=[forms]![frmMenuInventory]![ChooseOrderType])
AND ((tblBookedOrdersWithPOsAssigned.BillDate) Between
[forms]![frmMenuInventory]![BeginningDate] And
[forms]![frmMenuInventory]![EndingDate]));
I hope I have given enough information. Please let me know if you
need
anything further and thank you in advance for the assistance.
