Access Database help

Ok yall, figured you would be my best place for help.

I have somehow become the keeper of some databases with absolutely no formal training.

One of my databases has the main table where two fields formated as currency and need to be just regular numbers. As they are some 1.8 million rows, the simple format change options aren't working. I know that there is supposed to be some way to change this with an update query, but it is escaping me with massive google fu failure. There is also supposed to be a way with VBA, but I have absolutely no experience in that and what I find is confusing me

Treating me like a 3rd grader, can someone help me with fixing this?

Literally 1.8 million rows?

Easiest I can think of:

****Backup db****
Update the table, add new column [tmp] of type numeric double, default value NULL (you will be using this as a "failed to update" indicator)
Run query to update
UPDATE Table1 SET Table1.tmp = [your_money_field];
Validate all data was updated
SELECT * FROM Table1 WHERE tmp IS NULL
Drop old column, rename tmp to that name

Quote:

---

Originally Posted by Larommi

Literally 1.8 million rows?

---

Nah, only 1,964,488 rows

Quote:

---

Originally Posted by CeeBee

Easiest I can think of:

****Backup db****
Update the table, add new column [tmp] of type numeric double, default value NULL (you will be using this as a "failed to update" indicator)
Run query to update
UPDATE Table1 SET Table1.tmp = [your_money_field];
Validate all data was updated
SELECT * FROM Table1 WHERE tmp IS NULL
Drop old column, rename tmp to that name

---

Ok, the table is called Distribution Shipment Detail. Old Field is Gross Weight, new field is TMP.

Is it
Field: Gross Weight
Table: Distribution Shipment Detail

UPDATE: Distribution Shipment Detail SET Distribution Shipment Detail.TMP = [Gross Weight]

Put names with spaces within square brackets
UPDATE [Distribution Shipment Detail] SET [Distribution Shipment Detail].TMP = [Gross Weight]

Quote:

---

Originally Posted by CeeBee

Put names with spaces within square brackets
UPDATE [Distribution Shipment Detail] SET [Distribution Shipment Detail].TMP = [Gross Weight]

---

Damn, you expect a lot out of a 3rd grader, guess I should have said I wasn't better than a pre-schooler ;)
Didn't realize you were giving me SQL, worked like a charm.

If I can be greedy/needy/wanting/bothersome. What would we change to make it a number rather than text?

Oh, and thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Quote:

---

Originally Posted by Cleetus

What would we change to make it a number rather than text?

---

If the destination field is numeric then it will be a number. If there are values that cannot be converted I'd probably do something like
UPDATE [mytable] SET [mytable].[mynumcolumn] = Cdbl(mytxtcolumn) WHERE IsNumeric(mytextcolumn)=true
That will explicitly convert to a double type where the text field can be interpreted as number. See which values don't convert
SELECT * FROM [mytable] WHERE IsNumeric(mytextcolumn)=false
As before check for failed conversions looking for null just in case...

I hate Access. It uses a proprietary SQL wannabe implementation. Everything is much easier in plain SQL.

Was driving home and realized where I went wrong with making the field a numeric one. Derp. Thanks again. Now off to get my Excel/PowerPivot updated to 64bit now that this table is over 2.1 million rows as of this morning.

Yeah, our whole team has screamed ourselves horse telling them that Access is not the solution that they are looking for. Maybe if oil prices go back up we will get some money to do this correctly.

Quote:

---

Originally Posted by Cleetus

Was driving home and realized where I went wrong with making the field a numeric one. Derp. Thanks again. Now off to get my Excel/PowerPivot updated to 64bit now that this table is over 2.1 million rows as of this morning.

Yeah, our whole team has screamed ourselves horse telling them that Access is not the solution that they are looking for. Maybe if oil prices go back up we will get some money to do this correctly.

---

Maybe, but my experience has been that screwed up, inadequate, and clumsy solutions tend to persist over time, and money is simply not the root of the problem. An astounding number of people just can't handle change at all. I still have clients running DOS based applications that only run on current operating systems courtesy of VMWare, so don't hold your breath.

Quote:

---

Originally Posted by slgrieb

Maybe, but my experience has been that screwed up, inadequate, and clumsy solutions tend to persist over time, and money is simply not the root of the problem.

---

Sometimes.
But being in the development field I see how many times management simply doesn't see or want to acknowledge that using old, bad solutions that have hacks over hacks over hacks and throwing money at fixing issues is significantly more expensive than re-writing from the scratch.