Translate Excel Formula into Access Formula

[316854]

John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234 % 34.043019, -118.267256



I need help with parsing again.

To get only John Smith I use:
Left([fieldname], InStr([fieldname], "@") - 1)

To get only 34.043019, -118.267256 I use:
Trim(Mid([fieldname],InStr(Nz([fieldname],""),"%")+1))

To get only 1234 N Main Ave Los Angeles CA 90001-1234 I use what?

Thanks

[June7]

Gets complicated.
Will the @ and % characters always be present?

x represents the fieldname:

Mid(x, InStr(x,"@")+2, InStr(x,"%")-Instr(x,"@")-3)

[316854]

Thanks.

The string functions work as desired.

In a table I have:

John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
Mark Anderson @ 1234 N Main Ave Los Angeles CA 90001-1234
Daniel Williams @ 1234 N Main Ave Los Angeles CA 90001-1234

In that table, I'm going to add GPS coordinates to each record so it will look like this:

John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234 % 34.043019, -118.267256
Mark Anderson @ 1234 N Main Ave Los Angeles CA 90001-1234 % 34.043019, -118.267256
Daniel Williams @ 1234 N Main Ave Los Angeles CA 90001-1234 % 34.043019, -118.267256

I have a query that parses the strings into 3 fields, for example:

John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234 % 34.043019, -118.267256

John Smith

1234 N Main Ave Los Angeles CA 90001-1234

34.043019, -118.267256

How do I en masse update the query to replace the old records with the changed records?

[June7]

You are saving all this info into one field? Why? Or are you trying to split up imported data into separate fields?

[316854]

They are places that I drive to.

I use them to build a table of places that I drove to with the corresponding dates.

[June7]

Doesn't answer my questions.

Discrete data should not be bundled into same field in table. As you discovered, it is harder to parse string data than to concatenate.

[316854]

There could be multiple John Smiths.

John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 2345 N Main Ave Los Angeles CA 90001-1234
John Smith @ 3456 N Main Ave Los Angeles CA 90001-1234

Although I guess the gps coordinates will not change.

What's the best way to build this database?

[June7]

Yes, there can be multiple John Smiths. Determining whether or not the same person demands vigilant data management. People move. These could be the same person or different people.

What is this database for?

[316854]

Yes, sometimes multiple customers share the name John Smith, but sometimes a customer like John Smith has multiple addresses.

I document the places that I have driven to.

For example:

Specific Date:
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234

Specific Date:
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234

Specific Date:
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234

Specific Date:
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234
John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234

[June7]

Assuming there are repeat visits to customer/location, should have a table of Customers or Locations, unique record for each.

Then another table to document visits to customer or location. This table would have fields for CustomerID or LocationID and DateVisit.

Still doesn't answer if all this info is in one field.

[316854]

I used a misleading example.

Daily, I visit the people listed below.

I need to document the date of each visit, the order of the visits for each date, and who I visited for each date.

I need to match each customer to their corresponding address and gps coordinates.

I need to make a query that sorts who I visited by date from newest to oldest, and then by order from smallest to largest.

The query needs to show the correspoding addresses and gps coordinates of the customers.

Specific Date:
Smith
Johnson
Williams
Brown
Jones
Miller
Davis
Garcia
Hansen
Wilson
Martinez
Anderson
Taylor
Thomas

Specific Date:
Smith
Johnson
Williams
Brown
Jones
Miller
Davis
Garcia
Hansen
Wilson
Martinez
Anderson
Taylor
Thomas

Specific Date:
Smith
Johnson
Williams
Brown
Jones
Miller
Davis
Garcia
Hansen
Wilson
Martinez
Anderson
Taylor
Thomas


Thanks

[June7]

Doesn't change comments from my previous post.