date manipulation

[angie]

i have two date fields: expiration date and payment date.
i want to create an update query that updates records containing null
payment date value to containing the same value as the expiration date.
but if the expiration date value is either Saturday or Sunday, i want to
update the records so as to have a value date refering to a working date
prior to the correspondent weekend.
how can i achieve that?

 

[John Spencer]

Use a query that looks like

UPDATE YourTable
SET PaymentDate = IIF(WeekDay(ExpirationDate) = 1,[Expiration Date]-2 ,
IIF(Weekday([ExpirationDate]) =7, [ExpirationDate]-1, [ExpirationDate]))
WHERE PaymentDate is Null

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jerry Whittle]

STANDARD WARNING: Make a backup of any tables that you update first or even
the entire database.

The SQL statement below should do the trick once you change the table name
in a few places and ensure that the field names are correct. It does NOT
handle things like Mondays or Fridays being holidays. It also assumes that
the two date fields are actaully Date/Time data types.

Oh. Watch out for word wrapping. The SET to just before WHERE should be all
on one line.

UPDATE YourTable
SET YourTable.[payment date] = IIf(Weekday([expiration date])=7,[expiration
date]-1, IIf(Weekday([expiration date])=1, [expiration date]-2, [expiration
date]))
WHERE YourTable.[payment date])Is Null ;