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

date: Fri, 4 Jul 2008 02:57:01 -0700   author:   bmacrow

Re: 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
news:CCD5E4BE-A0ED-4120-8D77-D1B0831133F6@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

date: Fri, 4 Jul 2008 18:29:03 +0800   author:   Allen Browne lid

Re: 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
> news:CCD5E4BE-A0ED-4120-8D77-D1B0831133F6@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 
> 
>

date: Fri, 4 Jul 2008 04:02:01 -0700   author:   bmacrow