ACCESS, VBA and the Like command

I have a query that when I run it from the query builder I need to use the * as a wild card for the LIKE command.
When I run the same query from the Access form it won't work. I need to use the % with the LIKE command.
I would like to be able to run it from eithe place without having to change the wildcard.

Thanks !
Chuck LoweAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TONY TAYLORCommented:
Any chance of getting the SQL at least?
Chuck LoweAuthor Commented:
Here it is.

PARAMETERS txtUserID Text ( 255 );
INSERT INTO tbl_Plans ( PlanID, PlanName, PlanStatus, VisionClientID, ClientName, ParentCompany, PlanType, PlanSubType, ProtoTypeInd, VSTypeInd, VSType, DocType, DocTypeNo, MultiDoc, Participants, AuditID, PlanValuationDate, CRA, UserID, UpdateTS, MarkedForDeletion )
SELECT DISTINCT NZ(C.[Plan Number]), C.[Plan Name], C.[Plan Status], Nz(C.[Vision Client Id]) AS VisionClientID, C.[Client Name], IIf (C.[Parent Company] IS NULL,
C.[Client Name],C.[Parent Company]), C.[Plan Type], C.[Plan Sub Type], IIf([VGI Prototype]="Y",True,False) AS ProtoTypeInd, IIf(C.[VGI Volume Submitter Plan]="Y",True,False) AS VSTypeInd, IIf(C.[VGI Volume Submitter Plan]="Y","K","") AS CVSType, iif(C.[VGI Prototype] = "Y", "P" , iif(CVSType in ("K","B") ,"V" , iif(CVSType ="P", "P", "C"))) AS DocType, iif([VGI Prototype] = "Y", 2 , iif(CVSType in ("K","B") ,3 , iif(CVSType ="P", 4, 1))) AS DocTypeNo, " " AS MultiDoc, NZ(C.[# of Participants with Balances]) AS Participants, Nz(0) AS AuditID, Nz(C.[Plan Valuation Date]) AS PlanValuationDate, C.CRA, UCASE([txtUserID]) AS UserId, Now() AS UpdateTS, False AS MarkedForDeletion
FROM Audit_MasterClient AS C
WHERE (IsNumeric(C.[Plan Number])=True AND NZ(C.[Plan Number]) > 0 AND IsNumeric(C.[Vision Client Id])= True AND NZ(C.[Vision Client ID]) > 0 AND UCASE((C.[Plan Type]) =
"DEFINED CONTRIBUTION") and (C.[# of Participants with Balances]) > 0)
AND ((UCASE(C.[Plan Status]) = "ACTIVE" OR UCASE(C.[Plan Status]) = "ACTIVE - SERVICE ENHANCEMENT" OR UCASE(C.[Plan Status]) = "FROZEN"))  AND  ((C.[Plan Number] LIKE "06%" or C.[Plan Number] LIKE "09%"))
AND NOT EXISTS (select tbl_Plans.PlanId from tbl_Plans where tbl_Plans.PlanId =C.[Plan Number]);
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The asterisk is the wildcard delimiter for Access. Are you perhaps running this against a server backend?
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

TONY TAYLORCommented:
I would suggest to change from

AND ((C.[Plan Number] LIKE "06%" or C.[Plan Number] LIKE "09%"))

Open in new window


to

AND ((LEFT(C.[Plan Number], 2) IN ("06", "09%")))

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCCommented:
Chuck,

You say you would like to use it in "either place"; I assume you mean in Access or in a SQL Server (or other RDBMS).

The syntax will be different in other RDBMS programs, but there is a setting in Access that you can use to make your Access SQL SQL 92 compliant.  SQL 92 settingsWith the top one checked, you should be able to write SQL 92 compliant SQL which will run in Access.  Although I don't use this, you may find that you will also need to stop using quotes  in your SQL and replace those with the apostrophe to surround strings, change all of your dates in your SQL from #mm/dd/yyyy# format to 'yyyy-mm-dd' format.
Jeffrey CoachmanMIS LiasonCommented:
Well SQL must have the "%" and Access must have the "*"
So a "one size fits all" sql wont really work.
Why not just run it in one place..?

Since you are using a wildcard, I will presume that your values there (.06 and .09) are strings,...
So you could try something like this perhaps?:
((Left(C.[Plan Number],2)="06" or Left(C.[Plan Number],2)="09"))
Chuck LoweAuthor Commented:
@Scott
Yes, I'm running against a backend database, Access. As is the front end also Access.

@Tony, @Jeff
You both came up with the answer I did. I opted for the IN like Tony. (Tony you has a typo. You left the % with 09).           AND ((LEFT(C.[Plan Number], 2) IN ("06", "09%")))

@Jeff
I need to run it in 2 places. In the backend for testing purposes via query builder, hence the "*". And the front end, hence the "%" (using ADO and like a stored procedure). See function below.

    ' Variable declaration
    Dim cmd     As New Adodb.Command
    Dim p       As Integer

    ' Open the source connection.
    If connSource.State = adStateClosed Then
        connSource.Open
    End If

    ' Update Info
    With cmd
        .ActiveConnection = connSource
        .CommandText = strProc
        .CommandType = adCmdStoredProc
        .CommandTimeout = 360
       
        ' Add in parameter values - array should contain values in the order they are declared/requested.
        If IsArray(arrParams) Then
            .Parameters.Refresh
            For p = LBound(arrParams) To UBound(arrParams)
                If (Not IsNothing(arrParams(p, 0))) Then
                    .Parameters.Append .CreateParameter(arrParams(p, 0), arrParams(p, 1), adParamInput, arrParams(p, 2), arrParams(p, 3))
                End If
            Next p
        End If
       '*** .Execute runs the query
        .Execute
   
    End With

    ' Return with success.
    SourceAction = "Success"

@Dale
 You basically answered my original question. But it's too much to change the other fielfds and violates company coding standards.

@All
 I knew how to get my desired output I wanted to see if there was a easy work around to the * vs % issue I was having. Which there is not.
TONY TAYLORCommented:
Correction taken:  It was a typo.
Chuck LoweAuthor Commented:
I'll accept multiple answers. It's only fair.
Jeffrey CoachmanMIS LiasonCommented:
We are all glad we could help.
;-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.