Solved

Wildcard, Like in Vba

Posted on 2014-03-10
2
465 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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 the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

623 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