Really weird query responses.

[bmacrow]

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

 

[Allen Browne]

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

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[bmacrow]

It worked straight away!

Thank you so much!

Regards

Ben

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

Reply to group, rather than allenbrowne at mvps dot org.

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