Join 2 Fields (1 Viewer)

[USA_Chancer]

Hiya everyone 1st post. If this is already answered somewhere, please just point me there...I mange large amounts of data for different companies and use Access. I have to check a lot of the lists against State "Do Not Call Lists". Anyway the latest countrywide list is over 10,000,000 records. For the first time they have seperated the area code and the main number into 2 seperate fields and I need them to be in the same field. How do I do that please?? Any help is appreciated - thx

 

[casey]

If [Area Code] and [PhoneNumber] are fields already in your form's recordsource, then concatenate the two fields into one calculated field in a textbox on your form or in your query .

Ex.
add a textbox [Textbox1] to your form

set its controlsource=[Area Code] & " " & [PhoneNumber]

[Textbox1] will join the two fields and separate them w/ a " "(space).

You can also do the same thing by adding a field to your query.

HTH

 

[USA_Chancer]

Casey?

Hey Casey,

Thx for your help,

set its controlsource=[Area Code] & " " & [PhoneNumber]

I created a new field in the main table called [allphonenumber]

Then in the query I typed the above code in the criteria section for the [allphonenumber] field. It's not working, the query comes up blank. What code should I put in place of "set its control source=" thx man.

Chancer.

 

[casey]

Sorry for the misunderstanding

TextBox1 controlsource:
=[Area Code] & " " & [PhoneNumber]

Try that instead.

PS. As far as proper design, you shouldn't add a new field to the actual table when the data already exists in the table. If the two fields exist then reference them when you need to w/ a textbox or a query using the above statement.

 

[USA_Chancer]

But in a query?

But in a query how do I create TextBox 1?

Can i just run a 'make table' or 'update' query?

in the query for my new field added can i type something in the criterea are such as an expression that:

this field=[fieldname]&""&[fieldname], then run the query?

its not working right now but am i close?

the final table i have to run it on is huge! and i'm on a deadline, thx for your help


can we chat realtime, if u have time of course?

 

[casey]

Sorry, I only suggested adding a TextBox if you're working w/ a form.
It seems that you're using a query though (which is fine too). Add a field to the query like you suggested in your previous post and set it up just like the following:

allphonenumber:[Area Code] & " " & [PhoneNumber]

This assumes that [Area Code] and [PhoneNumber] are fields in the query as well.

If not, then you'll have problems.

If your query consists of more than one table then you may need to distinguish which table the fields are from.

 

[USA_Chancer]

Got It!! Thx

Thx for all your help.

I added that simple code in the query and it worked great!

I ran a 'create a new table' query. Then deleted the un-needed fields and then deleted the original table.