trim query (1 Viewer)

luzz · Feb 12, 2018

Hi all, I would like to separate the following into 141/4L 3W 18 1/2I 1V
14 1/4Lx3Wx18 1/2Ix1V

I tried using left trim but it will not work as the number of character is inconsistent for each line of the data.

bob fitz · Feb 12, 2018

Take a look at the Replace() function: https://www.techonthenet.com/access/functions/string/replace.php

luzz · Feb 12, 2018

bob fitz said:
Take a look at the Replace() function: https://www.techonthenet.com/access/functions/string/replace.php

Thank you for your reply. May I ask how does replace function help me in this case? as I do not need to do any replacement.

andydlindsay · Feb 12, 2018

luzz said:
Thank you for your reply. May I ask how does replace function help me in this case? as I do not need to do any replacement.

Replacement should help you in this case. My understanding is that you want to turn this:
14 1/4Lx3Wx18 1/2Ix1V
Into this:
14 1/4L 3W 18 1/2I 1V

It seems to me that if you replace every instance of 'x' with an empty string (""), you would achieve the desired output.

luzz · Feb 12, 2018

andydlindsay said:
Replacement should help you in this case. My understanding is that you want to turn this:
14 1/4Lx3Wx18 1/2Ix1V
Into this:
14 1/4L 3W 18 1/2I 1V

It seems to me that if you replace every instance of 'x' with an empty string (""), you would achieve the desired output.

After separating14 1/4Lx3Wx18 1/2Ix1V
Into this:
14 1/4L 3W 18 1/2I 1V
I want to store 14 1/4L under one field name then 3W another field name,
18 1/2I another field name, 1V another field name

andydlindsay · Feb 12, 2018

luzz said:
After separating14 1/4Lx3Wx18 1/2Ix1V
Into this:
14 1/4L 3W 18 1/2I 1V
I want to store 14 1/4L under one field name then 3W another field name,
18 1/2I another field name, 1V another field name

Don't bother with Replace() then, use Split() instead.

Give it a delimiter of "x", and you'll get an array of 4 strings which can easily be stored into separate fields in your db.

luzz · Feb 12, 2018

andydlindsay said:
Don't bother with Replace() then, use Split() instead.

Give it a delimiter of "x", and you'll get an array of 4 strings which can easily be stored into separate fields in your db.

okay, I will try first. After trying i get this error "A string can be up to 2048 characters long, including opening and closing quotation marks."

I use this under the fabric Cuttable width criteria
split("[Fabric Cuttable Width]", "X","4')

andydlindsay · Feb 12, 2018

luzz said:
okay, I will try first. After trying i get this error "A string can be up to 2048 characters long, including opening and closing quotation marks."

I use this under the fabric Cuttable width criteria
split("[Fabric Cuttable Width]", "X","4')

It is a VBA function, so it can't be used directly in a SQL query. You can either process the string using Split() in VBA or you can create a custom function which uses Split() and, from what I've researched, you can use custom functions in SQL queries.

There's an excellent answer on Stack Overflow which outlines how the latter would be accomplished. I can't post links, so search for "Selecting middle section of many different types of strings".

luzz · Feb 12, 2018

andydlindsay said:
It is a VBA function, so it can't be used directly in a SQL query. You can either process the string using Split() in VBA or you can create a custom function which uses Split() and, from what I've researched, you can use custom functions in SQL queries.

There's an excellent answer on Stack Overflow which outlines how the latter would be accomplished. I can't post links, so search for "Selecting middle section of many different types of strings".

Thanks for great help! I managed to achieve what i need! Quick question, how can i use the customsplit that i have created in the module in the MS Access query to retrun the same result in the customsplit module?

Below is my code:

Code:

Public Function CustomSplit(ByVal pInput As String) As String
        CustomSplit = Split(pInput, "x")(1)
End Function

Public Function CustomSplit1(ByVal pInput As String) As String
        CustomSplit1 = Split(pInput, "x")(2)
End Function

Public Function CustomSplit2(ByVal pInput As String) As String
        CustomSplit2 = Split(pInput, "x")(3)
End Function

Public Function CustomSplit3(ByVal pInput As String) As String
        CustomSplit3 = Split(pInput, "L")(0)
End Function

andydlindsay · Feb 12, 2018

luzz said:
Thanks for great help! I managed to achieve what i need! Quick question, how can i use the customsplit that i have created in the module in the MS Access query to retrun the same result in the customsplit module?

Use them right in your queries:

Code:

SELECT CustomSplit ("onextwoxthree") AS SecondString;

luzz · Feb 12, 2018

andydlindsay said:
Use them right in your queries:

Code:

SELECT CustomSplit ("onextwoxthree") AS SecondString;

I think i need to replace the pInput as Fabric Cuttable Width so that it will not prompt me to enter fabric cuttable width each time i run the query. How can i do that?

Code:

Public Function CustomSplit(ByVal [B]pInput[/B] As String) As String
        CustomSplit = Split(pInput, "x")(1)
End Function

moke123 · Feb 12, 2018

if your starting with this

Code:

 14 1/4Lx3Wx18 1/2Ix1V

its not clear if your appending this or inserting into a new table
if in a new table with your Pk as a FK you can loop through your table and call this in each loop

Code:

public sub ParseThis(strIn as string,YourPKID as long)

Dim strSql as string
Dim strVar as variant

strVar = split(strIn,"x")

strSql = "Insert into YourTable(YourPK,F1,F2,F3,F4) Values("  & YourPKID  & ",'"  & strVar(0) &"','" & strVar(1) &"','" & strVar(2) &"','" & strVar(3) &"')" 
Currentdb.execute strSql,DbFailOnError

end sub

if you want to remove the letters you would use replace like
replace(strVar(0),"L","") in each of the value variables

edit:Obviously you need to use your own field names and table names in the above.

andydlindsay · Feb 12, 2018

luzz said:
I think i need to replace the pInput as Fabric Cuttable Width so that it will not prompt me to enter fabric cuttable width each time i run the query. How can i do that?

Did you try creating aliased fields in the query and using [FabricCuttableWidth] in each one?

Code:

SecondString: CustomSplit([FabricCuttableWidth])
ThirdString: CustomSplit1([FabricCuttableWidth])
FourthString: CustomSplit2([FabricCuttableWidth])
FirstString: CustomSplit3([FabricCuttableWidth])

luzz · Feb 13, 2018

andydlindsay said:
Did you try creating aliased fields in the query and using [FabricCuttableWidth] in each one?

Code:

SecondString: CustomSplit([FabricCuttableWidth]) ThirdString: CustomSplit1([FabricCuttableWidth]) FourthString: CustomSplit2([FabricCuttableWidth]) FirstString: CustomSplit3([FabricCuttableWidth])

Oops, I did not. I tried keying the code you wrote here into my query and encountered error.

andydlindsay · Feb 13, 2018

luzz said:
Oops, I did not. I tried keying the code you wrote here into my query and encountered error.

Put your code above in a module. (module code.jpg)

Create a query with aliased fields using each of the custom split functions. (query designer.jpg)

And your output should be parsed as you'd like it. (query results.jpg)

luzz · Feb 13, 2018

andydlindsay said:
Put your code above in a module. (module code.jpg)

Create a query with aliased fields using each of the custom split functions. (query designer.jpg)

And your output should be parsed as you'd like it. (query results.jpg)

It prompt me to key in the fabric cuttable width

andydlindsay · Feb 13, 2018

luzz said:
It prompt me to key in the fabric cuttable width

You have a spelling error. You want "Fabric" not "Fabirc".

moke123 · Feb 13, 2018

pmfji but You can probably re-use the same function by adding a second argument for the position within the array

Code:

Public Function CustomSplit(ByVal pInput As String,Pos as integer) As String
        CustomSplit = Split(pInput, "x")(Pos)
End Function

luzz · Feb 13, 2018

andydlindsay said:
You have a spelling error. You want "Fabric" not "Fabirc".

Oh!! Thank you!!

trim query (1 Viewer)

Registered User.

Attachments

AWF VIP

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

AWF VIP

Registered User.

Registered User.

Attachments

Registered User.

Attachments

Registered User.

Attachments

Registered User.

AWF VIP

Registered User.

Similar threads

Users who are viewing this thread