Re: Update Query - Advice sought

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.

--
HTH
Van T. Dinh
MVP (Access)



"Jason O" <zeugma@xxxxxxxxxxxxxx> wrote in message
news:C0691F39.6B2C%zeugma@xxxxxxxxxxxxxxxxx
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
___



.

Relevant Pages

  • Re: complex filter and calculations in access
    ... when using a subquery on the same table as the main query, ... switch to SQL view. ... of your database using the from address in this post. ...
    (microsoft.public.access.queries)
  • Re: Strange behavior with Macro - Import from Access
    ... is the SQL text correct - ie does it run in an Access query? ... Dim con As New ADODB.Connection ... >>> into a specific cell in my excel sheet. ...
    (microsoft.public.excel.programming)
  • RE: Error in SQL
    ... paste in the SQL statement, then switch back to the query grid. ... "jokobe" wrote: ...
    (microsoft.public.access.formscoding)
  • Re: Need help with initial design
    ... I copied your text, created a new query, switched to sql and pasted then ... Equip = EquipID, CustID, Make, Model, Serial ... View) then switch to Design Mode it will make much more sense. ...
    (microsoft.public.access.tablesdbdesign)
  • Managing Transaction using ADO.net
    ... User can change any cell values at anytime. ... users can't query this table, which of course is not acceptable. ... But if I mimic above process in SQL Query Analyzer I can write Insert/Update ... Queries with UPDLOCK and can still manage to Query table in other connections. ...
    (microsoft.public.dotnet.languages.vb)