Need a way to copy numbers, maybe an update query?

david.paton · Apr 24, 2017

I have made a number field to record some numbers but it won't let me record zeros at the start of the number, such as 0457 945 673, as this is the format for mobile numbers in Australia. When I look at the number in a form they appear without the zero, so 457 945 673. I need to make the data type for the field to be text instead of number. At the moment the data type is number and I wanted to make a text field and copy all the numbers across to the text field from the number field. Is there a way to do this using an update query or something similar as I don't want to do it manually as I have between 100 and 200 numbers?

pbaldy · Apr 24, 2017

If you can't just change the data type of the field, which I'd expect, an update would certainly work.

The_Doc_Man · Apr 24, 2017

At worst, add a text field to your table. Then do the update to reformat it using a FORMAT function based on the old field. Then when the new field is right, remove the old field. Then rename the new field to the desired final name. (Like I said, that is the worst case scenario.)

david.paton · Apr 27, 2017

But how do you do this, what do I have to do to make an update query so it will copy the column contents to the new column?

pbaldy · Apr 27, 2017

I take it you can't just change the data type? In a test I could. If not:

UPDATE TableName SET NewField = OldField

gemma-the-husky · Apr 27, 2017

the thing is, a phone number is text, rather than a numeric number.

you can't have leading zeroes in a number, although you can display them.

eg, - how would you know 0033 12456 has one or two leading zeroes, if it was really a number? you can't distinguish between 3312456, 03312456 and 003312456, because they are all the same value.

and if you do store a long number, you will also have problems with numbers longer than 10 digits (2 billion or so), as vba long integers have a maximum of that value. most uk numbers are 11 digits now.

david.paton · Apr 27, 2017

Well I changed the data type to text using an update query and it is all good now, thanks everyone, but another problem has occurred. I decided I didn't need a field so I got rid of it but then I tried to run a form FRM_SearchLegatee but I just get the error message "Enter Parameter value, Legatee.LegateePostCode" which is the field I removed. I have tried to find it's link to that form but I couldn't. Why is it giving this error message?

I have attached my database.

Tieval · Apr 27, 2017

It is a field that needs removing from your qry_searchlegatee - your subform is based on a query of that query, although the subform doesn't use it the base query still looks for it.

Need a way to copy numbers, maybe an update query?

david.paton

Registered User.

pbaldy

Wino Moderator

The_Doc_Man

Immoderate Moderator

david.paton

Registered User.

pbaldy

Wino Moderator

gemma-the-husky

Super Moderator

david.paton

Registered User.

Attachments

Tieval

Still Clueless

Similar threads

Users who are viewing this thread