Adding another number digit into my fax database.

[MaMister]

Hi all, I am happy with my fax database until my goverment decided to add another 6 infront of all the phone and fax number.

How can I change all the numbers together?

Example from : 1234567 to 61234567

Thanks.

 

[David Eagar]

Create an update query. where new value = 6+oldvalue

 

[MaMister]

Create an update query. where new value = 6+oldvalue

May I know where to input the 6+oldvalue?

 

[MaMister]

Create an update query. where new value = 6+oldvalue

I tried under design view -> criteria and input 6+000000 but it doesn't work.

 

[Moniker]

That's not criteria -- you are not searching for it. It's an update query, and it goes in the Update To field.

UPDATE TableWithFaxNumberYouWantToUpdate SET FaxFieldName = 6 & [FaxFieldName];

Make a new query, and drag in the table with the fax field you want to update. Change the query type to Update, and in the Update To, add 6 & [FaxFieldName], where FaxFieldName is the name of the field storing your fax number (the same field you dragged down).

 

[neileg]

Moniker is dead right, providing your fax and phone numbers are stored as text. If by some chance they are stored as numbers then you would need to use 60000000+OldValue.

 

[David Eagar]

D'oh - I learn yet one more thing - think of all permutations before giving answers, thanks Neil

 

[Moniker]

I just made a quick dummy table, put in a Long Integer field called fax, and made one entry: 1234567. I then went into the QBE, dragged that table in, and performed an update like I said to do (6 & [FaxFieldName]) and got the expected answer of 61234567. Therefore, I believe that will work for both text and numeric fields. If someone can prove that wrong, I'll be happy to retract this.

 

[Rabbie]

Because Moniker used a & to link the fields that forced them into text mode while the concatenation was taking place. So Moniker's solution was correct. Neileq's solution would also work. As someone's(Missinglinq) signature says "There is more than one way to skin a cat!"

 

[MaMister]

Thanks guys, for a simple user like me, the 6 & [FaxFieldName] works well.

One more question, I found out that there some fax numbers that are not key in correctly, some less 1 digit, some extra 1 digit.

How can I remove all these?

 

[neileg]

Use Len() in a calculated field in the query to find out the length of the field and add a criterion of <>7 to find the ones that are too long.

 

[MaMister]

Thanks.

Just curious, how can I ask for 7 digits and 8 digits to be displayed together?

I tried "=7 and 8" it didn't work out...

 

[neileg]

The length can't be 7 AND 8 at the same time. It can be 7 OR 8 or it can be IN(7,8)

 

[MaMister]

The length can't be 7 AND 8 at the same time. It can be 7 OR 8 or it can be IN(7,8)

Thanks. The In(7,8) works well.

One more question please.

I have another database from someone else, his input is 1234 5678 but mine is 12345678, how can I remove that spacing in the middle?

 

[Moniker]

Search this forum for NumbersOnly (one word). It's a function I wrote that removes everything but the numbers (including spaces) from a string.

Edit: It's in this post (Response #3).

You can use that in queries, etc. just like a built-in function (or any other custom function).

 

[MaMister]

Thanks.

I tried the wildcard *#* and it only display the alphabet, but why *?* doesn't display only numbers?

 

[MaMister]

I use the replace function instead and it works well. Thanks.

Another thing is that I am just trying out this wildcard # and ?.

I understand # means alphabet and ? means numbers. (Am I right?)

When I try a query the *#* works and only display the alphabet but the *?*didn't seems to

 

[neileg]

Doesn't seem right. # represents any single numeric digit but ? represents any single character. You shouldn't get alpha characters returned using #.