CODEWITHSUNDEEP
sqlms-access-2003
Desert Spider
Desert Spider

Reputation: 778

MS-Access 2003 SQL ALike modification not working

I have a query that pulls vacation time Gained, Used, and Left by employee. I have since modified the ALike statement on the "Gain" To look for "Anniversary" Then return the value. It now returns 0 the fail statement for the Iif. Here is the SQL.

SELECT 
       SchedulingLog.UserID, 
       SchedulingLog.Category, 
       Sum(IIf([CatDetail] ALike 'Anniversary*',[Value],0)) AS Gain, 
       Sum(IIf(CatDetail ALike '%Used',[Value],0)) AS Used, [Gain]+[Used] AS [Left]
FROM SchedulingLog
GROUP BY SchedulingLog.UserID, SchedulingLog.Category
HAVING (((SchedulingLog.Category) Like "Vac*"));

Upvotes: 2

Views: 447

Answers (3)

Fionnuala
Fionnuala

Reputation: 91376

You are working purely in MS Access using the standard ANSI SQL set-up, is that correct? If so, there is no need to complicate things with ALike, just use Like and the standard wildcard *.

Upvotes: 1

Pablo Claus
Pablo Claus

Reputation: 5920

When you're using Alike you would use the ANSI wildcard (%) instead of Jet's default (*).

So

WHERE FieldName Like 'Something*'

would be

WHERE FieldName ALike 'Something%'

Upvotes: 2

BillN
BillN

Reputation: 133

A couple of things. I think you want 'Anniversary%' and 'Vac%' I'd recommend using the like function vs the alike function. I've read that access does support alike through ADO connections, but it is not documented that I could find.

Verify that CatDetail contains data in the form 'AnniversaryXXXXX' in your source table, you may need to modify it to '%Anniversary%' if the column does not always start with the 'Anniversary'

Upvotes: 1

Related Questions