Code to gather information

Fly Boy 5 · External Since: Apr 07, 2007 Posts: 8

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

Jeff Boyce · External Since: Nov 04, 2004 Posts: 2342

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" <FlyBoy5 RemoveThis @discussions.microsoft.com> wrote in message
news:45EB6A7A-5910-49D9-ACFD-2997E557E557@microsoft.com...
>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

Fly Boy 5 · External Since: Apr 07, 2007 Posts: 8

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" <FlyBoy5 DeleteThis @discussions.microsoft.com> wrote in message
> news:45EB6A7A-5910-49D9-ACFD-2997E557E557@microsoft.com...
> >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
>
>
>

Jackie L · External Since: Apr 05, 2006 Posts: 78

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

Jeff Boyce · External Since: Nov 04, 2004 Posts: 2342

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" <FlyBoy5 RemoveThis @discussions.microsoft.com> wrote in message
news:8F17FC0A-26ED-4748-96FE-35BE64E20F56@microsoft.com...
> 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" <FlyBoy5 RemoveThis @discussions.microsoft.com> wrote in message
>> news:45EB6A7A-5910-49D9-ACFD-2997E557E557@microsoft.com...
>> >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
>>
>>
>>