What is wrong with my formula in query? 2007

[lowanam1]

Can you let me know what is wrong with my formula....
I get the answer of $0. Thank you

Nz([Total Hours],0)*Nz([tbl_BAEComp]![Rate],0)+Nz([MATERIAL VALUE],0)*Nz([tbl_BAEComp]![G&A Rate],0)*Nz([tbl_BAEComp]![Award/Inc Fee],0)+Nz([SUB QUOTES],0)*Nz([tbl_BAEComp]![G&A Rate],0)*Nz([tbl_BAEComp]![Award/Inc Fee],0)

 

[spikepl]

No idea -maybe all the values are 0 or null. Are they? Remove the formula and run the query listing all the fields that are in the formula

 

[lowanam1]

no the values are not all 0 or null. some have dollar amounts in them. I removed the formula and this are the fields in the query: I reentered the formula. Still nothing.....getting the answer of 0

SELECT tbl_BAEComp.[Reference Number], tbl_BAEComp.[WORK ITEM NUMBER], tbl_BAEComp.[SUB DISCIPLINE], tbl_BAEComp.[BAE LABOR HOURS], Round([BAE LABOR HOURS]*0.29,0) AS [Equates to 29%], [BAE LABOR HOURS]+[Equates to 29%] AS [Total Hours], tbl_BAEComp.[MATERIAL VALUE], Round([Total Hours]*1.6,0) AS Consumables, Round([MATERIAL VALUE]+[CONSUMABLES],0) AS [Total Material Value], tbl_BAEComp.[SUB QUOTES]
FROM tbl_BAEComp
ORDER BY tbl_BAEComp.[WORK ITEM NUMBER];

 

[spikepl]

ok then - show the sql incl the formula

 

[lowanam1]

SELECT tbl_BAEComp.[Reference Number], tbl_BAEComp.[WORK ITEM NUMBER], tbl_BAEComp.[SUB DISCIPLINE], tbl_BAEComp.[BAE LABOR HOURS], Round([BAE LABOR HOURS]*0.29,0) AS [Equates to 29%], [BAE LABOR HOURS]+[Equates to 29%] AS [Total Hours], tbl_BAEComp.[MATERIAL VALUE], Round([Total Hours]*1.6,0) AS Consumables, Round([MATERIAL VALUE]+[CONSUMABLES],0) AS [Total Material Value], tbl_BAEComp.[SUB QUOTES], Nz([Total Hours],0)*Nz([tbl_BAEComp]![Rate],0)+Nz([MATERIAL VALUE],0)*Nz([tbl_BAEComp]![G&A Rate],0)*Nz([tbl_BAEComp]![Award/Inc Fee],0)+Nz([SUB QUOTES],0)*Nz([tbl_BAEComp]![G&A Rate],0)*Nz([tbl_BAEComp]![Award/Inc Fee],0) AS Total
FROM tbl_BAEComp
ORDER BY tbl_BAEComp.[WORK ITEM NUMBER];

sql w/formula

 

[spikepl]

Did you write this by hand? Did you try to put it into the query designer? Because there is a bunch of GROUP BY missing, and the query designer wouldn't let you get away with it. Copy this into the SQL view of the query designer, and see what it says

Belay that - I am rambling...one sec

OK this still looks a bit handmade - did you do this in the query designer? If not then paste it in and see what it says

 

[lowanam1]

I dont know what the sql query designer is?....I created the query in the design view and entered the formula by hand into the expression builder yes . Can you tell me where to locate the query designer so that I can paste it in. Thank you

 

[spikepl]

http://www.opengatesw.net/ms-access-tutorials/Access-Articles/Microsoft-Access-Query.htm this is what I mean

 

[lowanam1]

Ok yes I created it in the query designer. I pasted the verbiage into the sql view from the query designer. still getting the $0 answer......

 

[spikepl]

attach a db with some data

 

[lowanam1]

i have attached a sample of the db.. the password is :SUBCONTRACTS all caps. the query in question is the qry_BAEComp. I really appreciate your help. Thanks

 

[spikepl]

IN the db you sent me the rate-factors are null, so when you multiply by 0 you get 0.I did ask you if all the bits were non 0.

 

[lowanam1]

for the rates they each have default values of 6.4, 11.23, 48.55. that are not reading then...So do i have to enter the rates for every calculation??

 

[spikepl]

The one record contained in the db you gave me had no values there -so you probably made it before setting the defaults. Any new records added will have these default values - so your issue was not the formula but the data you were testing it on.

Next time you have an issue of any sort, the absolute first step is to verify that the data you assume to be there are in fact there. Inspect them, print them .. anything. First then start debugging your formulas or code

 

[lowanam1]

Thank you so much! you were a great help