Code to gather information

[Fly Boy 5]

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]

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]

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

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

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]

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

 

[Jeff Boyce]

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.

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

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

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