Really weird query responses.
in [Queries]
|
From: bmacrow on 4 Jul 2008 05:57 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
From: Allen Browne on 4 Jul 2008 06:29 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" <bmacrow(a)discussions.microsoft.com> wrote in message news:CCD5E4BE-A0ED-4120-8D77-D1B0831133F6(a)microsoft.com... > 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
From: bmacrow on 4 Jul 2008 07:02 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" <bmacrow(a)discussions.microsoft.com> wrote in message > news:CCD5E4BE-A0ED-4120-8D77-D1B0831133F6(a)microsoft.com... > > 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 > >
|
Pages: 1 Prev: Subtract positive number from negative number Next: Simple update query question |