Currency parameter query Between/And or All

[Owl]

I want to use and know how to use Between/And for a currency field, but what I don't know is how to have the option to show all records. Please could somebody help me with this.



Thank you in anticipation

[June7]

Seems fairly straightforward, but I have never used Currency datatype.

Amt
$23.00
$9.00
$57.00

Criteria: Between 1 And 10
Should return only the $9.00 record

Wait, just reread post and caught the 'option for all records' condition. You want to return all records if none meet the BetweenAnd criteria?

This was the only way I can get it to work. Create a field in query with expression:
Match: IIf(([Amt] Between 1 And 9) Or DCount("Amt","Table1","Amt Between 1 and 9")=0,"Y","N")
Set criteria for this field to "Y"
I used literal values 1 and 9. These could be replaced with input parameters, such as reference to textboxes on form. If other filter criteria are applied to the data in this same query, would have to also be in the DCount.

[Owl]

Thank you for replying. However, I think you have misunderstood what I am trying to do, or perhaps I didn't give enough detail. I need the user to enter his/her own figures in a minimum and maximum or All records. There are 5669 records, and the user may need to see just a certain range of BalanceOwing. The following works, except for the ALL records:

Between [Enter the MINIMUM of BalanceOwing or leave blank for ALL] and [Enter the MAXIMUM of BalanceOwing or leave blank for ALL]

I didn't add that previously as I realise that what I need to enter in the query criteria could differ a lot from that.

I tried the beginning and end of the following which works on a text field:

Like"*"&(Between [Enter the MINIMUM of BalanceOwing or leave blank for ALL] and [Enter the MAXIMUM of BalanceOwing or leave blank for ALL])&"*"

but it didn't work.

Thank you in anticipation.

[June7]

I did say the 1 and 9 could be replaced with input parameters. You are using parameter prompt in query? I never do that because can't validate entry. What if user accidentally enters o instead of 9? The query will still run, or try to, but the input will either error or results will not be as expected.

Another option is to pass alternate max and min values if user leaves blank:
Between Nz([Enter the MINIMUM of BalanceOwing or leave blank for ALL],0) And Nz([Enter the MAXIMUM of BalanceOwing or leave blank for ALL],999999999)

This won't return records where amount field is null. Would need an Or Is Null criteria.

Do like this better than my DCount solution. Wish I had come up with it earlier, like DUH!

[Rainlover]

Try this.

Assuming you have a Table tblBalance and a Field BalanceOwing.

SELECT tblBalance.BalanceOwing
FROM tblBalance
WHERE (((tblBalance.BalanceOwing) Between [Enter the MINIMUM of BalanceOwing or leave blank for ALL] And [Enter the MAXIMUM of BalanceOwing or leave blank for ALL])) OR ((([Enter the MINIMUM of BalanceOwing or leave blank for ALL]) Is Null) AND (([Enter the MINIMUM of BalanceOwing or leave blank for ALL]) Is Null));

[Owl]

Thank you for your reply and your help June7 and Rainlover. However, I still have not the result I need.

Rainlover, did you mean Maximum in second bracketed duplication instead of Minimum? I have only just realised that now and perhaps that is why it created an extra field. However, the problem below doesn't relate to that.

To summarise what is below, June7’s and Rainlover’s solutions work, but I get R2000.00 erroneously coming up when I enter a minimum of 200 and a maximum of 400 in the parameter prompts. I have tried everything I can think of but don’t know how to incorporate BETWEEN and AND for this not to happen to the calculated field BalanceOwing. The criteria works perfectly in all the non-calculated fields, Previous, Current and Paid. This is as far as you may need to read. However, I have added the rest in case it helps.

I hear what you are saying about validation, June7, and I will remember it for the future, but this database has to be done with parameter prompts.

There won’t be any nulls (though lots of zeroes), because the field is a calculated field where all the components are required fields, and their defaults are 0 – so I don’t need the Nz option, but, as it does no harm to have it, and it is working except for the problem referred to below, it is a good idea to use it. However, I still have a problem.

