convert query sql to SQLServer sql

[bobh]

Hi All,

I have this query which updates a field based on the result of an IIF
statement. The table is on SQLServer and I'm linked to it and it has
many records and will take Access a very long time to run so I'm
thinking I can make this query a pass-thru query and I hope someone
can write the proper SQLServer SQL for this Access SQL
I know I have to change the IIF to Case When Else but I haven't been
able to get it figured out yet.

UPDATE tblHospLineFinal SET tblHospLineFinal.RemarkCode =
IIf(Not IsNull(tblHospLineFinal!ResubEOR),tblHospLineFinal !ResubEOR,
IIf(Not IsNull(tblHospLineFinal!EOB3Line),tblHospLineFinal !EOB3Line,
IIf(Not IsNull(tblHospLineFinal!EOB2Line),tblHospLineFinal !
EOB2Line,tblHospLineFinal!EOB1Line)));

thanks
bobh.

[Rich P]

Greetings,

since your query is not linked to any additional tables and there is no
Where clause, then you are updating your table to just one value. You
can simplify your query by extracting the value first and then passing
that value into your Update query

Value = DLookup or DSum, Dcount, ...

Update tblx Set fld1 = value

or

Update tblx Set fld1 = value where fldy is not null

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[paii, Ron]

"bobh" <vu******@yahoo.comwrote in message
news:11**********************@d55g2000hsg.googlegr oups.com...

Hi All,

I have this query which updates a field based on the result of an IIF
statement. The table is on SQLServer and I'm linked to it and it has
many records and will take Access a very long time to run so I'm
thinking I can make this query a pass-thru query and I hope someone
can write the proper SQLServer SQL for this Access SQL
I know I have to change the IIF to Case When Else but I haven't been
able to get it figured out yet.

UPDATE tblHospLineFinal SET tblHospLineFinal.RemarkCode =
IIf(Not IsNull(tblHospLineFinal!ResubEOR),tblHospLineFinal !ResubEOR,
IIf(Not IsNull(tblHospLineFinal!EOB3Line),tblHospLineFinal !EOB3Line,
IIf(Not IsNull(tblHospLineFinal!EOB2Line),tblHospLineFinal !
EOB2Line,tblHospLineFinal!EOB1Line)));

thanks
bobh.

It may be quicker run 5 querys using the test in you IIF for the WHERE
clause.

But why would you want to copy data from 1 column of a table to another for
all rows of your table? Unless you were reformatting the data?

[chris.nebinger]

UPDATE tblHospLineFinal

SET tblHospLineFinal.RemarkCode =
CASE
IIf(Not IsNull(tblHospLineFinal!ResubEOR),tblHospLineFinal !ResubEOR,
IIf(Not IsNull(tblHospLineFinal!EOB3Line),tblHospLineFinal !EOB3Line,
IIf(Not IsNull(tblHospLineFinal!EOB2Line),tblHospLineFinal !
EOB2Line,tblHospLineFinal!EOB1Line)));

On Nov 1, 1:20 pm, bobh <[e-mail]:

Hi All,

I have this query which updates a field based on the result of an IIF
statement. The table is on SQLServer and I'm linked to it and it has
many records and will take Access a very long time to run so I'm
thinking I can make this query a pass-thru query and I hope someone
can write the proper SQLServer SQL for this Access SQL
I know I have to change the IIF to Case When Else but I haven't been
able to get it figured out yet.

UPDATE tblHospLineFinal SET tblHospLineFinal.RemarkCode =
IIf(Not IsNull(tblHospLineFinal!ResubEOR),tblHospLineFinal !ResubEOR,
IIf(Not IsNull(tblHospLineFinal!EOB3Line),tblHospLineFinal !EOB3Line,
IIf(Not IsNull(tblHospLineFinal!EOB2Line),tblHospLineFinal !
EOB2Line,tblHospLineFinal!EOB1Line)));

thanks
bobh.

[chris.nebinger]

UPDATE tblHospLineFinal

SET
tblHospLineFinal.RemarkCode =
CASE
WHEN ResubEOR is not null THEN ResubEOR
WHEN EOB3Line is not null then EOB3Line
WHEN EOB2Line is not null then EOB2Line
ELSE EOB1LINE
end
OR:
UPDATE tblHospLineFinal

SET
tblHospLineFinal.RemarkCode = coalesce
(ResubEOR,EOB3Line,EOB2Line,EOB1LINE,'Unknown')

Chris Nebinger

On Nov 1, 1:20 pm, bobh <[e-mail]:

Hi All,

I have this query which updates a field based on the result of an IIF
statement. The table is on SQLServer and I'm linked to it and it has
many records and will take Access a very long time to run so I'm
thinking I can make this query a pass-thru query and I hope someone
can write the proper SQLServer SQL for this Access SQL
I know I have to change the IIF to Case When Else but I haven't been
able to get it figured out yet.

UPDATE tblHospLineFinal SET tblHospLineFinal.RemarkCode =
IIf(Not IsNull(tblHospLineFinal!ResubEOR),tblHospLineFinal !ResubEOR,
IIf(Not IsNull(tblHospLineFinal!EOB3Line),tblHospLineFinal !EOB3Line,
IIf(Not IsNull(tblHospLineFinal!EOB2Line),tblHospLineFinal !
EOB2Line,tblHospLineFinal!EOB1Line)));

thanks
bobh.

[bobh]

Both these approaches were returning nulls in the RemarkCode field.
After playing with these for a long while and not having any success
at getting them to work and I believed they should have worked. I
finally discovered that the SQL table that the dba setup on the
SQLServer has 'Allow zero lenght strings' set to yes on all the text
fields. After writing some vba code and checking for nulls it didn't
find any nulls changed the code to find Len(RemarkCode)>0 it didn't
find any so, I changed your
Case logic to say
WHEN Len(ResubEOR)>0 THEN ResubEOR

Bingo! it worked perfectly........ thanks for your help and
steering me in the right direction.
bobh.

On Nov 1, 9:00 pm, "[e-mail]"
<[e-mail]:

UPDATE tblHospLineFinal

SET
tblHospLineFinal.RemarkCode =
CASE
WHEN ResubEOR is not null THEN ResubEOR
WHEN EOB3Line is not null then EOB3Line
WHEN EOB2Line is not null then EOB2Line
ELSE EOB1LINE
end

OR:

UPDATE tblHospLineFinal

SET
tblHospLineFinal.RemarkCode = coalesce
(ResubEOR,EOB3Line,EOB2Line,EOB1LINE,'Unknown')

Chris Nebinger

On Nov 1, 1:20 pm,bobh<[e-mail]:

Hi All,

I have this query which updates a field based on the result of an IIF
statement. The table is on SQLServer and I'm linked to it and it has
many records and will take Access a very long time to run so I'm
thinking I can make this query a pass-thru query and I hope someone
can write the proper SQLServer SQL for this Access SQL
I know I have to change the IIF to Case When Else but I haven't been
able to get it figured out yet.

UPDATE tblHospLineFinal SET tblHospLineFinal.RemarkCode =
IIf(Not IsNull(tblHospLineFinal!ResubEOR),tblHospLineFinal !ResubEOR,
IIf(Not IsNull(tblHospLineFinal!EOB3Line),tblHospLineFinal !EOB3Line,
IIf(Not IsNull(tblHospLineFinal!EOB2Line),tblHospLineFinal !
EOB2Line,tblHospLineFinal!EOB1Line)));

thanks
bobh.- Hide quoted text -

- Show quoted text -