Field Criteria, need to add an allowance of xy

[tinyevil777]

Hey there,

My query validates the Total from the Hold table against the Total from the grn table. I've got this working fine using

<>[grn]![Total]

I want to now add an allowance of (lets say) £1.00 either side of the Total.

i.e.

If a total in Hold is £25.00, and the total for the same OrderNo in grn is £24.01 or £25.99, it will NOT flag up. However, if the total in grn is £26.01, then it would action the query.

What code/criteria would i need to use to do this?

I'm the first to use a trial and error method, but i'm really at a loss where to begin with this.


Thank you in advance!

Tom

 

[John Big Booty]

Try;

<[grn]![Total]-1 And >[grn]![Total]+1

 

[tinyevil777]

Hi John, unfortunately that didn't work. The syntax was valid, but it returned no records, when it should have. I'm a loss, is it possible?

 

[John Big Booty]

How about;

Not Between [grn]![Total]-1 And [grn]![Total]+1

 

[tinyevil777]

That worked great, thank you very much John for your help!

 

[Brianwarnock]

Try;

<[grn]![Total]-1 And >[grn]![Total]+1

Just for the record it should have been an OR not an AND

Brian

 

[tinyevil777]

Just for the record it should have been an OR not an AND

Brian

When i try to use "or", it tells me that i have not entered the keyword And in the Between...And operator.

Will the And keyword work? Or will i need to find a new code to include the Or keyword?

 

[Brianwarnock]

When i try to use "or", it tells me that i have not entered the keyword And in the Between...And operator.

Will the And keyword work? Or will i need to find a new code to include the Or keyword?

If you read my post you will see that I was referring to the post using < And > , not the Between... And ... Function.


Brian

 

[tinyevil777]

Ah i see, my bad! Thanks to both of you for your help, is much appreciated!

 

[Brianwarnock]

No problem, no doubt your mind was on the last post you read, I only posted in case anybody was curious

Brian

 

[John Big Booty]

Just for the record it should have been an OR not an AND

Brian

Doh!! Fingergraphical error

 

[tinyevil777]

Afternoon all!

Just to re-open this thread... My project is nearing completion, and upon testing with live data, i've found that the query using the above criteria hangs.

Not Between [grn]![goods]-3 And [grn]![goods]+3

I've tested a few variations of this, and it continues to hang. If i take that code out, and set it to

[grn]![goods]

the query processes in seconds. This makes me think that there's something in the code thats causing it to fall over.

Are there any key pointers to look out for when queries hang?

 

[Brianwarnock]

You say it hangs now that you are using live data, this suggests that it didn't with test data - what's the difference? What weren't you testing for?

Having asked that is it possible that it may not have hung but if the increase in data is large that it is processing too slowly.

To help I think that we have at least to see the SQL and for you to confirm whether it has run with test data.


Brian