I have created and and wanted to attach the sample database isolating my problem, but for some reason I am having trouble zipping the file. As is evident in Query 4, my current problem (R2000.00 being read as R200.00), does not relate to either of your criteria only, as Query 4 doesn’t use your criteria and it has the same unexpected result. I have examined my fields and see nothing wrong, so it seems to me the problem is with using Between and And with a calculated field. However, perhaps something just needs to be added to qualify it more exactly.

Query 1 (No BalanceOwing Criteria) to shows all 6 records.
Query2 (June7’s suggestion) uses your criteria results in including R2000.00 for 200 to 400, but works otherwise.
Query3 (simply: Between 200 and 400) works perfectly for 200 to 400, but user can’t specify and it doesn’t allow All records.
Query4 (trying to replace 200 and 400 with user’s parameter choice) doesn’t allow All and includes R2000.00 for 200 to 400.
Query5 (trying to format the field to Currency although it was displaying as Currency) doesn’t work at all. This was the only way I could get it to accept what I tried. It is obviously very wrong.
Query6 (using Rainlover’s suggestion from Between onwards) also results in the R2000.00/R200 problem but works otherwise.
Query7 is to work/practise on.

As I am having a problem zipping the sample database for some reason, I will just describe it.
There is a table called Table1 with the fields:
ID - Autonumber
Person - Text
Previous – Currency; Required field; Indexed; Default 0
Current – Currency; Required field; Indexed; Default 0
Paid – Currency; Required field; Indexed; Default 0

All the queries have the same fields (except that Query 6 created one extra):
Person
Previous
Current
Paid
BalanceOwing:[Previous]+[Current]-[Paid]
BalanceOwing’s criteria as follows:

Query1 – none
Query2 - Between Nz([Enter the MINIMUM of BalanceOwing or leave blank for ALL],0) And Nz([Enter the MAXIMUM of BalanceOwing or leave blank for ALL],999999999)
Query3 - Between 200 And 400
Query4- Between [Type Minimum for BalanceOwing] And [Type Maximum for Balance Owing]
Query5 - Format((([Previous]+[Current]-[Paid])) Between [Type Minimum for BalanceOwing] And [Type Maximum for Balance Owing],"Currency")
For Query6, I copied in Rainlover’s suggestion and it created from Between onwards and it left:

Between [Enter the MINIMUM of BalanceOwing or leave blank for ALL] And [Enter the MAXIMUM of BalanceOwing or leave blank for ALL]

in that field and it created a second field called: [Enter the MINIMUM of BalanceOwing or leave blank for ALL]

and entered in the Or row, the following:

Is Null And Is Null

Thank you again in anticipation.

[orange]

OWL,
You may want to use a Form to get some parameters from the User. As June says, you could validate the input, then build the query according to User selections.

Usually, the user interacts via Forms, not queries or directly with Tables.

There are some great tutorials at
http://www.fontstuff.com/access/index.htm

Good luck with your project.

[Owl]

Thank you for your reply, Orange.

I know that users don't interact with tables and queries, nor do mine. However, I was trying to be as brief as possible and to stick to the point. The database I described in my last post, was simply a SAMPLE one I created to highlight my problem and it is nothing like the actual database.

This query generates a report. The user will click on the switchboard menu for the appropriate report and select from about 10 parameters (e.g. Branch etc). In must cases they will accept all records.

As I mentioned I will consider using a form for future databases. However, for this one, I want to use parameter prompts to help generate the prompt.

What I need to know is how to use Between and And on a caculated field in the parameter prompt. The criteria that June7 (and Rainlover) gave me work perfectly on the NON-CALCULATED currency fields of Previous, Current and Paid (not that I am using those fields for parameter prompts in my report - just that I tested the criteria on them). I need them to work on the calculated field of BalanceOwing:

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

Thank you in anticipation of help.

[orange]

I have tried a query using Currency parameters on a table. You may be able to adapt the syntax to your specific issue.

The table structure is in Owl1.jpg

The query is: (see Owl1.jpg also)

Parameters [EnterMin]  currency,[EnterMax]  Currency;
SELECT Cabinet.CabinetCode, Cabinet.Component, [Cost]*[Quantity] AS TotalCost
FROM Cabinet
WHERE  iif(IsNull(EnterMin) and IsNull(Entermax),Cost >0,Cabinet.Cost Between [EnterMin] And [Entermax]);

