Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Wildcard, Like in Vba

Posted on 2014-03-10
2
Medium Priority
?
473 Views
Last Modified: 2014-03-10
I use to following codeto open a report based on the Customer and the site Location.


Private Sub PreviewReport_Click()
On Error GoTo Err_Command6_Click

    Dim stDocName As String
    stDocName = "FGasStatusReport"
DoCmd.OpenReport stDocName, acPreview, , "[Customer] = Forms!FGasReport!Combo0 and [SiteLocation] = Forms!FGasReport!Combo2"

Exit_Command6_Click:
    Exit Sub

Err_Command6_Click:
    MsgBox Err.Description
    Resume Exit_Command6_Click
End Sub


If a customer has multiple sites I want the option to report all sites on one report or by selected sites.

For example: using the code above I would have to run 5 separate reports for a customer with 5 sites (which is not always what is wanted).

I can get it work in a query using  Nz(([Customer] = Forms!FGasReport!Combo0]),"*"))), but would like to do this in VBA
0
Comment
Question by:SparkyP
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 39917331
you can build up the "condition" like this:
Dim strCondition as string

strCondition = "[Customer] = Forms!FGasReport!Combo0 "

if  not (Forms!FGasReport!Combo2 = "*" ) then
  strCondition  = strCondition  & "and [SiteLocation] = Forms!FGasReport!Combo2"
end if

DoCmd.OpenReport stDocName, acPreview, ,  strCondition 

Open in new window

0
 

Author Closing Comment

by:SparkyP
ID: 39917350
Guy

Absolutely spot on, many thanks for prompt response.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question