Inserting Dashes in Phone Number field

[GriffyNJ]

Ok Friends here's my new problem:

I have an access Table in which I have phone numbers without dashes in a particular field. I need to export this Table into a fixed width text file WITH dashes in the phone number. The problem is I when I put a input mask for dashes on the table, in design view in shows dashes, but upon export it doesn't. My table was built through a series of APPEND queries. What can I do either to my queries, or to the actual table, that will allow for me to export the phone numbers with dashes????

Thanks in advance for your help I appreciate you guys a great deal

 

[georgedwilkinson]

In general:

select '(' & left(MyPhoneField,3) & ') ' & mid(MyPhoneField,4,3) & '-' & right(MyPhoneField,4)
from MyTable;

would work for US/Canadian phone numbers. This is the general syntax for any country, though.

 

[GriffyNJ]

here is the code I put in:

Expr5: ([select left([Facility_Phone],3) & '-' & Mid([Facility_Phone],4,3) & '-' & Right([Facility_Phone],4)

but its not working. I need to pull the first three area code, insert dash, next three, insert dash, then final four. I put this expression in my APPEND QUERY under the field name..then I tried it as a criteria..what am i doing wrong?

 

[GriffyNJ]

I tried different syntaxes and tried the FROM MY TABLE; at the end, still not workin

 

[georgedwilkinson]

I tried different syntaxes and tried the FROM MY TABLE, still now work

Now works or "not working"?

You shouldn't have the word "select" in a field (nor the opening brace). Please paste your append query's SQL here and somebody will take a look.

 

[GriffyNJ]

The meat and potatoes of the problem

INSERT INTO [new attempt] ( Last_name, First_name, Pt_id1, Pt_id2, SSN, Dob, Stated_Age, Current_Sex, Race_ind, [Home Address], City_name, State_cd, [Zip Code], Lab_name, Old_Form_id, [Facility Address], [Facillity Address 2], City, State, [Facility Zip], Facility_Contact, Facility_Phone, [Laboratory Name], [Laboratory Contact], [Laboratory Phone], Accession_no, CLIA_code, [Positive Elisa], [Positive Elisa Date], [Report Date], [System Report Date] )
SELECT PERSON_NAME.Last_name, PERSON_NAME.First_name, PERSON.Pt_id1, PERSON.Pt_id2, PERSON.SSN, PERSON.Dob, PERSON.Stated_Age, Switch([Current_Sex]="1","M",[Current_Sex]="2","F") AS Expr2, Switch([Race_w]="1","1",[Race_b]="1","2",[Hisp]="1","3",[Race_a]="1","4",[race_ind]="1","4",[race_i]="1","5",[race_u]="1","9") AS Expr1, PERSON_ADDRESS.Street_addr1, PERSON_ADDRESS.City_name, PERSON_ADDRESS.State_cd, PERSON_ADDRESS.Zip, LABORATORY.Lab_name, LAB_TEST.Old_Form_id, FACILITY.Street_addr1, FACILITY.Street_addr2, FACILITY.City, FACILITY.State, FACILITY.Zip, FACILITY.Facility_Contact, FACILITY.Facility_Phone, LABORATORY.Lab_name, LABORATORY.Lab_Contact, LABORATORY.Lab_Phone, PERSON.Accession_no, LABORATORY.CLIA_code, LAB_TEST.Test_Result, LAB_TEST.Test_dt, Now() AS Expr3, Now() AS Expr4
FROM (PERSON INNER JOIN (LABORATORY INNER JOIN (FACILITY INNER JOIN (PERSON_ADDRESS INNER JOIN LAB_TEST ON PERSON_ADDRESS.Person_id = LAB_TEST.Person_id) ON FACILITY.Facility_Phone = LAB_TEST.Facility_phone) ON LABORATORY.CLIA_code = LAB_TEST.CLIA_Code) ON (PERSON.Person_id = PERSON_ADDRESS.Person_id) AND (PERSON.Person_id = LAB_TEST.Person_id)) INNER JOIN PERSON_NAME ON PERSON.Person_id = PERSON_NAME.Person_id
WHERE (((FACILITY.Facility_Phone)=Left([Facility_Phone],3) & '-' & Mid([Facility_Phone],4,3) & '-' & Right([Facility_Phone],4)) AND ((LAB_TEST.Test_Result)="1") AND ((LAB_TEST.Test_Type)="EIA-1") AND ((LAB_TEST.Imported)="0" Or (LAB_TEST.Imported)="2"));


My problem is in the red area. i'm guessing I don't want to accomplish this task by inserting in my Criteria box. Basically, I need to take the field facility_phone from the table facility and make the raw data of 9738457272 look like 973-845-7272...

again, thanks for your help, I appreciate you guys a great deal

 

[georgedwilkinson]

I'm a little confused as to why you have this in your where clause. Take it out and put it in your select statement in place of "Facility_Phone".

Something like:

INSERT INTO [new attempt] [/COLOR]
[COLOR=#a0522d]( Last_name, First_name, Pt_id1, Pt_id2, SSN, Dob, Stated_Age, [/COLOR]
[COLOR=#a0522d]Current_Sex, Race_ind, [Home Address], City_name, [/COLOR]
[COLOR=#a0522d]State_cd, [Zip Code], Lab_name, Old_Form_id, [Facility Address], [/COLOR]
[COLOR=#a0522d][Facillity Address 2], City, State, [Facility Zip], Facility_Contact, [/COLOR]
[COLOR=#a0522d]Facility_Phone, [Laboratory Name], [Laboratory Contact], [/COLOR]
[COLOR=#a0522d][Laboratory Phone], Accession_no, CLIA_code, [Positive Elisa], [/COLOR]
[COLOR=#a0522d][Positive Elisa Date], [Report Date], [System Report Date] )[/COLOR]
[COLOR=#a0522d]SELECT [/COLOR]
[COLOR=#a0522d]PERSON_NAME.Last_name, PERSON_NAME.First_name, [/COLOR]
[COLOR=#a0522d]PERSON.Pt_id1, PERSON.Pt_id2, PERSON.SSN, PERSON.Dob, [/COLOR]
[COLOR=#a0522d]PERSON.Stated_Age, Switch([Current_Sex]="1","M",[Current_Sex]="2","F") AS Expr2, [/COLOR]
[COLOR=#a0522d]Switch([Race_w]="1","1",[Race_b]="1","2",[Hisp]="1","3",[Race_a]="1","4",[race_ind]="1","4",[race_i]="1","5",[race_u]="1","9") AS Expr1, [/COLOR]
[COLOR=#a0522d]PERSON_ADDRESS.Street_addr1, PERSON_ADDRESS.City_name, PERSON_ADDRESS.State_cd,[/COLOR][COLOR=#a0522d] [/COLOR]
[COLOR=#a0522d]PERSON_ADDRESS.Zip, LABORATORY.Lab_name, LAB_TEST.Old_Form_id, FACILITY.Street_addr1, [/COLOR]
[COLOR=#a0522d]FACILITY.Street_addr2, FACILITY.City, FACILITY.State, FACILITY.Zip, FACILITY.Facility_Contact, [/COLOR]
[COLOR=#a0522d][B][COLOR=#ff0000]Left([Facility_Phone],3) & '-' & Mid([Facility_Phone],4,3) & '-' & Right([Facility_Phone],4)[/COLOR][/B], [/COLOR]
[COLOR=#a0522d]LABORATORY.Lab_name, LABORATORY.Lab_Contact, LABORATORY.Lab_Phone, PERSON.Accession_no, [/COLOR]
[COLOR=#a0522d]LABORATORY.CLIA_code, LAB_TEST.Test_Result, LAB_TEST.Test_dt, Now() AS Expr3, Now() AS Expr4[/COLOR]
[COLOR=sienna]FROM [/COLOR]
[COLOR=sienna](PERSON INNER JOIN (LABORATORY INNER JOIN (FACILITY INNER JOIN [/COLOR]
[COLOR=sienna](PERSON_ADDRESS INNER JOIN LAB_TEST ON PERSON_ADDRESS.Person_id = LAB_TEST.Person_id) [/COLOR]
[COLOR=sienna]ON FACILITY.Facility_Phone = LAB_TEST.Facility_phone) ON LABORATORY.CLIA_code = LAB_TEST.CLIA_Code) [/COLOR]
[COLOR=sienna]ON (PERSON.Person_id = PERSON_ADDRESS.Person_id) AND (PERSON.Person_id = LAB_TEST.Person_id)) [/COLOR]
[COLOR=sienna]INNER JOIN PERSON_NAME ON PERSON.Person_id = PERSON_NAME.Person_id[/COLOR]
[COLOR=sienna][COLOR=red][B]WHERE [/B][/COLOR]((LAB_TEST.Test_Result)="1") [/COLOR]
[COLOR=sienna]AND ((LAB_TEST.Test_Type)="EIA-1") [/COLOR]
[COLOR=sienna]AND ((LAB_TEST.Imported)="0" [/COLOR]
[COLOR=sienna]Or (LAB_TEST.Imported)="2"));[/COLOR]

I'd like to point out that you are denormalizing an already overly de-normalized design when you do this. This is only recommended if you intend this to be a reporting only system.

HTH.