Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 475
  • Last Modified:

Wildcard, Like in Vba

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
SparkyP
Asked:
SparkyP
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
SparkyPAuthor Commented:
Guy

Absolutely spot on, many thanks for prompt response.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now