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

  • Thread starter Thread starter Frank Martin
  • Start date Start date
F

Frank Martin

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

Reply to group, rather than allenbrowne at mvps dot org.

Frank Martin said:
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?
Click to expand...
Click to expand...
 
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)
 
Back
Top