Solved

Wildcard, Like in Vba

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

710 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