Mixed Units of Measure

[mickmullen]

I have a field labeled U/M (unit of measure) that is mixed. Meaning it has "hr" ; "ft" and "ea". Thats the way my imported excel sheet is set up.


I'm trying to setup a control on a form to check a query, and total the quantities associated with each U/M


Any advice on how to express this?

 

[MajP]

can you provide a couple of examples in the table, and desired output? Usually you need another conversion table. I assume you have Hours, Feet, and Each?

 

[June7]

What do you mean by 'check a query' - is this query the form's RecordSource? If not, perhaps you need to use DSum() domain aggregate function. If you want the sum to output on each record and to be conditional on the U/M in that record, consider:

=DSum("[quantity]", "queryname", "[U/M]='" & [U/M] & "'")

 

[Galaxiom]

U/M is a terrible name for a field.

Always avoid including special characters in any object name.

 

[mickmullen]

can you provide a couple of examples in the table, and desired output? Usually you need another conversion table. I assume you have Hours, Feet, and Each?

Excel sheet is imported to table, One of the fields is U/M, containing LF (Lineal Feet, ea (each) or hr (hour).


The excel sheet also has other columns, Such as Products (product A, B,C) along with Rooms (Room 1, 2, 3), and QTY



I have a form with a combo box for Product and a combo box for Room. The query looks at these combo boxes for Criteria


There is text box on the form that DSums the QTY.



I suppose I need (3) total Text Fields on the form, That Dsums the QTY based on its associated U/M. I'm just not sure how to express that. Multiple queries?


PS- Galaxiom- I know its not ideal having the U/M Field mixed. I have considered making it a procedure to separate the U/M column on the excel sheet into 3 separate fields. But that would not be ideal, as the excel sheets will be imported quite often.

 

[June7]

=DSum("[quantity]", "queryname", "[U/M]='LF'")

Do you need the sum by product?

 

[mickmullen]

Holy Crap, Thanks Mr. Obvious


I was racking my brain with that for 2 hours!

 

[Galaxiom]

I know its not ideal having the U/M Field mixed. I have considered making it a procedure to separate the U/M column on the excel sheet into 3 separate fields. But that would not be ideal, as the excel sheets will be imported quite often.

Absolutely nothing wrong with having the units defined like that. Indeed it is preferable to separating into three columns.

My complaint is the choice of the field NAME. Special characters should be avoided.

 

[sxschech]

What Galaxiom means was for example:

Instead of field name called U/M
Rename the field to UM

Don't use special characters means the following should not normally be part of a field name. Not a full list, but gives you the idea:
?,/-()!@#

Also preferable not to use spaces or if you need a separator use the _ rather than -