Fairly new to databases

[Dale Palmer]

Hi,

I'm having a problems on sorting out my work database (Access 2003).

We have a sliding invoice rate for clients whereby by we charge a flat rate
for 5 hours work and a seperate fee per hour if the work is over 5 hours. I
have set up fields for clients to insert both types of fee and now want to
run an Invoice report based on a query.

I can get the query to run and display either the flat rate or the hourly
rate*no of hours, what I want to do is automate the query so if the total no
of hours is 5 or under then use the flat rate, otherwise multiply the hourly
rate by the no of hours.

Is there a way to do this at query level as I want the reportto auto total.

Regards

Dale

 

[Chris Darnell]

Use the IIF() function to determine what calculation to do. You can use
IIF() within the field list of a SQL statement.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vafctIIF.asp

 

[Conrad]

Just use an imediate If statement (IIf) in the query. IIf([Hours] < 6, [Hours] * Flat Rate, [Hours] * Seperate Fee. Look in the Help files under IIf for more info.

 

[BerHav]

Hi,

There is a small error in the formula:
iif([Hours] < 6, FlatRate, [Hours] * HourlyRate)

--
HTH
Bernd

Just use an imediate If statement (IIf) in the query. IIf([Hours] < 6, [Hours] * Flat Rate, [Hours] * Seperate Fee. Look in the Help files under IIf for more info.

Hi,

I'm having a problems on sorting out my work database (Access 2003).

We have a sliding invoice rate for clients whereby by we charge a flat rate
for 5 hours work and a seperate fee per hour if the work is over 5 hours. I
have set up fields for clients to insert both types of fee and now want to
run an Invoice report based on a query.

I can get the query to run and display either the flat rate or the hourly
rate*no of hours, what I want to do is automate the query so if the total no
of hours is 5 or under then use the flat rate, otherwise multiply the hourly
rate by the no of hours.

Is there a way to do this at query level as I want the reportto auto total.

Regards

Dale

 

[Dale Palmer]

Thanks very much all of you

have a virtual beer on me

cheers

Dale

Hi,

There is a small error in the formula:
iif([Hours] < 6, FlatRate, [Hours] * HourlyRate)

--
HTH
Bernd

Just use an imediate If statement (IIf) in the query. IIf([Hours] < 6,

[Hours] * Flat Rate, [Hours] * Seperate Fee. Look in the Help files under
IIf for more info.