UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09]. [26] = '1'
WHERE ((([HCPC struct 12-17-09].[Mod])='26')) ;
UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09]. [50] = '1'
WHERE ((([HCPC struct 12-17-09].[Mod])='50')) ;
UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[51] = '1'
WHERE ((([HCPC struct 12-17-09].[Mod])='51')) ;
UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[62] = '1'
WHERE ((([HCPC struct 12-17-09].[Mod])='62')) ;
UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[66] = '1'
WHERE ((([HCPC struct 12-17-09].[Mod])='66')) ;
UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[80] = '1'
WHERE ((([HCPC struct 12-17-09].[Mod])='80')) ;

There's a way to do it in regular SQL server using CASE, but in Access it
doesn't work!

Thx

Do you have fields named [26], [50], [51], etc.? If so why are they named as
such? Typically [HCPC struct 12-17-09]. [26] would be read as [table
name].[field name]

"E-Town" <(E-Mail Removed)> wrote in message
news:EA26B391-1C04-4A74-96B9-(E-Mail Removed)...
> UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09]. [26] = '1'
> WHERE ((([HCPC struct 12-17-09].[Mod])='26')) ;
> UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09]. [50] = '1'
> WHERE ((([HCPC struct 12-17-09].[Mod])='50')) ;
> UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[51] = '1'
> WHERE ((([HCPC struct 12-17-09].[Mod])='51')) ;
> UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[62] = '1'
> WHERE ((([HCPC struct 12-17-09].[Mod])='62')) ;
> UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[66] = '1'
> WHERE ((([HCPC struct 12-17-09].[Mod])='66')) ;
> UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[80] = '1'
> WHERE ((([HCPC struct 12-17-09].[Mod])='80')) ;
>
> There's a way to do it in regular SQL server using CASE, but in Access it
> doesn't work!
>
> Thx

On Wed, 6 Jan 2010 14:13:08 -0800, E-Town <(E-Mail Removed)>
wrote:

>UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09]. [26] = '1'
>WHERE ((([HCPC struct 12-17-09].[Mod])='26')) ;
>UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09]. [50] = '1'
>WHERE ((([HCPC struct 12-17-09].[Mod])='50')) ;
>UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[51] = '1'
>WHERE ((([HCPC struct 12-17-09].[Mod])='51')) ;
>UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[62] = '1'
>WHERE ((([HCPC struct 12-17-09].[Mod])='62')) ;
>UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[66] = '1'
>WHERE ((([HCPC struct 12-17-09].[Mod])='66')) ;
>UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[80] = '1'
>WHERE ((([HCPC struct 12-17-09].[Mod])='80')) ;
>
>There's a way to do it in regular SQL server using CASE, but in Access it
>doesn't work!
>
>Thx

Access SQL and T/SQL are two different dialects. Two relevant differences are
that Access SQL does not allow multiple SQL commands in one query; another is
that Access does not support the CASE statement.

Your table with 80 (or more) fields named [26] and [50] and [80] and so on is
seriously denormalized, which is one reason you're having difficulty! How is
table [HCPC struct 12-17-09] related to other tables? Could its data be
generated by using a CROSSTAB query using [Mod] as the Column Header?
--

John W. Vinson [MVP]

If for some reason you are stuck with the structure you have, you can use a
query like the following.

UPDATE [HCPC struct 12-17-09]
SET [HCPC struct 12-17-09].[26] = IIF([Mod]='26','1',[26])
, [HCPC struct 12-17-09].[50] = IIF([Mod]='50','1',[50])
, [HCPC struct 12-17-09].[51] = IIF([Mod]='51','1',[51])
, [HCPC struct 12-17-09].[62] = IIF([Mod]='62','1',[62])
, [HCPC struct 12-17-09].[66] = IIF([Mod]='66','1',[66])
, [HCPC struct 12-17-09].[80] = IIF([Mod]='50','1',[80])
WHERE [HCPC struct 12-17-09].[Mod] IN ('26','50','51','62','66','80')

Although, you might find it more efficient to run 6 separate queries instead
of running one query.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

> On Wed, 6 Jan 2010 14:13:08 -0800, E-Town <(E-Mail Removed)>
> wrote:
>
>> UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09]. [26] = '1'
>> WHERE ((([HCPC struct 12-17-09].[Mod])='26')) ;
>> UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09]. [50] = '1'
>> WHERE ((([HCPC struct 12-17-09].[Mod])='50')) ;
>> UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[51] = '1'
>> WHERE ((([HCPC struct 12-17-09].[Mod])='51')) ;
>> UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[62] = '1'
>> WHERE ((([HCPC struct 12-17-09].[Mod])='62')) ;
>> UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[66] = '1'
>> WHERE ((([HCPC struct 12-17-09].[Mod])='66')) ;
>> UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[80] = '1'
>> WHERE ((([HCPC struct 12-17-09].[Mod])='80')) ;
>>
>> There's a way to do it in regular SQL server using CASE, but in Access it
>> doesn't work!
>>