Update Query to remove Text before "="

  • Thread starter Thread starter Jeff
  • Start date Start date
J

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.
 
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)
 
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],"="))
 
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
 
Jerry

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

jeff

Jerry Whittle said:
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.


Jeff said:
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.
Click to expand...
Click to expand...
 
Back
Top