uncombine string (1 Viewer)

dmacomber · Oct 16, 2019

Hi all,
New to this forum but i am looking at a way to uncombine strings.
I have this

[function_] & "-" & [TAG_] as [Tag]

I would like a process where it takes [TAG] and turns it into [function_] [Tag_]

The [Tag] will always be 2 digits for [function_]. Is there a way to write it where you can tell it to use the first 2 digits as [function_] and then the digits after the "-" use those for [TAG_].

Any help is appreciated and thank you in advance.

theDBguy · Oct 16, 2019

Hi. Welcome to AWF! Check out functions like Left(), Mid(), Right(), and IsNumeric().

The_Doc_Man · Oct 16, 2019

I'm not clear on something. Are you saying that what was stored as a single string was stored by concatenating something, and that now you want to undo that concatenation? And you really just want to get rid of the dash?

Our friend theDBguy pointed you to certain string management functions. But my question is, where were you planning to use this? I.e. in SQL or VBA code? Because if VBA is involved, a few more options become viable including the SPLIT() function.

If the string you are "uncombining" ALWAYS has that dash in the same place, then you can use a little bit of magic with the LEFT() function. The only trick will be whether TAG is always the same length. If so, then the RIGHT() function is useful. Otherwise, you would need to use the MID() function and take advantage of the fact that if you ask for more characters than actually exist in the MID's length argument, it will just give you as many characters as are available.

Just as a warning, you are using a name "TAG" that happens to also be a property of form controls. I checked and it is not a "Reserved word" - therefore my warning is simply to be careful. If it were a reserved word, I would have given you a stronger admonition.

dmacomber · Oct 16, 2019

I believe I will be using it in VB. We will be using two as an output. I have a table where TAG_ is a column and then I have a table where it has Function_ and TAG_. What i am looking to do is if i change one in one table the other needs to change. I have it figured out where the concatenating will show and change in the TAG_ only table. Now i want to change the Function_ and TAG_ in the other table if I change TAG_ in the TAG_ only table.

Basically if someone changes an item that resides in the TAG only table I need it to go back into the Function_ & Tag_ table and visa versa. This Database is linked to multiple things so we want it to be able to change anywhere it shows up.

This is a database created by a 3rd party program and I am just modifying it to suit our needs.

The two tables I am trying to make say the same thing represent entities that are different in different programs, but in essence are the same thing.
In one program the Entity is placed in Table 1, in another program it is placed in Table 2, but they are the same it is just how the programs store it.

The "tag" will always be 2 digits and then the "-" and there could be multiple digits after that, so it will not always be the same length unfortunately.

Hope i am explaining this correctly.

theDBguy · Oct 16, 2019

dmacomber said:
I believe I will be using it in VB. We will be using two as an output. I have a table where TAG_ is a column and then I have a table where it has Function_ and TAG_. What i am looking to do is if i change one in one table the other needs to change. I have it figured out where the concatenating will show and change in the TAG_ only table. Now i want to change the Function_ and TAG_ in the other table if I change TAG_ in the TAG_ only table.

Basically if someone changes an item that resides in the TAG only table I need it to go back into the Function_ & Tag_ table and visa versa. This Database is linked to multiple things so we want it to be able to change anywhere it shows up.

This is a database created by a 3rd party program and I am just modifying it to suit our needs.

The two tables I am trying to make say the same thing represent entities that are different in different programs, but in essence are the same thing.
In one program the Entity is placed in Table 1, in another program it is placed in Table 2, but they are the same it is just how the programs store it.

The "tag" will always be 2 digits and then the "-" and there could be multiple digits after that, so it will not always be the same length unfortunately.

Hope i am explaining this correctly.

Hi. One other function that might help you with this is the InStr() function.

dmacomber · Oct 16, 2019

Here is what i have so far.

Private Sub cmdPushToPID_Click()
Dim sqlStirng As String
Dim tableName As String
Dim pidIDNo As String

tableName = Form_frmPIDInfo.txtTableName
pidIDNo = txtPIDid
sqlString = "UPDATE [" & tableName & "] SET dbcode_ = '" & txtItemNo & "',shortdesc_ = '" & txtSDesc & "',longdesc_ = '" & txtLDesc & "',line_num_ = '" & txtLineNumber & "',TAG_ = '" & txtTAG & "' WHERE id_count_ LIKE '" & pidIDNo & "'"

DoCmd.RunSQL sqlString
Form_frmPIDInfo.Requery
cmdNext.SetFocus
CheckConsistency
End Sub

Private Sub cmdPushToPipe_Click()
Dim sqlStirng As String
Dim tableName As String

txtItemNo = Form_frmPIDInfo.txtItemNo
txtSDesc = Form_frmPIDInfo.txtShortDescription
txtLDesc = Form_frmPIDInfo.txtLongDescription
txtLineNumber = Form_frmPIDInfo.txtLineNo
txtTAG = Form_frmPIDInfo.txtTAG
cmdNext.SetFocus
CheckConsistency
End Sub

The issue is the cmdPushToPID

So when i click it adds the TAG "XV-001" to the TAG_. I need the XV portion to go to Function_ and the 001 go to the TAG_ but what i get is XV in the Function_ and XV-001 into the TAG_ so it comes out as XV-XV-001. I am using a form to show both outcomes.

I am sorry if I am not explain this correctly. Haven't used Access in over 15 years and trying to refresh my memory on things.

arnelgp · Oct 16, 2019

you can create a Data Macro that will make the changes to your on either table.
replaced the encircled on the attached picture with your table name.

do the same macro on both tables, using the Other table (encircled on the attached).

Pat Hartman · Oct 16, 2019

It would be a far better solution to actually fix the underlying design problem. You shouldn't be storing the concatenated value to begin with. If you don't store it, you don't have to break it apart. Just concatenate it in your queries if that is how you want to show it in reports. In forms, I would tend to keep the two fields separate to make them easier to update.

dmacomber · Oct 16, 2019

Pat Hartman said:
It would be a far better solution to actually fix the underlying design problem. You shouldn't be storing the concatenated value to begin with. If you don't store it, you don't have to break it apart. Just concatenate it in your queries if that is how you want to show it in reports. In forms, I would tend to keep the two fields separate to make them easier to update.

I would agree with this statement but seeing it is defaulted to use it this way i am trying to find a solution without having to rewrite the entire database.

Pat Hartman · Oct 16, 2019

You could end up spending more time looking for and testing a band aid than it would take to actually fix the problem.

Fixing it might be as easy as identifying all the queries that select the mushed column from the table and changing those queries to perform the concatenation on the fly. You should remove or rename the now unnecessary column because creating an alias that mimics the name of an actual column will cause an error.

Renaming anything can cause huge problems if you have the NameAutoCorrect feature enabled so start by disabling that feature and leave it disabled. You can reenable it if you actually want to use its functionality.

uncombine string (1 Viewer)

dmacomber

New member

theDBguy

I’m here to help

The_Doc_Man

Immoderate Moderator

dmacomber

New member

theDBguy

I’m here to help

dmacomber

New member

arnelgp

..forever waiting... waiting for jellybean!

Attachments

Pat Hartman

Super Moderator

dmacomber

New member

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread