Nested IIFs

[Guest]

Im pretty good at Excel but am a new user of Access to which I moved when my
requirements became more than Excel could cope with.

Currently I am trying to produced an Itinerary for Accommodation and
activity dates.
I want to create a Query which will show/produce:
the arrival date (from 'Orders'Table) when 'DayReqd' ('OrderDetails' table)=1,
arrival date +1 when 'DayReqd'=2,
etc.,
'DayReqd' 8 = is a catch all for activities which are taken on all days

I cant seem to make a nested IIF work what might I be doing wrong

 

[John Vinson]

Im pretty good at Excel but am a new user of Access to which I moved when my
requirements became more than Excel could cope with.

Well... warning.

Access is not a "bigger version of Excel".

Excel is the definitive spreadsheet software.
Access is a relational database.

THEY ARE DIFFERENT and require different logic and different mindsets!

Currently I am trying to produced an Itinerary for Accommodation and
activity dates.
I want to create a Query which will show/produce:
the arrival date (from 'Orders'Table) when 'DayReqd' ('OrderDetails' table)=1,
arrival date +1 when 'DayReqd'=2,
etc.,
'DayReqd' 8 = is a catch all for activities which are taken on all days

I cant seem to make a nested IIF work what might I be doing wrong

Well, don't use any IIF at all:

DateAdd("d", [DayReqd] - 1, [ArrivalDate])

will calculate the departure date.

John W. Vinson[MVP]

 

[Guest]

Thank you for that information unfortunately I have already got the program
to tell me the departure date.

I do want it to relate specific information to the dates in between, i.e.
that on the day after arrival, (Day2) they will be doing xxxx. I may not be
explaining it properly, but on Orders I am showing the following information.
'Activity' 'Quantity' (eg number of days activity reqd) 'Day Required' (eg
accommodation would be required on all days, breakfast all days, but entry to
a club only on one day, Saturday). 'Activity Time' (start time of activity)

On my Itinerary sheet I will be detailing the activities required Arrival
Day, Day 2, Day 3, Departure Date etc. the times for activities and details.

Thanks for any additional assistance
--
Jab bournemouth

Im pretty good at Excel but am a new user of Access to which I moved when my
requirements became more than Excel could cope with.

Well... warning.

Access is not a "bigger version of Excel".

Excel is the definitive spreadsheet software.
Access is a relational database.

THEY ARE DIFFERENT and require different logic and different mindsets!

Currently I am trying to produced an Itinerary for Accommodation and
activity dates.
I want to create a Query which will show/produce:
the arrival date (from 'Orders'Table) when 'DayReqd' ('OrderDetails' table)=1,
arrival date +1 when 'DayReqd'=2,
etc.,
'DayReqd' 8 = is a catch all for activities which are taken on all days

I cant seem to make a nested IIF work what might I be doing wrong

Well, don't use any IIF at all:

DateAdd("d", [DayReqd] - 1, [ArrivalDate])

will calculate the departure date.

John W. Vinson[MVP]

 

[John Vinson]

Thank you for that information unfortunately I have already got the program
to tell me the departure date.

I do want it to relate specific information to the dates in between, i.e.
that on the day after arrival, (Day2) they will be doing xxxx. I may not be
explaining it properly, but on Orders I am showing the following information.
'Activity' 'Quantity' (eg number of days activity reqd) 'Day Required' (eg
accommodation would be required on all days, breakfast all days, but entry to
a club only on one day, Saturday). 'Activity Time' (start time of activity)

On my Itinerary sheet I will be detailing the activities required Arrival
Day, Day 2, Day 3, Departure Date etc. the times for activities and details.

Thanks for any additional assistance

If you have fields in your Table named Arrival Day, Day 2, Day 3 and
so on... I'm sorry, but your table structure IS WRONG. Bear in mind
that the table structure is the basis of everything; if you're
designing the tables to fit the form, you're going at it backwards!

I do not clearly understand your table structure. Could you describe
it? Do you want to automatically add four records to a table if
DayRequired is 4?

John W. Vinson[MVP]

 

[Guest]

Thanks, OK here goes.

I have an:
Order Details Table with the following fields:
'Order No.'; 'ProductID' (which displays Product Name from Products table);
'Quantity' (which will show number of days/occurances of Activity, i.e. a
guest staying 2 nights would require 2 x accommodation and 2 x breakfast);
'DayReqd' (here, against each activity line, I am entering the day the
activity is required as, 1 (1st day/arrival day), 2 (2nd day) .....etc., 8
(all days); ActivityTime (enter start time of activity, i.e. 10.00);
'UnitPrice' (Cost of Activity);

Orders Table:
'OrderID'; 'CustomerID'; 'OrderDate'; 'ArrivalDate';
'TotalNights','GroupTotal' (size of party)

As well as Customers, Products, Categories, Suppliers.

The current flow is that customer will book accommodation, breakfast, for 2
nights arr. 20 April and Night Club Entry for 1 night for 15 guests, On a
subform, Im selecting the activity, Quantity, DayReqd (1,2 ...8). This form
is then calculating cost.

When outside activities have been booked I will then go back to the form and
enter 'ActivityTime'. Some activities have a general time (breakfast 8am
onwards) others specific start times.

Prior to arrival, I will produce an Itinerary which will show all Customer
details together with:

'20 April 2006

'ActivityTime'; 'GroupTotal'; 'Activity Details' -(NightClub, address)

21 April 2006

Ditto

I had planned to use IIF, All activities where DayReqd = 1 or 8 (excl
breakfast)should show under ArrivalDate,

ArrivalDate+1
All activities where DayReqd = 2 or 8

ArrivalDate+2 etc.

etc; I would only need subsequent days if customers were staying more than 2
nights.

Sorry about the above, its probably as clear as mud, but hope you can make
head or tail or it?