Update Query to remove Text before "="

[Jeff]

I imported data from a .csv file
All of the fileds begin with the field name followed by an equal sign
firstname=jeff,phone=555-555-1234,[email protected]
I would like to create an update query to remove the equals sign and
everything before it for all fields.

 

[Jerry Whittle]

Backup database first.

Put something like this in the Update To row of an Update Query. Make sure
to use the correct field names.

Mid([TheFieldName],instr(1,[TheFieldName],"=")+1)

 

[BobT]

Use the following (change Field1 to your field name(s)) to remove everything
to the left of and including the equals sign for your "Update To" line:

Right$([Field1],Len([Field1])-InStr(1,[Field1],"="))

 

[Ken Sheridan]

Use the Instr function to identify the position of the = sign and the Mid
function to return the substring which follows it. Include the Nz function
to cater for Nulls:

UPDATE YourTable
SET
firstname = Mid(Nz(firstname,""),Instr(Nz(firstname,""),"=")+1),
phone = Mid(Nz(phone,""),Instr(Nz(phone,""),"=")+1),
email = Mid(Nz(email,""),Instr(Nz(email,""),"=")+1);

Ken Sheridan
Stafford, England

 

[Jeff]

Jerry

Thanks!
I have several fiedls in my DB
Is there a way to do this for all fields?

jeff

Backup database first.

Put something like this in the Update To row of an Update Query. Make sure
to use the correct field names.

Mid([TheFieldName],instr(1,[TheFieldName],"=")+1)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I imported data from a .csv file
All of the fileds begin with the field name followed by an equal sign
firstname=jeff,phone=555-555-1234,[email protected]
I would like to create an update query to remove the equals sign and
everything before it for all fields.