Update Query into a new column

[Sarah]

Column = Roll Width

Data looks like: 100.12 (and can range anywhere from 69.00 - 110.14)

I want the last two digits to be taken off and placed into their own
seperate column named "Sixteenths", and I want the decimal point to go
away so that the width would equal: 100 or 69

how would I write the criteria in an update query? Is that the best
way to do that?

 

[KARL DEWEY]

You have to add the new field in table design view.
Then run update AFTER BACKUP DATABASE.

Field: [Roll Width] [Sixteenths]
Update To: Int([Roll Width]) ([Roll Width] - Int([Roll Width])) * 100

 

[John Spencer]

UPDATE YourTable
SET [Roll Width] = Int([Roll Width])
, Sixteenths = Int(Right([Roll Width],2))



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Jeff Boyce]

Sarah

"How" depends on "what" ... even though your "data looks like: 100.12",
that's no guarantee what data type it is.

Is this field a numeric data type or text? You'd handle it differently,
depending...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Sarah]

Sarah

"How" depends on "what" ... even though your "data looks like: 100.12",
that's no guarantee what data type it is.

Is this field a numeric data type or text?  You'd handle it differently,
depending...

Regards

Jeff Boyce
Microsoft Office/Access MVP









- Show quoted text -

Non of the suggestions worked above.
Jeff Boyce - The data field is numeric. The data "Roll Width" can have
the following sixteenths: "00", "02", "04", "06", "08","10"

I have made it so that the Roll Width is formated so that it has 2
decimal places (or else the Roll width "88.00" would populate as "88",
"45.10" would be "45.1", etc.

I need a formula that would allow me to take the "00", "02", "04",
"06", "08","10" and put it into a seperate column.
Sixteenths: Int(Right([Roll Width],2)) produces "85" in the Sixteenths
column, where Roll Width = 85.00. Where Roll Width = "61.06" it
produces Sixteenths to = "6". I need it to look like "00" and "06".

Thanks for your help!!

 

[John Spencer]

To get the expressions you want try



WholeNumber: Int([Roll Width])
Sixteenths: Right(Format([RollWidth],"0.00"),2)


If sixteenths is a number field then you will need to apply a format to it to
make it show leading zeros. The format would be "00"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Sarah

"How" depends on "what" ... even though your "data looks like: 100.12",
that's no guarantee what data type it is.

Is this field a numeric data type or text? You'd handle it differently,
depending...

Regards

Jeff Boyce
Microsoft Office/Access MVP





- Show quoted text -

Non of the suggestions worked above.
Jeff Boyce - The data field is numeric. The data "Roll Width" can have
the following sixteenths: "00", "02", "04", "06", "08","10"

I have made it so that the Roll Width is formated so that it has 2
decimal places (or else the Roll width "88.00" would populate as "88",
"45.10" would be "45.1", etc.

I need a formula that would allow me to take the "00", "02", "04",
"06", "08","10" and put it into a seperate column.
Sixteenths: Int(Right([Roll Width],2)) produces "85" in the Sixteenths
column, where Roll Width = 85.00. Where Roll Width = "61.06" it
produces Sixteenths to = "6". I need it to look like "00" and "06".

Thanks for your help!!

 

[Jeff Boyce]

Sarah

Going back to your original post, with the example of "100.12", are you
saying that, even though that is being stored as a (decimal) number, it
REALLY means "100 inches, plus 12/16th of an inch"?

Regards

Jeff Boyce
Microsoft Access MVP

Sarah

"How" depends on "what" ... even though your "data looks like: 100.12",
that's no guarantee what data type it is.

Is this field a numeric data type or text? You'd handle it differently,
depending...

Regards

Jeff Boyce
Microsoft Office/Access MVP









- Show quoted text -

Non of the suggestions worked above.
Jeff Boyce - The data field is numeric. The data "Roll Width" can have
the following sixteenths: "00", "02", "04", "06", "08","10"

I have made it so that the Roll Width is formated so that it has 2
decimal places (or else the Roll width "88.00" would populate as "88",
"45.10" would be "45.1", etc.

I need a formula that would allow me to take the "00", "02", "04",
"06", "08","10" and put it into a seperate column.
Sixteenths: Int(Right([Roll Width],2)) produces "85" in the Sixteenths
column, where Roll Width = 85.00. Where Roll Width = "61.06" it
produces Sixteenths to = "6". I need it to look like "00" and "06".

Thanks for your help!!