Update Query - Advice sought

[Jason O]

Hi

I just wondered if someone could advise me if and how something could be
done. Let's say I have just 2 fields - 'Date' and 'Name'. I want an update
query to do the following:

Update 'Name' to 'X' if 'Date' between date1 and date2
Update 'Name' to 'Y' if 'Date' between date3 and date4
Update 'Name' to 'Z' if 'Date' between date5 and date6

I can do the first step easy enough, but wanted to do the other steps in the
same query. Is this possible? I have a few to do so didn't want a separate
update query for each.

If anyone could point me in the right direction that'd be great. Oh, in
design view if possible, rather than sql

Many TIA,

Jay
___

 

[Van T. Dinh]

In the Update To cell of the 'Name' column, use the expression:

IIf( ([Date] >= date1) And ([Date] <= date2), "X",
IIf( ([Date] >= date3) And ([Date] <= date4), "Y",
IIf( ([Date] >= date5) And ([Date] <= date6), "Y", [Name])

(type as ONE line in the cell.)

Note: if you use explicit date values (for date1 ... date6), they must be
expressed in the US format "mm/dd/yyyy" (or an internationally unambiguos
format like "yyyy-mm-dd") and enclosed in hashes (#).

Once you get it to work, switch to SQL view to view the constructed SQL and
learn how to read / write SQL as it is easier to communicate in these
newsgroups using SQL.

 

[Jason O]

In the Update To cell of the 'Name' column, use the expression:

IIf( ([Date] >= date1) And ([Date] <= date2), "X",
IIf( ([Date] >= date3) And ([Date] <= date4), "Y",
IIf( ([Date] >= date5) And ([Date] <= date6), "Y", [Name])

(type as ONE line in the cell.)

Note: if you use explicit date values (for date1 ... date6), they must be
expressed in the US format "mm/dd/yyyy" (or an internationally unambiguos
format like "yyyy-mm-dd") and enclosed in hashes (#).

Once you get it to work, switch to SQL view to view the constructed SQL and
learn how to read / write SQL as it is easier to communicate in these
newsgroups using SQL.

That's great - thanks a lot. Oh, and I've every intention of getting to
grips with sql. I asked for reading suggestions in a previous post so have a
shopping list of books

Regards

Jason
___

 

[Van T. Dinh]

For a comprehensive list of books, see:

http://home.bendbroadband.com/conreadsystems/accessjunkie/resources.html#Books