Need a way to copy numbers, maybe an update query? (1 Viewer)

david.paton

Registered User.
Local time
Today, 08:54
Joined
Jun 26, 2013
Messages
338
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

Wino Moderator
Staff member
Local time
Today, 08:54
Joined
Aug 30, 2003
Messages
36,118
If you can't just change the data type of the field, which I'd expect, an update would certainly work.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:54
Joined
Feb 28, 2001
Messages
26,999
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

Registered User.
Local time
Today, 08:54
Joined
Jun 26, 2013
Messages
338
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

Wino Moderator
Staff member
Local time
Today, 08:54
Joined
Aug 30, 2003
Messages
36,118
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

Super Moderator
Staff member
Local time
Today, 15:54
Joined
Sep 12, 2006
Messages
15,613
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

Registered User.
Local time
Today, 08:54
Joined
Jun 26, 2013
Messages
338
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.
 

Attachments

  • OrangeLegacyWidows.v3.07 to upload.accdb.zip
    191.5 KB · Views: 127
Last edited:

Tieval

Still Clueless
Local time
Today, 15:54
Joined
Jun 26, 2015
Messages
475
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.
 

Users who are viewing this thread

Top Bottom