Currency parameter query Between/And or All

[orange]

Owl,

I typed in the data you gave me.

Here is the result of Min 200 and Max 400



If you go to my previous post#13 and look at Owl8.jpg, you'll see that Barbara has BalanceOwing of 2000.00.

[Owl]

Thank you, Orange for doing that. Please could you make 200 your minimum and 400 your maximum and see whether it erroneously includes $2000.00.


Thank you, Rainlover. As I said, I had a problem zipping the file. It wouldn't zip.

PS Orange. I see you have now done that. I don't understand why it excludes $2000.00 for you. Also, with 200 to 400 shouldn't that either be inclusive of both 200 and 400, or exclusive of both?

[orange]

Please read post#16. That's exactly what I did. And, no, 2000 is NOT included in result set.

[Rainlover]

Change your Formating to Number,Double.

[Owl]

Sorry, I made a mistake about the inclusion/exlusion of the 200 and 400 - I was looking at the wrong file.

Yes, I saw that $2000.00 was excluded on yours. What I don't understand is why it is included in mine.

Also if I enter on what you gave me, 200 to 400 gives me only 200 and 2000.

The only thing I changed is for EnterMin I had EnterMinBalanceOwing and for EnterMax I had EnterMaxBalanceOwing.

If it helps, this created an extra field in Design View of

IIf(IsNull([EnterMinBalanceOwing]) And IsNull([EnterMaxBalanceOwing]),[Previous]+[Current]-[Paid]>0,[Previous]+[Current]+[Paid] Between [EnterMinBalanceOwing] And [EnterMaxBalanceOwing])

with criteria of

<>False

keeping

BalanceOwing: ([Previous]+[Current]-[Paid])

as above.

I think we may have cross-posted to each other.

[Owl]

Thank you, Rainlover, but it is still doing it. First I changed it to Number, Double leaving Format as Currency and it didn't work. Then I changed the Format to Fixed to 2 decimals and it still didn't work.

Should I do a Fix and Repair (or whatever it is called) on the database? However, it is doing it in my real database and in my little sample one so I doubt that that is the problem.

I will try it though - I am prepared to do anything to get this to work short of taking poison, lol! Or anything that isn't immoral, illegal or fattening, as my mother used to say.

[orange]

So, do you have it working now?
The only thing I see as different is that I ahve brackets around
([Previous]+[Current]-[Paid])

[Owl]

Nope, the Compact and Repair didn't help either, although it reduced the size of the database dramatically.

[Owl]

Orange, I am replying before I put in those brackets because we cross- posted again and my last reply looks like a reply to your last one, but it isn't. I thought I had the brackets, but I will check now.

[Owl]

I added the brackets in both places that they were missing (I had them in on the fieldname) and it is still bringing up R200 and R2000 instead of the 3 others.

[Rainlover]

Get rid of the formating also.

Can you post thesample DB unziped?

[Owl]

Do you mean the formatting in the table? I'll try that. I should be able to send the file unzipped now that I have done the Compact and Repair. If I remember correctly, I think it is smaller than the maximum size. I will try first removing the formatting and if that doesn't fix it, then I will send the file, if I am right about the size.

Thanks again for staying with me, both of you.

Getting rid of the formatting didn't help.

I am going to try to send the file now.

[orange]

Owl,

You are using currency, right? You aren't getting test data from somewhere?

I haven't worked with International settings etc, and i don't have Acc2007, so my comments and advice are based on Canadian $ and Acc2003.

[Owl]

Thanks for your reply, Orange.

I was using Currency until Rainlover asked me to take out the formatting and leave it on Number, Double, but it behaved just as it did when it was formatted as currency.

Our Currency works exactly as yours does except that your sign is $ and ours is R and yes this is my data that I put together as a sample database because of the R200/R2000 dilemma I had in the actual database.

The Compacted and Repaired sample database (now in Access 2003) is attached to my previous post.

[June7]

This criteria works in my query:

Between Val(Nz([Enter the MINIMUM of BalanceOwing or leave blank for ALL],0)) And Val(Nz([Enter the MAXIMUM of BalanceOwing or leave blank for ALL],999999999))

Currency datatype and formatting not an issue.

I now recall that there can be issue with Nz in that it is supposed to return a variant but must be treating as string in this case. Guess I never used it in quite this situation so didn't come to mind when I suggested and I've never encountered problems with Nz except when I tried to use it with Excel VBA. (The evil of parameter prompts exposed!) So I used Val to convert to number.
http://allenbrowne.com/QueryPerfIssue.html