Change last,first name to first last name

[MM889]

I have 2 (hopefully) simple things i am trying to accomplish.

1.
I have a field which is formatted: "LAST_NAME, FIRST_NAME".

I would like to make the field be formatted: "FIRST_NAME LAST_NAME" (no comma just a space seperating them).

2.
I have several fields containing dollar values (8.22, 100.10, etc.) and i want to be able to create a column that is the sum of several columns minus the value of another column.

Ex: (deduct + copay + coinsure) - (allowed_amt) = NEW COLUMN

The data is already in a local table in access, not sure if it would be easier to use SQL or VBA to accomplish these tasks.

 

[Andrzejek]

2. If you have fields in your table called: deduct, copay, coinsure, and allowed_amt, you may just do:
[tt]
Select (deduct + copay + coinsure) - allowed_amt As Whatever
From tblMyTable[/tt]


Have fun.

---- Andy

 

[dhookom]

If all of your name values are consistent, you can use the Instr() function to find the location of the comma. Then use the Left() function to extract the last name and the Mid() function to extract the first name. Concatenate these together with a space between them to get first last.

I hope you can figure this out without someone providing the full solution. If you are in a hurry or struggling with the expression, let us know.

Regarding the 2nd question, do you expect to save the total (not a good idea) or just display it? Are all of the fields valued or 0? Did you try [blue](deduct + copay + coinsure) - (allowed_amt)[/blue]?

Can you share any table and field names?

Duane
Hook'D on Access
MS Access MVP

 

[randy700]

1. This is a perfect example of why the data should be in separate fields (i.e. FirstName and LastName).
With 2 fields, you can format either way, depending on your needs.

LastNameFirst = LastName & ", " & FirstName
FirstNameFirst = FirstName & " " & LastName

Randy