Hi all, hope you guys are well....

Some help if you have a moment..

A simple access db to filter some data... this will be sent offsite so needs to be bullet proof. Or rather stupid proof!

*created a query to manage the data - fine
*created a few queries to use as lookup in a combo box that populates a single line table with the criteria for filtering the data. - fine
*now back in my main data query i show the table with the info i want to filter on - ok
*add to criteria iif([LenderName]=null,"*",[LenderName]) - simple you would think, have tried so many work arounds and have not cracked it. The only thing that sort of works is "In ([LenderName])" but that doesnt give me an option to view all data if none is selected!!!

2nd thing is I have the two forms brought into a third form as subforms. i can select the data in the first subform but cant refreshed the continuous form to show the results without closing and reopening it... any ideas?

Thx for help D

Where are these criteria? I would put them on the form and pass them in the WHERE clause of the DoCmd.OpenReport or OpenForm event like this, assuming the Vendor Combo takes an integer (PK):

Dim sLink As String
Dim sDoc As String

If IsNull(Me.cboVendorName) Or Me.cboVendorName = "" Then
   sLink = ""
Else
   sLink = "[VendorName] = " & Me.cboVendorName
End If

sDoc = "rptMyReportName"

DoCmd.OpenReport sDoc, acPreview, , sLink

Did that help?


mmcdonal

Look it up at: http://wrox.books24x7.com

A slight modification if Vendor Name is text.

Dim sLink As String
Dim sDoc As String

If Len(Trim(Nz(Me.cboVendorName, ""))) = 0 Then
sLink = ""
Else
sLink = "[VendorName] = '" & Me.cboVendorName & "'"
End If

sDoc = "rptMyReportName"

DoCmd.OpenReport sDoc, acPreview, , sLink


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division