PDA

View Full Version : Wildcard character for VBA



vaioprincess
06-26-2010, 02:48 PM
Hello,

I am writing a program that consists of multiple user defined picklist values and will pull data from Access based on what is defined. Rather than write code for any possible combination of variables, I want to just pull all values if the variable is undefined - basically, set that variable to a wildcard to tell Access to give me all values for that field that match the other criteria.

I wrote this code for all of the picklist variables:
If Sheets!Grouping.GrpPickList.Value = "" Then VALUE = "" * "" Else If Sheets!Grouping.GrpPickList.Value <> "" Then VALUE = Sheets!Grouping.GrpPickList.Value

The red part is what I am having problems with. In this statement, how do you make the value be a wildcard character? I have tried quotes and apostrophes and get different errors or no data is returned.

Thanks.

Bob Phillips
06-27-2010, 10:55 AM
How does a formula pull from Access db?

vaioprincess
06-27-2010, 11:08 AM
Here's my code:

If Sheets!Grouping.GrpRMTPickList.Value = "" Then RMT = "All" Else If Sheets!Grouping.GrpRMTPickList.Value <> "" Then RMT = Sheets!Grouping.GrpRMTPickList.Value
If Sheets!Grouping.GrpEngineerPickList.Value = "" Then Engineer = "All" Else If Sheets!Grouping.GrpEngineerPickList.Value <> "" Then Engineer = Sheets!Grouping.GrpEngineerPickList.Value
If Sheets!Grouping.GrpJobTypePickList.Value = "" Then JobType = "All" Else If Sheets!Grouping.GrpJobTypePickList.Value <> "" Then JobType = Sheets!Grouping.GrpJobTypePickList.Value
If Sheets!Grouping.StimPicklist.Value = "" Then StimType = "All" Else If Sheets!Grouping.StimPicklist.Value <> "" Then StimType = Sheets!Grouping.StimPicklist.Value
If Sheets!Grouping.RRLPicklist.Value = "" Then RigRL = "All" Else If Sheets!Grouping.RRLPicklist.Value <> "" Then RigRL = Sheets!Grouping.RRLPicklist.Value
SDate = Range("SDate").Value
EDate = Range("EDate").Value
If RMT <> "All" And Engineer <> "All" And JobType <> "All" And StimType <> "All" And RigRL <> "All" Then
Sql = "Select WELLCOMP_NAME, t4EngName, Project_Type, StimulationType, RigRL, MaxOft2TaskEnd, t1AFEAmt, t5RMT, CompletionMonth, IncBOPD, IncMCFPD, IncBOEPD, AMOUNT, ActIncOil, ActIncWater, ActIncGas, ActIncBOE " & _
"From Workover_Data_Table " & _
"WHERE t5RMT = ('" & RMT & "') and Project_Type = ('" & JobType & "') and StimulationType = ('" & StimType & "') and t4EngName = ('" & Engineer & "') and RigRL = ('" & RigRL & "') " & _
"AND MaxOft2TaskEnd > #" & SDate & "# " & _
"AND MaxOft2TaskEnd < #" & EDate & "# "

It's working, and I went ahead and wrote code to pull any possible combination and it works, but I would still like to know just to have it pull everything if no value is specified.