August 31, 2011 at 2:02 pm
There has got to be some way to combine these to do one update:
UPDATE CovElig AS cel
SET cel.GrpNmeCd = 001
WHERE (Mid([cel].[GrpName],10,1)="S"));
UPDATE CovElig AS cel
SET cel.GrpNmeCd = 006
WHERE (Mid([cel].[GrpName],10,1)="B"));
UPDATE CovElig AS cel
SET cel.GrpNmeCd = 910
WHERE (Mid([cel].[GrpName],10,1)=""));
August 31, 2011 at 2:10 pm
you can do it like this in SQL...i had to replace your MID functions witht eh SQL equivilents.
this will definitely work for SLQ server, not sure if it works for Access:
UPDATE CovElig
SET cel.GrpNmeCd = CASE
WHEN (SUBSTRING([cel].[GrpName],10,1)='S')
THEN '001'
WHEN (SUBSTRING([cel].[GrpName],10,1)='B')
THEN '006'
WHEN (SUBSTRING([cel].[GrpName],10,1)='')
THEN '910'
END
WHERE SUBSTRING([cel].[GrpName],10,1) IN('S','B','')
Lowell
August 31, 2011 at 2:16 pm
I get "syntax error (missing operator) in query expression 'CASE WHEN...." etc error.
August 31, 2011 at 2:19 pm
this is just a stab in the dark; i'm assuming the query you pasted was ok for Access,a nd also that access supports updates via a case statement...pretty reasonable guesses:
UPDATE CovElig AS cel
SET cel.GrpNmeCd = CASE
WHEN (Mid([cel].[GrpName],10,1)="S"))
THEN 001
WHEN(Mid([cel].[GrpName],10,1)="B"))
THEN 006
WHEN (Mid([cel].[GrpName],10,1)=""))
THEN 910
END
WHERE (Mid([cel].[GrpName],10,1) IN ("B","S","");
Lowell
August 31, 2011 at 2:30 pm
It doesn't work. I googled CASE in Access and it said it can only be used in VBA code. I am using SQL code in Access. I don't know why CASE doesn't work. I learned about CASE in my SQL class last year but have never actually used it at work.
August 31, 2011 at 3:00 pm
renecarol33 (8/31/2011)
It doesn't work. I googled CASE in Access and it said it can only be used in VBA code. I am using SQL code in Access. I don't know why CASE doesn't work. I learned about CASE in my SQL class last year but have never actually used it at work.
as you have already discovered CASE wont work in Access.
The Access "equivalent" is IIF
in my experience the IIF statement in Access requires a lot of "nesting" to work.
Anyway....does this help (I have removed your alias of 'CET' for readability)
UPDATE CovElig SET GrpNmeCd =
IIF (Mid(GrpName,10,1)="S", "001",
IIF (Mid(GrpName,10,1)="B", "006", "910"))
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 31, 2011 at 3:00 pm
Someone on another forum gave me the answer:
UPDATE CovElig
SET CovElig.GrpNmeCd = Switch(Mid([GrpName],10,1)="S","001", Mid([GrpName],10,1)="B","006", Mid([GrpName],10,1)="","910")
WHERE Mid([GrpName],10,1) In ("S","B","");
August 31, 2011 at 3:02 pm
Let me suggest that you create an expression in Access using the query grid that is a select statement. You can use the IIF() function to set the value - its syntax is IIF(value=True,True Value returned,False Value returned) and in your case you can nest them to look like:
IIF(Mid([cel].[GrpName],10,1)="S","001",IIF(IIF(Mid([cel].[GrpName],10,1)="B","006","910"))
Note that the case where a space is returned should be the only remaining option because of your where clause. Once the select query is giving you the values you want, you can paste the expression (less the label) into the Update value in the grid after you change the query into an Update query. Then switch the view into SQL, and you can see what the SQL statement looks like. Finally note that I assumed the column cel.GrpNmeCd you are setting is text, not numeric. If it is numeric you don't need the quotes or the leading zeros. Hope this helps.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
August 31, 2011 at 3:14 pm
The Switch() function worked. I had tried nesting IIf before I posted asking for help. It kept returning errors and the cursor was stopping at the , like it didn't like the , or it didn't like the IIf that followed. The 006, 001 and 910 are codes (like an account code) we use text fields for those because we wouldn't do any calculations on that kind of data. I also found Switch() when I googled "Access version of CASE". But I was trying to use Switch on every choice which didn't work when I ran the query. I assume that is why it didn't work.
August 31, 2011 at 3:32 pm
clear explanation of the Access "switch"
http://www.databasechannel.com/AccessArticles/Article_Conditions_NestedIfFunctionsSwitch.html
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 1, 2011 at 6:47 am
Reading that it seems like Switch() is the better choice than nesting IIfs. Do you know if Switch() will work in Oracle? We will be moving our Database to Oracle at the end of the year. I guess all the code that I write will have to be changed from Switch to Case? I haven't been able to find a clear answer in my oracle books (can't find switch in the index) or by googling it. Its going to be so much changes to code to get it to work in Oracle. But for now I need it to run in Access so I can get accurate results.
September 1, 2011 at 7:01 am
quick google found this...any good?
http://www.dbforums.com/microsoft-access/987393-decode-equivalent-ms-access.html
you may also find this useful:
http://eis.bris.ac.uk/~ccmjs/ora_sql.htm
sorry if no good...I know nothing of Oracle 🙂
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply