Split Text (1 Viewer)

Gismo

Registered User.
Local time
Today, 17:24
Joined
Jun 12, 2017
Messages
1,298
Hi all,

I am importing from excel
The part number field may have multiple part number
Then you open it in excel and wrap the text, you can see the part number in separate line

In access, it is one long string with no separators

Please could you advise how to have these part numbers separated by comma in a query
 

Minty

AWF VIP
Local time
Today, 15:24
Joined
Jul 26, 2013
Messages
10,355
Look at the text in notepad or similar, and you should see what the linebreak character is.

You can use Replace() to put in a "," wherever that linebreak character is.
 

Gismo

Registered User.
Local time
Today, 17:24
Joined
Jun 12, 2017
Messages
1,298
Look at the text in notepad or similar, and you should see what the linebreak character is.

You can use Replace() to put in a "," wherever that linebreak character is.
I was looking at Replace but not sure how to check for character 13

I believe it was created with carriage return in the string
 

Gismo

Registered User.
Local time
Today, 17:24
Joined
Jun 12, 2017
Messages
1,298
Hi all,

I am getting an error on my code and assume it could possible by because i am only looking at Chr10 and not including Chr13

So I replaced with below code
Replace(Replace([Aircraft Overview]![Part Number],Chr(10)," / "),Chr(10)," / ")

When the control is blank, I get an error calculation
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:24
Joined
Sep 21, 2011
Messages
14,056
You cannot replace anything if it is blank? :(
Test to see if the field has any data, before Replace?, though I just tried a replace on an empty variable, and did not get an error?
 

Minty

AWF VIP
Local time
Today, 15:24
Joined
Jul 26, 2013
Messages
10,355
Put an NZ around the part number
Replace(Replace(NZ([Aircraft Overview]![Part Number],""),Chr(10)," / "),Chr(10)," / ")

@Gasman - A string variable can't be null so that might have silently fixed the issue in your test?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:24
Joined
Feb 19, 2013
Messages
16,555
the second replace won’t do anything because the first one has already done it

might be better with

Replace(Replace([Aircraft Overview]![Part Number],Chr(10)," / "),Chr(13),"")
 

Gismo

Registered User.
Local time
Today, 17:24
Joined
Jun 12, 2017
Messages
1,298
I am having the same issue with below

Not sure where to add the NZ

DateDiff("m",Date(),[Aircraft Overview Remaining Calendar]![Remaining 2B]) & " " & [Aircraft Overview]![Unit2]
 

Minty

AWF VIP
Local time
Today, 15:24
Joined
Jul 26, 2013
Messages
10,355
What would you want as a default date if there wasn't one?
I'd so be getting rid of all those spaces, the square brackets drive me mad when typing SQL out.
 

Gismo

Registered User.
Local time
Today, 17:24
Joined
Jun 12, 2017
Messages
1,298
What would you want as a default date if there wasn't one?
I'd so be getting rid of all those spaces, the square brackets drive me mad when typing SQL out.
If no date then the result should be a blank
 

Users who are viewing this thread

Top Bottom