Criteria on Percentage calculation

[Gary W. Graley]

I'm using Access 2003, have a query and a calc field that is presented as a perentage

I want to make it so you can enter a value that is <= what you want to display
say you want to view all records that are 33% or less.

In the query if I enter <.33 it works and presents only those less than that

If I change it to be; < [Enter decimal value] then it doesn't present any records whatsoever.

Appreciate the help,
Thanks in advance,
G2

 

[vbaInet]

I'm using Access 2003, have a query and a calc field that is presented as a perentage

I want to make it so you can enter a value that is <= what you want to display
say you want to view all records that are 33% or less.

In the query if I enter <.33 it works and presents only those less than that

If I change it to be; < [Enter decimal value] then it doesn't present any records whatsoever.

Appreciate the help,
Thanks in advance,
G2

Try this:

="<." & [Enter decimal value]

 

[Brianwarnock]

Just put in the leading 0

0.3 not .3


Or add PARAMETERS [Enter decimal value] Decimal;
to your query - first line

Brian

 

[Gary W. Graley]

Thanks Brian, that seems to work, sorry VBA that didn't seem to work.

I did try to pre-empt the entry with a 0. but the . went away and that didn't work
but as long as I explain to the users what they need to enter, that'll be fine!
Much thanks,
G2

 

[Brianwarnock]

Thanks Brian, that seems to work, sorry VBA that didn't seem to work.

I did try to pre-empt the entry with a 0. but the . went away and that didn't work
but as long as I explain to the users what they need to enter, that'll be fine!
Much thanks,
G2

Use the parameter statement option then they can enter 0.3 or .3

In design view click query on the toolbar and select parameter from the dropdown, the rest is obvious.

Brian

 

[vbaInet]

Thanks Brian, that seems to work, sorry VBA that didn't seem to work.

I did try to pre-empt the entry with a 0. but the . went away and that didn't work
but as long as I explain to the users what they need to enter, that'll be fine!
Much thanks,
G2

A form would have been a much better choice for data entry. Then you would be able to restrict what search string they use or even provide the user with a list or combo box

 

[Gary W. Graley]

Haven't got the hang of that parameter dealybob yet, will read up on it as it
still doesn't want to make the jump to light speed.

VBA, the database is being used for reference only of our pricing, no entries will
be made, only selection of certain part numbers.

In this particular query, they wanted to be able to pull up only those with a margin that
is equal to or less than a value they would pick at that time.

But you brought up a good point, maybe a set level, by .05, would be a handy thing, I will check into that!

Thanks all !
G2

 

[Brianwarnock]

Using a form is the normal and best way to go if the DB is going out to users as the query/report is then fired off from a command button and the input can be controlled in all sorts of ways, plus information to the user can be displayed in a label.

Just in case you have never done it the reference in the criteria would be

Forms!formname!textboxorcomboname

If you do produce a report this can be included in the report header to show the criteria

Brian