Solved

Wildcard, Like in Vba

Posted on 2014-03-10
2
451 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 142

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now