Look up and replace data from one field into an expression field

[kplatt]

Hi, I am trying to do something pretty crazy. I have succeeded in excel to a point but I want to do this in access. Hopefully someone can help me. Take a look at the cell of information below and imagine that is a value in a field. The data that I am interested is the N078 and the N420. Each of these represents something different. I know this is bad, but this is how SAP spits out the information. I need to decipher it. My objective is to query a line such as below and pull out each N-number separated by a comma. Like this (N078, N420). Keep in mind there could be zero, 1, 2, or 3 N-numbers within the field matrix.

N078 N420 NESHAP_6H NESHAP_6W

Can I use a Lookup and replace type expression to extract the N numbers and have the placed into a new field separated by comma’s

Thanks

 

[Beetle]

A couple clarification questions;

1) Can there be other values in the string that do not begin with N, and if so do you want to ignore those values?

2) Do you want to store each N value in a separate field, or just a comma separated string in one field?

 

[kplatt]

1) Very good question. There could be a multiple array of letters and digits. For example another cut and paste from my table is below. So the answer to your question is yes.

192TX 3 A C F N N090 N982 O PM TAPII Y R

Let me caution you that there are also stand along "N" seperated by spaces in some arrays as you can see above. Therefore I was thinking the code would state something like "N#" in order to retreive the N-numbers that i am concerned with

2) Both if possible.

 

[Beetle]

Just to clarify;

2) Do you want to store each N value in a separate field, or just a comma separated string in one field?

What I meant (and should have said) by the first part in the above is do you want each value in a separate record (not field)

With that in mind, an Array would come in handy here. The following example splits the string and loops through the array creating a new comma delimited string of only the values you want. Then the new string is then inserted into a single record in a table;

Dim vArray As Variant
Dim i As Long
Dim sNewString As String
Dim sInsert As String

sNewString = ""

'Split the field into an Array using the space as the delimiter.
vArray = Split([[COLOR="Red"]TestField[/COLOR]], " ")

'Loop through the Array looking for values that
'begin with N and have a length greater than 1.
'Create a comma delimited string based on these values.
For i = 0 To UBound(vArray)
    If Left(vArray(i), 1) = "N" And Len(vArray(i)) > 1 Then
        If sNewString = "" Then
            sNewString = vArray(i)
        Else
            sNewString = sNewString & ", " & vArray(i)
        End If
    End If
Next

