Beginner Access help

You may be an avid reader/contributor to my “Beginner SQL help” thread.

I’ve decided to make a separate thread for when I’m trying to use SQL within Access…because the syntax is different Access is stupid.

The question(s) posted in this particular post have been dealt with. Please move on to the latest :bump: in this thread.

How does the CASE statement work in Access?

Here’s my SQL-SQL code:

SELECT
 ...
 ,CASE
   WHEN [POLICY_ISSUE_DATE]<='20191231' THEN 'Before Times'
   WHEN [POLICY_ISSUE_DATE] BETWEEN '20200101' AND '20201231' THEN '2020'
   WHEN [POLICY_ISSUE_DATE] BETWEEN '20210101' AND '20211231' THEN '2021'
   ELSE '2022'
 END AS ERA
       ...

When I try running it in Access I get “Syntax error (missing operator) in query expression ‘CASE…END’”.

A little googling tells me that I might need to do something like this:

Select Case LNumber
   Case 1, 2
      [RegionName] = "North"
   Case 3, 4, 5
      [RegionName] = "South"
   Case 6
      [RegionName] = "East"
   Case 7, 11
      [RegionName] = "West"
End Select

Is “Select Case” its own thing…i.e., it’s completely separate from the “SELECT” statement within which it appears?

Ima gonna try it out & report back. Hang on…

I don’t think Access necessarily recognizes “BETWEEN” as an operator:

CASE
WHEN policy_issue_date <= ‘20191231’ THEN ‘Before Times’
WHEN policy_issue_date >= ‘20200101’ AND policy_issue_date <= ‘20201231’ THEN ‘2020’
WHEN policy_issue_date >= ‘20210101’ AND policy_issue_date <= ‘20211231’ THEN ‘2021’
ELSE ‘2022’
END AS era

1 Like

Thanks for that…I still got errors so I just set up a mess of nested iifs. It’s just a one-off report, so I don’t really care how sloppy it is.

My nested iifs worked well enough for one part of my query but for another part I got something along the lines of “expression too complex in query expression”.

So, I built a table that I can link up and that’s all fine and good, but my “if then”/“case when” logic is more complex than just merging two tables.

Let me show you what I mean:

SELECT...

,CASE
   WHEN PRODUCT IN ('A','B','C') THEN 'ABC'
   WHEN PRODUCT IN ('X','Y','Z') THEN 'XYZ' 
   WHEN LEFT(PRODUCT,2) = 'DE' AND LOB = 'H' THEN 'HDE'
   WHEN LEFT(PRODUCT,2) = 'DE' AND LOB = 'L' THEN 'LDE'
   WHEN rtrim(LOB) IN ('WL','TL') THEN 'LIFE'
   ELSE '?????'
  END
 ) AS LBL
...

…so, like I said, I have a table that will map products a, b, d, x, y, z to abc & xyz. I don’t know how to handle the rest of that in access…where I map the left two characters of product and the lob to something.

Any ideas? Am I going about this the completely wrong way? You know of any job openings that use SAS instead of SQL for a washed up career-ASA?

I think there is a pretty small limit on the number of nested iif statements allowed for a field calculation.

Product LOB LBL_1
A . ABC
B . ABC
C . ABC
DE H HDE
DE L LDE
X . XYZ
Y . XYZ
Z . XYZ
. WL LIFE
. TL LIFE

Expand the above table to have rows for each possible values associated with the two indexing variables. Then you can do a follow up calculation with a single iif statement:

iif(LBL_1 = ‘’, ‘???’, LBL_2) AS LBL

1 Like

This is helpful & a really good idea.

How do I handle that join? …sometimes the join is by PRODUCT, sometimes by LEFT(PRODUCT,2), and sometimes by LOB…

What I’ve seen online is that I should do the JOINS separately and then UNION them. See sql server - Join with Or Condition - Stack Overflow

Actually…I just realized I’m being pretty dumb about it…it just so happens that I can run those three queries independently and deal with it all on the back end. I think I’m good here.

1 Like

:bump:

I have an Access db that has multiple linked Tables/files. For simplicity, this example will have two:

Table File
Life s:\actuary\2020\12\Life_20201231_20210101_0323.csv
Health s:\actuary\2020\12\Health_20201231_20210101_0654.csv

Now, I need to update them to these files:

Table File
Life s:\actuary\2020\12\Life_20211231_20220101_0312.csv
Health s:\actuary\2020\12\Health_20211231_20220101_0612.csv

When I go into Linked Table Manager & try to relink it, it gets all upset because it cannot “find the object [newly linked file].” It seems to be expecting the name of the newly linked file to be “Life” or “Health”.

I found this link which has some vba code which is supposed to update linked files but (1) I don’t quite follow the logic and (2) I can’t figure out how to run it - when I hit the run-triangle, this pops up:
image

Well what the heck? Where’s my macro?

So, I think my first question is “How do you write & execute a vba macro in Access?”
Then, my second question is "How do I fix up that code above so that it will work for me?

If I can figure out the first question I might be able to handle the second question myself.

[or] if you have a better way of updating the links & names to linked files in Access I’d love to hear it.

I think the general and absolute recommendation is “Stop using Access.”
I can’t help, because i took this advice 20 years ago.

2 Likes