Update Query - Advice sought

  • Thread starter Thread starter Jason O
  • Start date Start date
J

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
___
 
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.
 
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.
Click to expand...


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
___
 
Back
Top