If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Really weird query responses.
Right, this has been confusing me for days now and I have thrown in the towel
and need to get some advice. I am getting some really weird results from a query. The background: I have a table that amongst other fields has fields for the total annual spend in years 1,2 and 3 of a contract. All the fields are in the Currency format. From this table I am generating a query to find the total spend in the contract using the following expression in a new field on the query: Spend: CCur(CCur(nz([Total Annual Spend Yr 1],0))+CCur(NZ([Total Annual Spend Yr 2],0))+CCur(NZ([Total Annual Spend Yr 3],0))) This new field is also set to currency format. I have then created a second query that is designed to filter out contracts above or below a specific value, and this second query is giving me greif. I have used the expression =[Enter maximum contract value] in the criteria field of this second query I have then created some example entries in the table with annual spends in year 1,2 and 3 of £1,000, £100,000 and £1,000,000 Which means the total spend query is giving results of £3,000, £300,000 and £3,000,000 int he thre examples. When i run the query and give it a minimum contract value of 2999, all three contracts appear in the query as expected and the zero value contract is filtered, which is what expect. The same happens when i enter 3000. Now when I enter 3001 the query filter out all the results, when in actual fact it should still leave the £300,000 and 3,000,000 results in there. Oh, one more thing, because work have never fully moved to office 2003, the file has to be in office 2000 format, if that makes any difference I hope I make sense, if anyone thinks they can help but need more info just ask and ill try my best to give you an answer but i am fairly new to this access stuff but any reason as to why it is being stupid would be greatly appreciated! Many Thanks Ben |
#2
|
|||
|
|||
Really weird query responses.
Declare the parameter, so Access knows its data type too. My guess is that
it is performing a string comparison. In query design view, choose Parameters on the Query menu. In the dialog, enter: [Enter maximum contract value] Currency The query should now work. BTW, your Spend field has 7 function calls. It will work, but it might be more efficient with just 3 if you use IIf() instead of Nz(). Details: http://allenbrowne.com/QueryPerfIssue.html#Nz -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "bmacrow" wrote in message ... Right, this has been confusing me for days now and I have thrown in the towel and need to get some advice. I am getting some really weird results from a query. The background: I have a table that amongst other fields has fields for the total annual spend in years 1,2 and 3 of a contract. All the fields are in the Currency format. From this table I am generating a query to find the total spend in the contract using the following expression in a new field on the query: Spend: CCur(CCur(nz([Total Annual Spend Yr 1],0))+CCur(NZ([Total Annual Spend Yr 2],0))+CCur(NZ([Total Annual Spend Yr 3],0))) This new field is also set to currency format. I have then created a second query that is designed to filter out contracts above or below a specific value, and this second query is giving me greif. I have used the expression =[Enter maximum contract value] in the criteria field of this second query I have then created some example entries in the table with annual spends in year 1,2 and 3 of £1,000, £100,000 and £1,000,000 Which means the total spend query is giving results of £3,000, £300,000 and £3,000,000 int he thre examples. When i run the query and give it a minimum contract value of 2999, all three contracts appear in the query as expected and the zero value contract is filtered, which is what expect. The same happens when i enter 3000. Now when I enter 3001 the query filter out all the results, when in actual fact it should still leave the £300,000 and 3,000,000 results in there. Oh, one more thing, because work have never fully moved to office 2003, the file has to be in office 2000 format, if that makes any difference I hope I make sense, if anyone thinks they can help but need more info just ask and ill try my best to give you an answer but i am fairly new to this access stuff but any reason as to why it is being stupid would be greatly appreciated! Many Thanks Ben |
#3
|
|||
|
|||
Really weird query responses.
It worked straight away!
Thank you so much! Regards Ben "Allen Browne" wrote: Declare the parameter, so Access knows its data type too. My guess is that it is performing a string comparison. In query design view, choose Parameters on the Query menu. In the dialog, enter: [Enter maximum contract value] Currency The query should now work. BTW, your Spend field has 7 function calls. It will work, but it might be more efficient with just 3 if you use IIf() instead of Nz(). Details: http://allenbrowne.com/QueryPerfIssue.html#Nz -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "bmacrow" wrote in message ... Right, this has been confusing me for days now and I have thrown in the towel and need to get some advice. I am getting some really weird results from a query. The background: I have a table that amongst other fields has fields for the total annual spend in years 1,2 and 3 of a contract. All the fields are in the Currency format. From this table I am generating a query to find the total spend in the contract using the following expression in a new field on the query: Spend: CCur(CCur(nz([Total Annual Spend Yr 1],0))+CCur(NZ([Total Annual Spend Yr 2],0))+CCur(NZ([Total Annual Spend Yr 3],0))) This new field is also set to currency format. I have then created a second query that is designed to filter out contracts above or below a specific value, and this second query is giving me greif. I have used the expression =[Enter maximum contract value] in the criteria field of this second query I have then created some example entries in the table with annual spends in year 1,2 and 3 of £1,000, £100,000 and £1,000,000 Which means the total spend query is giving results of £3,000, £300,000 and £3,000,000 int he thre examples. When i run the query and give it a minimum contract value of 2999, all three contracts appear in the query as expected and the zero value contract is filtered, which is what expect. The same happens when i enter 3000. Now when I enter 3001 the query filter out all the results, when in actual fact it should still leave the £300,000 and 3,000,000 results in there. Oh, one more thing, because work have never fully moved to office 2003, the file has to be in office 2000 format, if that makes any difference I hope I make sense, if anyone thinks they can help but need more info just ask and ill try my best to give you an answer but i am fairly new to this access stuff but any reason as to why it is being stupid would be greatly appreciated! Many Thanks Ben |
Thread Tools | |
Display Modes | |
|
|