I have the following in a query field. Expr1: +[LABOR]+[TRAVCOST]
I need to modify this formula so that if the service is warranty rather than
charge, it would subtract the above plus other fields such as Airfare, auto,
hotel, living expenses, ect.

Thanks for any help you may provide.

Fly Boy 5

It sounds like you are saying that your table has a field for each category
of cost. If so, that's a spreadsheet, not a relational database!

Instead, consider a table like the following (untested):

tblCost
CostID
Amount
CostCategoryID (this points to another table that lists cost
categories like "labor" and "travel" and ...)
CostDate

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

"Fly Boy 5" <(E-Mail Removed)> wrote in message
news:45EB6A7A-5910-49D9-ACFD-(E-Mail Removed)...
>I have the following in a query field. Expr1: +[LABOR]+[TRAVCOST]
> I need to modify this formula so that if the service is warranty rather
> than
> charge, it would subtract the above plus other fields such as Airfare,
> auto,
> hotel, living expenses, ect.
>
> Thanks for any help you may provide.
>
> Fly Boy 5

Thanks Jeff,

It's a linked table in Access 2007 called service which contains all hrs,
expenses and revenue. I list the type of service in one field which should
control how the above is calculated. I'm trying to track revenue for each
call but warranty should subtract and produce a negitive figure.
Sorry for not explaining very well and thanks for your efforts.

Fly Boy 5



"Jeff Boyce" wrote:

> It sounds like you are saying that your table has a field for each category
> of cost. If so, that's a spreadsheet, not a relational database!
>
> Instead, consider a table like the following (untested):
>
> tblCost
> CostID
> Amount
> CostCategoryID (this points to another table that lists cost
> categories like "labor" and "travel" and ...)
> CostDate
>
> Good luck!
>
> Regards
>
> Jeff Boyce
> Microsoft Access MVP
>
> "Fly Boy 5" <(E-Mail Removed)> wrote in message
> news:45EB6A7A-5910-49D9-ACFD-(E-Mail Removed)...
> >I have the following in a query field. Expr1: +[LABOR]+[TRAVCOST]
> > I need to modify this formula so that if the service is warranty rather
> > than
> > charge, it would subtract the above plus other fields such as Airfare,
> > auto,
> > hotel, living expenses, ect.
> >
> > Thanks for any help you may provide.
> >
> > Fly Boy 5
>
>
>

In your query, put a formula similar to the following
Cost:
IIF([ServiceType]="warranty",(Nz([Labor])+Nz([TravCost]))*-1,Nz([Labor])+Nz([TravCost]))


"Fly Boy 5" wrote:

> I have the following in a query field. Expr1: +[LABOR]+[TRAVCOST]
> I need to modify this formula so that if the service is warranty rather than
> charge, it would subtract the above plus other fields such as Airfare, auto,
> hotel, living expenses, ect.
>
> Thanks for any help you may provide.
>
> Fly Boy 5

I'm still having some trouble visualizing the data/structure.

You posted in the newsgroup dedicated to supporting table (structure) and db
design ... would you please post a description of your table (see my
previous response for an example)?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


"Fly Boy 5" <(E-Mail Removed)> wrote in message
news:8F17FC0A-26ED-4748-96FE-(E-Mail Removed)...
> Thanks Jeff,
>
> It's a linked table in Access 2007 called service which contains all hrs,
> expenses and revenue. I list the type of service in one field which
> should
> control how the above is calculated. I'm trying to track revenue for each
> call but warranty should subtract and produce a negitive figure.
> Sorry for not explaining very well and thanks for your efforts.
>
> Fly Boy 5
>
>
>
> "Jeff Boyce" wrote:
>
>> It sounds like you are saying that your table has a field for each
>> category
>> of cost. If so, that's a spreadsheet, not a relational database!
>>
>> Instead, consider a table like the following (untested):
>>
>> tblCost
>> CostID
>> Amount
>> CostCategoryID (this points to another table that lists cost
>> categories like "labor" and "travel" and ...)
>> CostDate
>>
>> Good luck!
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Access MVP
>>
>> "Fly Boy 5" <(E-Mail Removed)> wrote in message
>> news:45EB6A7A-5910-49D9-ACFD-(E-Mail Removed)...
>> >I have the following in a query field. Expr1: +[LABOR]+[TRAVCOST]
>> > I need to modify this formula so that if the service is warranty rather
>> > than
>> > charge, it would subtract the above plus other fields such as Airfare,
>> > auto,
>> > hotel, living expenses, ect.
>> >
>> > Thanks for any help you may provide.
>> >
>> > Fly Boy 5
>>
>>
>>