Avatar of astoffels
astoffelsFlag for Canada asked on

VBA String Comparison Function Like

I am comparing the field in a table to one in a combo box on a form.
I can't call the function Like in the query design view. Access 2003 and 2007 is replacing the function with the call to a function called "Alike". This function does not compare strings it concatenates them.
Why am I not able to use the "Like" function in the query design view, and why do the 2 versions of access not recognize this call in the Design view of the query?
Microsoft AccessSQL

Avatar of undefined
Last Comment
GRayL

8/22/2022 - Mon
Rey Obrero (Capricorn1)


this the format of a select query with criteria pointing to a control in a form using the Like

select * from TableX
where [fieldname] like [forms]![frmMain]![combo1]


post the sql of the query you are using.











ASKER
astoffels

SELECT DISTINCT dbo_TSAllocLine.ConSysPersonCode, dbo_TSAllocLine.ClientCode, dbo_Client.ClientName, dbo_TSAllocLine.ProductCode, dbo_Product.ProductName, dbo_TSAllocLine.JobCode, dbo_Job.JobName, dbo_TSAllocLine.TaskCode, dbo_Task.TaskShortName, CDbl(dbo_TSAllocLine.ChargeableHours/100) AS Hours, dbo_TSDay.Day, [FirstName] & " " & [LastName] AS Person, IIf(dbo_TSDay.Day=0,"Monday",IIf(dbo_TSDay.Day=1,"Tuesday",IIf(dbo_TSDay.Day=2,"Wednesday",IIf(dbo_TSDay.Day=3,"Thursday",IIf(dbo_TSDay.Day=4,"Friday",IIf(dbo_TSDay.Day=5,"Saturday",IIf(dbo_TSDay.Day=6,"Sunday"))))))) & " " & Format((dbo_TSDay.EndDate-6+dbo_TSDay.Day),"dd-mmm-yyyy") AS DayOfWeek, dbo_TSAllocLine.EndDate, Nz(PeriodSelection.PeriodNumber,0) AS Period, PeriodSelection.Month, PeriodSelection.StartDate INTO tempHoursByClient
FROM ((((((dbo_TSAllocLine LEFT JOIN dbo_Client ON dbo_TSAllocLine.ClientCode = dbo_Client.ClientCode) LEFT JOIN dbo_Job ON dbo_TSAllocLine.JobCode = dbo_Job.JobCode) LEFT JOIN dbo_Product ON dbo_TSAllocLine.ProductCode = dbo_Product.ProductCode) LEFT JOIN dbo_Task ON dbo_TSAllocLine.TaskCode = dbo_Task.TaskCode) LEFT JOIN dbo_TSDay ON (dbo_TSAllocLine.EndDate = dbo_TSDay.EndDate) AND (dbo_TSAllocLine.ConSysPersonCode = dbo_TSDay.ConSysPersonCode)) LEFT JOIN dbo_Person ON dbo_TSAllocLine.ConSysPersonCode = dbo_Person.ConSysPersonCode) LEFT JOIN PeriodSelection ON dbo_TSAllocLine.EndDate = PeriodSelection.EndDate
WHERE (((dbo_TSAllocLine.ClientCode) Like [Forms]![frmReportMenu].[cmbClient]) AND ((dbo_TSAllocLine.ProductCode) Like [Forms]![frmReportMenu].[cmbProduct]) AND ((dbo_TSAllocLine.JobCode) Like [Forms]![frmReportMenu].[cmbJob]) AND ((dbo_TSAllocLine.TaskCode) Is Not Null) AND ((dbo_TSDay.Day)<=6) AND ((Nz([PeriodSelection].[PeriodNumber],0))<=53 And (Nz([PeriodSelection].[PeriodNumber],0))>=1) AND ((dbo_TSAllocLine.TimeType)=2 Or (dbo_TSAllocLine.TimeType)=16))
ORDER BY dbo_TSAllocLine.EndDate;

In the query design view (QBE) i can't use the Like function.
ASKER CERTIFIED SOLUTION
dqmq

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
astoffels

I'm not using wildcards i'm looking for exact values. I can use the Like keyword in the SQL View of a query but not in the design view because of the forced replacement of the keywords.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
astoffels

capricor1: i have tried this query on 3 different computer with versions 2007 and 2003, i got it to work from the SQL View but if I switch to the design view of the query builder it changes it to Alike.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
dqmq

The Like operator still works, the issue is that the query editor changes Likes to Alikes, much like it adds parenthesis all over the place.  What I don't understand is that since there are no wildcards in play, LIKE, ALIKE and = are logically equivalent and all should work.  But, like I posted earlier, seems like you can go with the simpler =.
Rey Obrero (Capricorn1)

check if you are have missing reference. From VBA window tools>references.

also try decompiling your db
http://www.granite.ab.ca/access/decompile.htm

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
astoffels

already tried the references, seems like everything is there... in the SQL View of the query, the like keyword works...
ASKER
astoffels

Using the "=" gave us an alternative solution but we still don't know why Alike replaces Like in the Design View QBE Query Builder for Access 2003 and 2007.
Capricorn1 put a good start to the solution, so he gets a split
First time I have to allocate points guys, so hope you all are happy
GRayL

Still don't know why you assigned a B.  Looks like you want to shoot the messenger.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
astoffels


Hi GRayL

The B is because I didn't get a full solution, it helped my SQL code but not the access DesignView Code
It was because I was dissatisfied with the solution not the person
But we found the solution and here it is
We first though it is an autocorrection, it wasn't
Then we stumbled on this
===============================================================================
This is expected behavior if you have the ANSI-92 SQL syntax option
selected. In Access 2000/2002/2003 this option is found here:

    Tools | Options... | Tables/Queries tab   (lower right corner)

I'm not using Access 2007, but I'm sure you can find it somewhere (good
luck). I'd start by clicking on that silly Office button thingy, and then
clicking on the Access Options... button. I think you will find it hidden
somewhere in that menu.

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/ 
http://www.access.qbuilt.com/html/expert_contributors.html 
==============================================================================
And this solved it
But thanks to everybody's efforts anyhow

GRayL

Thanks for the explanation.