Update Query into a new column

  • Thread starter Thread starter Sarah
  • Start date Start date
S

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

"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 -
Click to expand...

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!!
 
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 -
Click to expand...

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!!
Click to expand...
 
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 -
Click to expand...

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!!
 
Back
Top