Search in Query (1 Viewer)

rkisor

Registered User.
Local time
Today, 18:51
Joined
Mar 24, 2013
Messages
16
Hi Team,

I'm working on the special project in my office. In that i'm fetching the data from the excel sheet-Field Name "Location"

The catch is Location has the format example: SSSIndiaXXXXX and SSSPhilipinesXXXX. I need to separate the SSSIndia and SSSPhilipines in the field. Like that new locations will be adding on the monthly basis . How to do that . Kindly help me on this.


Thanks,
Kishore
 

rkisor

Registered User.
Local time
Today, 18:51
Joined
Mar 24, 2013
Messages
16
Hi Alex

XXX is the persons name, the length will change.

Below is the format

SSSCountryname-Personsname

SSSIndia-Kishore
SSSPhilipines-Rkishorekumar

I will be doing reports for different locations. So the country name gets changed.

I need to separate SSSConuntryname and Personsname separately.

Kindly help me

Kishore Kumar R
 

Cronk

Registered User.
Local time
Tomorrow, 00:21
Joined
Jul 4, 2013
Messages
2,770
One wonders why you would ever have data like that to start with.

The only distinguishing start/end of the country is that it starts at the fourth character in the string and ends with (hopefully always) the last lower case character following. Stiff though for a country with two names eg South Korea (although maybe use the space to continue searching.)

You would have maybe got more response posting in the modules forum.

Finally, if the data is in Excel , why are you looking at Access? Why not a module in Excel?
 

AlexHedley

Registered User.
Local time
Today, 13:21
Joined
Aug 28, 2012
Messages
171
Will it have the "-" separating the Countryname and Personsname or was that just to indicate the separation?

If it does you could use Find, Search, InStr or Split to get the two parts.

SSSCountryname-Personsname

SSSIndia-Kishore
SSSPhilipines-Rkishorekumar
 

rkisor

Registered User.
Local time
Today, 18:51
Joined
Mar 24, 2013
Messages
16
Hi Alex,
It's separating the Countryname and Personsname.

And one more question I'm using Access 2007 and Access 2010. Is it possible to separate.


Thanks,
Kishore Kumar.R
 

Geotch

Registered User.
Local time
Today, 08:21
Joined
Aug 16, 2012
Messages
154
Here's a solution with one query using various functions, Replace, InSt, Left Right, Mid. These are all great things to know for working with data like this. Picture of results and database uploaded.
querySolution.png
 

Attachments

  • UseReplaceFunction.accdb
    492 KB · Views: 55

Geotch

Registered User.
Local time
Today, 08:21
Joined
Aug 16, 2012
Messages
154
One wonders why you would ever have data like that to start with.

The only distinguishing start/end of the country is that it starts at the fourth character in the string and ends with (hopefully always) the last lower case character following. Stiff though for a country with two names eg South Korea (although maybe use the space to continue searching.)

You would have maybe got more response posting in the modules forum.

Finally, if the data is in Excel , why are you looking at Access? Why not a module in Excel?

I just posted a response and a solution for the two example. I just realized you'll need to adjust the iif statements for each new country. I guess you could create a cross reference table for all the countries, that way if a new country is added, just enter it in the new table for it to work.
 

rkisor

Registered User.
Local time
Today, 18:51
Joined
Mar 24, 2013
Messages
16
Hi team,
I tried in access 2010. Its not coming correctly. Can any one help on this.

Ex: sss - countryname- personname

I need to separate sss , countryname, personname and put it in each field.

Please help. I want use this access 2010
 

Geotch

Registered User.
Local time
Today, 08:21
Joined
Aug 16, 2012
Messages
154
Mine was in 2010 also and I attached a picture to show that it works. What does yours look like?
 

Cronk

Registered User.
Local time
Tomorrow, 00:21
Joined
Jul 4, 2013
Messages
2,770
Much easier not using Replace

Try the following SQL

SELECT tblExample.Field1, Left(Mid([Field1],4),InStr(Mid([field1],4),"-")-1) AS Country, Mid(Mid([Field1],4),InStr(Mid([field1],4),"-")+1) AS Person
FROM tblExample;

And test by replacing one of the country part by UK.

As long as there is consistent rules for delimiting data strings, they can be extracted.
 

rkisor

Registered User.
Local time
Today, 18:51
Joined
Mar 24, 2013
Messages
16
Hi Team,

Thank You So much. Its working fine.

Thanks,
Kishore
 

Users who are viewing this thread

Top Bottom