Parameter query. I need only the numerical part of a parameter.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Frank Martin - 02 Dec 2005 04:24 GMT

I have to filter records from units of Kg, L, 5L, 5Kg, 10L, 10Kg, 200L,
200Kg etc.

But the numerical parts will be good enough, and shorten the list box.

How can I designate as paramers only the numerical part of the unit and
ignore the rest?

Please help, Frank

Reply to this Message

Allen Browne - 02 Dec 2005 04:37 GMT

In the WHERE clause of your query (SQL View on View menu), you could use:
Val([YourFieldName]) = [How many?]
where "YourFieldName" represents the name of your field, and "How many?" is
the name of the parameter.

But the best solution would be to use two different fields: one for Quantity
(Number type field), and one for the unit of measurement (Text type field,
probably linked to a lookup table of valid unit names.) Advantages:
- Simplicity
- Performance
- Integrity (meaningful units only)
- Sorting, comparsion, and range-checking will be correct.
- Ability to convert from one unit type to another (e.g. grams <-> kilos or
pounds.)
- Ability to specify a default unit of measurement for each product, or
pricings per unit of measure.

Signature

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.

>I have to filter records from units of Kg, L, 5L, 5Kg, 10L, 10Kg, 200L,
>200Kg etc.
[quoted text clipped - 3 lines]
> How can I designate as paramers only the numerical part of the unit and
> ignore the rest?

Reply to this Message

Frank Martin - 02 Dec 2005 05:15 GMT

Thank you. In the long term I will have to redesign the "tblProducts" so as
to split the value from the units, but it will be a daunting task since many
charts and queries use the existing arrangement.
Regards,Frank

> In the WHERE clause of your query (SQL View on View menu), you could use:
> Val([YourFieldName]) = [How many?]
[quoted text clipped - 21 lines]
>> How can I designate as paramers only the numerical part of the unit and
>> ignore the rest?

Reply to this Message

John Spencer - 02 Dec 2005 13:00 GMT

Use the Val function to get the number part

SELECT Distinct Val(Units) as UnitNum
FROM TheTable

In the query grid:
Field: UnitNum: Val(Units)

>I have to filter records from units of Kg, L, 5L, 5Kg, 10L, 10Kg, 200L,
>200Kg etc.
[quoted text clipped - 5 lines]
>
> Please help, Frank

Reply to this Message