'Insert the new string into a table field.
sInsert = "Insert Into [COLOR="red"]YourTable[/COLOR]([COLOR="red"]YourField[/COLOR]) Values(""" & sNewString & """);"
CurrentDb.Execute sInsert, dbFailOnError

If you want each value in a separate record, then just insert each one as you proceed through the loop;

Dim vArray As Variant
Dim i As Long
Dim sInsert As String

'Split the field into an Array using the space as the delimiter.
vArray = Split([TestField], " ")

'Loop through the Array looking for values that
'begin with N and have a length greater than 1.
'Insert each value into a new record in the table as we go.
For i = 0 To UBound(vArray)
    If Left(vArray(i), 1) = "N" And Len(vArray(i)) > 1 Then
        sInsert = "Insert Into YourTable(YourField) Values(""" & vArray(i) & """);"
        CurrentDb.Execute sInsert, dbFailOnError
    End If
Next

 

[kplatt]

So what do i need to change in this code? Do i need to change anything or just cut and copy into the VB expression.

 

[kplatt]

For the first option cut from your text and pasted below, did you mean to say that this would put the N number in a seperate field seperated by commas? I ask because the second option explains that it will make a seperate record for each n-number also

"With that in mind, an Array would come in handy here. The following example splits the string and loops through the array creating a new comma delimited string of only the values you want. Then the new string is then inserted into a single record in a table;"

 

[kplatt]

It looks like the only thing that i have to change is the name of the field in the [Brackets]. Right

 

[Beetle]

For the first option cut from your text and pasted below, did you mean to say that this would put the N number in a seperate field seperated by commas? I ask because the second option explains that it will make a seperate record for each n-number also

"With that in mind, an Array would come in handy here. The following example splits the string and loops through the array creating a new comma delimited string of only the values you want. Then the new string is then inserted into a single record in a table;"

The first option creates a string of comma separated values and places that string in one field in a single row (record) of your table.

It looks like the only thing that i have to change is the name of the field in the [Brackets]. Right

You need to change the name of the field in the brackets as well as the name of the table and field in the append query. I edited my previous post to highlight the areas that would need modification (in red).

You would run this code from some form event, like the click event of a command button.

 

[kplatt]

Should the Your Field that you put in the code be in brackets? ie "YourTable(YourField)"

 

[kplatt]

I am sorry I am not the best at the visual basics aspects of access. I originally was thinking that i would put the code in VBA in a query which when ran would add the data to a field or a new record. I dont even know if that is possible, but I guess you are talking about actually making the change in the table which is fine also. So I put a command button "Command65" on a form and tried to paste the code in VBA. Do i need to add the

Private Sub Command65_Click()

and the

End Sub

on the outskirts of the code you wrote?

 

[Beetle]

Should the Your Field that you put in the code be in brackets? ie "YourTable(YourField)"

If you have spaces in your table or field names then you will need to enclose them in brackets.

Do i need to add the

Private Sub Command65_Click()

and the

End Sub

on the outskirts of the code you wrote?

If you opened the code module from the command buttons event tab then those lines should be there already.

 

[kplatt]

Hey, I appreciate all of your help, I am trying to put the code below in where [Cas Reg List] is where the existing data remains and I am trying to put the filtered/seperated values in the [ES Purchases][TestCodes1] Table. The command button is Command65 which is on a frontpage type form. I have the code as "on click" event command on the command button. Is the goal to just click the button and them open the table to see if it is automatically autopopulated. I did create the [TestCodes1] field in my table. Thanks

Private Sub Command65_Click()
Dim vArray As Variant
Dim i As Long
Dim sNewString As String
Dim sInsert As String
sNewString = ""
'Split the field into an Array using the space as the delimiter.
vArray = Split([Cas Reg List], " ")
'Loop through the Array looking for values that
'begin with N and have a length greater than 1.
'Create a comma delimited string based on these values.
For i = 0 To UBound(vArray)
If Left(vArray(i), 1) = "N" And Len(vArray(i)) > 1 Then
If sNewString = "" Then
sNewString = vArray(i)
Else
sNewString = sNewString & ", " & vArray(i)
End If
End If
Next
'Insert the new string into a table field.
sInsert = "Insert Into [ES Purchases][TRI Chemical Category Purchases] Values(""" & sNewString & """);"

End Sub

I seem to be getting a debugger on the red highlighted part of the above code. I feel it may be because there are some records in the field that could be zero length strings ( ie they contain null values). Any idea on how to fix this?

 

[Beetle]

The command button is Command65 which is on a frontpage type form.

What do you mean by a frontpage type form? Are you using Front Page as the UI with an Access database? If so, then all my previous advice is meaningless. I am not familiar with Front Page, but afaik it uses VBScript, not VBA. These are two different animals. VBScript probably has no built in Split function, which may explain why you're getting an error on that line.

 

[kplatt]

I just mean a regular form. forget i said frontpage.

 

[Beetle]

OK. So the code, as written, is only going to check the [Cas Reg List] field for the current record in your form. In other words, this is only going to work one record at a time (not sure if that's what you want or not). If the [Cas Reg List] field happens to be Null (or empty) for the current record, then you will get an Invalid Use of Null error trying to create the array. You could trap for this just before creating the array using the Nz function. See the highlighted new lines in the example below;

Private Sub Command65_Click()
Dim vArray As Variant
Dim i As Long
Dim sNewString As String
Dim sInsert As String

sNewString = ""

[COLOR="Red"]'Trap for Null
If Nz([Cas Reg List], vbNullString) <> vbNullString Then[/COLOR]

    'Split the field into an Array using the space as the delimiter.
    vArray = Split([Cas Reg List], " ")

    'Loop through the Array looking for values that
    'begin with N and have a length greater than 1.
    'Create a comma delimited string based on these values.
    For i = 0 To UBound(vArray)
        If Left(vArray(i), 1) = "N" And Len(vArray(i)) > 1 Then
            If sNewString = "" Then
                sNewString = vArray(i)
            Else
                sNewString = sNewString & ", " & vArray(i)
            End If
        End If
    Next
    'Insert the new string into a table field.
    sInsert = "Insert Into [ES Purchases][TRI Chemical Category Purchases] Values(""" & sNewString & """);"

[COLOR="red"]End If[/COLOR]

End Sub

 

[kplatt]

OK, it failed again and highlighted the "If Nz([Cas Reg List], vbNullString) <> vbNullString Then" in the code. The error said that id did not recognize the field. I noticed you stated that the record should be in a form. Currently my data is only in a table and the table is closed. Then i have a command button in an unbound form with the command button running the code. I dont know if the table, named [ES Purchases] needs to be open or not.

 

[Beetle]

That's not going to work. The example code was based on the assumption that it would be run from a bound form with the table (or a query thereof) as it's record set. You're going to need some type of record set to get at the original data in the table.

If you want to do this one record at a time, then I would create a bound form, based on the table in question, so that the user can navigate to the correct record and then run the update via the command button.

On the other hand, if you want to be able to just click a button and have it update every (or at least multiple) records in the underlying table all at once, then you can use a command button on an unbound form, but you'll need to open a record set in the code and loop through the record set.

 

[kplatt]

Ok if i wanted to use the unbound form and command button and loop through the records set. how would i do that. So the question is how do i open a record set in the code and loop through the record set. Thanks