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

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.

--
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.

"Frank Martin" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>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?

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


"Allen Browne" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
> --
> 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.
>
> "Frank Martin" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>>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?
>
>

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)
"Frank Martin" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>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
>