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.
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.
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
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
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.
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:
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.