Having problems Filtering Listbox

[Zoe]

I have a listbox that I want to be able to filter by several different
fields. I have combo boxes for Vendor and Status fields that are working
great. I also have two date fields (Check Date, Invoice Date) that I am
filtering by and that is also working. The last 2 fields I want to filter by
are CheckAmount & InvoiceNumber. I can't seem to get it to work. Here is some
information:

Form listbox is on is not bound.
ListInvoices is my listbox (recordsource is qryInvoice)
FiltInvNum is unbound txt box for Invoice Number
FiltCheckAmount is unbound txt box for Check Amount

Here is an example of what I have for code behind a button to filter by
Check Number:

Dim iFilterCheckNumber As String

If IsNull(FiltCheckNum) Then
MsgBox ("Please provide a check number to filter by.")

Else
iFilterCheckNumber = FiltCheckNum 'value from unbound check number field

Me.ListInvoices.RowSource = "SELECT qryInvoice.Address1,
qryInvoice.OccupantName, qryInvoice.VendorName, qryInvoice.InvoiceNumber,
qryInvoice.InvoiceDate, qryInvoice.AmountApproved, qryInvoice.InvoiceStatus,
qryInvoice.CheckNumber, qryInvoice.CheckDate, qryInvoice.Requester,
qryInvoice.RequestType, qryInvoice.InvoiceID FROM qryInvoice WHERE
qryInvoice.CheckNumber = " & iFilterCheckNumber

End If

For the field I filtered by date I had this:
Const conDateFormat = "\#mm\/dd\/yyyy\#"

and then at the end of the Me.ListInvoices.RowSource line I have this:
WHERE qryInvoice.CheckDate = " & Format(iFilterCheckDate, conDateFormat)

Do I have to do something similar to my CheckNumber & CheckAmount code to
define a currency format? I am at a loss. Help much appreciated! Hope I gave
you enough info.

Thanks!

 

[Tom Wickerath]

Hi Zoe,

I have some Query By Form (QBF) examples that you might find helpful:

http://www.accessmvp.com/TWickerath/downloads/elements.zip
http://www.accessmvp.com/TWickerath/downloads/Chap08QBF.zip

The first one, elements.zip, uses a single unbound multi-select list box to
display records in a subform. This is the simplist QBF example that I have
available, where one is creating the WHERE portion of a SQL statement
on-the-fly. The second example is a bit more involved, where a user can enter
search criteria in a variety of unbound controls, with the results displayed
in a subform. You mentioned filtering a list box whereas my samples display
the results in a subform. However the methods should be quite similar.

Finally, I have a third example posted on the Seattle Access web site, as
the download for Feb. 2008:

http://www.seattleaccess.org/downloads.htm

See this download:
Query By Form - Multi Select
Tom Wickerath, February 12, 2008


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________