Sample records are in Owl2.jpg

If you just hit Enter for the EnterMin and EnterMax prompts, the query uses Where Cost >0 (all records in my case)
If you enter nonNull values, and EnterMin is < EnterMax the query uses
Where Cabinet.Cost Between [EnterMin] And [Entermax]


Sample output is in Owl3.jpg
I hope this is useful.


Edit: Forgot the attachments...

[orange]

I adjusted the previous query to Between on a calculated field
([Cost] * [Quantity])

Owl4.jpg shows the revised query
Owl5 shows the result when
EnterMin was 12 and EnterMax was 59

Good luck

PS: I think you have to spell out the calculation. If i try to use Expr1 in my WHERE clause, it prompts
for a value. But it's fine if I use ([Cost]*[Quantity])

[Owl]

Thank you for your reply, Orange, and your time and effort. However, what you gave me didn't solve my problem.

I need to know what I need to type into the Criteria of the field BalanceOwing in my query.

If you have the time, I really would appreciate your re-creating my simple SAMPLE database as described about 2 or 3 threads above with 1 table and 1 query (Query2 only -the other queries were just my attempts at getting it right).

The data I had in Table1 was as follows

ID: 1
Person: Ann
Previous: R1600.00
Current: R200.00
Paid: R100.00

ID: 2
Person: Barbara
Previous: R1500.00
Current: R500.00
Paid: R0.00

ID: 3
Person: Mary
Previous: R400.00
Current: R200.00
Paid: R0.00

ID: 4
Person: Jane
Previous: R0.00
Current: R200.00
Paid: R0.00

ID: 5
Person: Martha
Previous: R600.00
Current: R400.00
Paid: R0.00

ID: 6
Person: Claire
Previous: R650.00
Current: R200.00
Paid: R500.00

Thus you will see with the query that Barbara has a BalanceOwing of R2000.00 () and Jane has a BalanceOwing of R200.00

When I put the following into the criteria of BalanceOwing:

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

and enter into the parameter prompt 200 as the minimum and 400 as the maximum, I get R2000.00 coming up as well. This is not right.

I need to know how to change the criteria in the parameter prompt so that R2000.00 doesn't come up when I select 200 to 400. This is happening because it is a calculated field. The same criteria functions perfectly on non-calculated fields.

Thank you in anticipation for any further help.

[Rainlover]

Rainlover, did you mean Maximum in second bracketed duplication instead of Minimum?

I did get them back to front but should not matter.

Try getting rid of all formatting and try again.

I am otherwise at a lost as to why it does not work as I tested it and it works for me.

[orange]

Owl,
I have created your table tblOwl see Owl6.jpg

The revised query see Owl7.jpg It is based on the Parameter queries I described in previous posts.

See Owl8.jpg which is the result of just hitting Enter on the parameter prompts.

Owl9_210-1100.jpg is the result for EnterMin as 210 and EnterMax as 1100

Good luck.

My sample uses $ (dollars) because of local settings;, just as you have R(rand) ... I think.

[Rainlover]

In order to post your DB, create a new blank DB and import the Table and Query in question.

Convert it to 2003 for people like me who don't have 2007.

Then you should be able to zip and post.

[Owl]

Thank you Rainlover and Orange. I tried Orange's last suggestion to the letter except for adapting it to my fields and it is still doing it, except that it is worse now because 200 to 400 brought up Barbara (R2000.00) and Jane (R200.00) instead of Jane (R200.00), Claire (R350.00) and Martha(R400.00) and it should not have brought up Barabara (R2000.00) at all.

The reason why neither of you are experiencing an error is that with June7's and Rainlover's (where it almost worked) it seems to be reading R2000.00 as R200.00 and Orange, you didn't have a R2000.00 and a R200.00 and didn't ask for 200 to 400, and presumably neither did you, Rainlover.

It seems to be reading R2000 as text and including it because R200 is thus included in R2000. This isn't happening on non-calculated fields.

This is why I asked you, if you have the time, to recreate my sample database with those figures in it.

I am completely flummoxed.

Thank you for any further help.