Divide field in table into two fields (1 Viewer)

[azhar2006]

Hello my creative friends
I have a table with a field. This field contains a number and a date at the same time. When designing the database, someone set this field and made it include two values, a number and a date. He did not realize that it was wrong. What I want is to separate this field into two fields, the first in the date and the second in the number. I hope for your help, comrades. Thanks in advance

 

[theDBguy]

Can you show us what the actual data look like?

 

[The_Doc_Man]

There are several ways to do this kind of thing, but what you will need for reliability in this venture is that something is predictable about the way this field was implemented originally. To have two elements like you mentioned, that has to be a text field because no other data type would allow that kind of mixing. So... in that text field, is there a reliable (predictable) character between the two parts? If so, you can use InStr to find the point of separation. Then you can use the LEFT(string, length) to take the left side of the string and the RIGHT( string, length) function to take the right side of the string.

Here is a different question: Can you change the table to have two fields where there used to be only one? Because if so, you can manually execute a field-split using an UPDATE query and then just assure that no one ever enters that combined field.

 

[azhar2006]

Can you show us what the actual data look like?

OK dear (theDBguy) in the table design are text . On display as shown in the attached photo

 

[azhar2006]

There are several ways to do this kind of thing, but what you will need for reliability in this venture is that something is predictable about the way this field was implemented originally. To have two elements like you mentioned, that has to be a text field because no other data type would allow that kind of mixing. So... in that text field, is there a reliable (predictable) character between the two parts? If so, you can use InStr to find the point of separation. Then you can use the LEFT(string, length) to take the left side of the string and the RIGHT( string, length) function to take the right side of the string.

Here is a different question: Can you change the table to have two fields where there used to be only one? Because if so, you can manually execute a field-split using an UPDATE query and then just assure that no one ever enters that combined field.

It doesn't matter who builds the table again by creating a query. The important thing is to separate that information in this field from each other. Then I can go ahead. The fact that this data in this field exceeds more than ten thousand records

 

[theDBguy]

OK dear (theDBguy) in the table design are text . On display as shown in the attached photo

If the pattern is consistent, it would be easy. Otherwise, you might be able to automate parts of it and then have to finish the rest manually.

For example, if the numeric part is always first, you could try to extract it using the following expression:

Val([FieldName])

 

[The_Doc_Man]

My point is that you CAN easily separate the fields if you have a way to recognize where the split can occur. BUT if you can prevent future users from building it incorrectly, then you can do a one-time data split for the data you have and then never have to bother with it again. But if this is going to be a recurring problem because the data comes from an external source on a recurring basis, we need to know that.

 

[azhar2006]

If the pattern is consistent, it would be easy. Otherwise, you might be able to automate parts of it and then have to finish the rest manually.

For example, if the numeric part is always first, you could try to extract it using the following expression:

Val([FieldName])

SELECT tbljasim.NumDateOrder, Val([NumDateOrder]) AS Expr1
FROM tbljasim;

How will the date come out with another column?

 

[theDBguy]

SELECT tbljasim.NumDateOrder, Val([NumDateOrder]) AS Expr1
FROM tbljasim;

How will the date come out with another column?

If the date part is always at the end preceded with a space, you could try something like this:

Mid([FieldName], InStrRev([FieldName], " ") + 1)

 

[CJ_London]

or if using vba, the split function

?split("699 in 17/12/2021", " in ")(0)
699
?split("699 in 17/12/2021", " in ")(1)
17/12/2021

 

[The_Doc_Man]

Suppose that the field in question uses this format: " #### mm/dd/yyyy hh:nnss " (where "nn" represents minutes and "##" represents the digits of an integer number). Suppose that the leading and trailing spaces MIGHT be there or might not, but that there is ALWAYS a space following the number and preceding the date. Let's call this MixedField just to have a name for it, and say it is in BigTable, just to have a place for it.

SELECT [A], [B], ... , 
    CLng( Left$( Trim$( [MixedField] ), InStr( 1, Trim$( [MixedField] ), " ") ) ) As TheNumber, 
    CDate( Right$( Trim$( [MixedField] ), Len( Trim$( [MixedField] ) ) - InStr( 1, Trim$( [MixedField] ), " " ) ) ) As TheDate, 
    [C], [D], .... <<other fields would go here>>
FROM BigTable ;

The above returns two fields: TheNumber and TheDate - where there was originally one field called MixedField.

What does it do? First it got rid of leading and trailing spaces (using Trim). Then it extracted the number (using Left and computing the length of that portion using Instr). Then did the same thing to get the right-hand side of the string (using LEN and INSTR). it converted the left-hand extraction to a LONG integer (via CLNG) and the right-hand extraction to a DATE field (using CDATE).

I was shooting from the hip, but this is one way this might be done. I do not doubt for even a moment that other ways might exist.

Here are links for the functions I used:

InStr function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

Left function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

Right function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

Len function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

LTrim, RTrim, and Trim functions (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

 

[arnelgp]

since your the embedded date on the field is in the format dd/mm/yyyy,
you create a function to convert it to mm/dd/yyyy and then apply the update
to your field:

if you have already created the two fields (1 numeric, long and 1 date field) to your table,
you can use Update query to update those new fields:

Update [yourTable] Set [newNumericField] = Val([theNumberDateField] & ""), [newDateField] = ddMM2MMdd([theNumberDateField])

copy and paste to a Module:

Public Function ddMM2MMdd(ByVal p As Variant)
    Dim v, t
    ddMM2MMdd = p
    If Len(p & "") = 0 Then
        Exit Function
    End If
    p = p & ""
    p = Mid$(p, InStrRev(p, " ") + 1)
    v = Split(p, "/")
    t = v(0):    v(0) = v(1):    v(1) = t
    ddMM2MMdd = CDate(Join(v, "/"))
End Function

 

[azhar2006]

If the date part is always at the end preceded with a space, you could try something like this:

Mid([FieldName], InStrRev([FieldName], " ") + 1)

good morning . Thank you very much my friend (theDBguy) The expression worked out great. With some errors and the reason for entering data into the table, they can be fixed manually. Thanks are also due to all.

 

[azhar2006]

since your the embedded date on the field is in the format dd/mm/yyyy,
you create a function to convert it to mm/dd/yyyy and then apply the update
to your field:

if you have already created the two fields (1 numeric, long and 1 date field) to your table,
you can use Update query to update those new fields:

Update [yourTable] Set [newNumericField] = Val([theNumberDateField] & ""), [newDateField] = ddMM2MMdd([theNumberDateField])

since your the embedded date on the field is in the format dd/mm/yyyy,
you create a function to convert it to mm/dd/yyyy and then apply the update
to your field:

if you have already created the two fields (1 numeric, long and 1 date field) to your table,
you can use Update query to update those new fields:

Update [yourTable] Set [newNumericField] = Val([theNumberDateField] & ""), [newDateField] = ddMM2MMdd([theNumberDateField])

copy and paste to a Module:

Public Function ddMM2MMdd(ByVal p As Variant)
    Dim v, t
    ddMM2MMdd = p
    If Len(p & "") = 0 Then
        Exit Function
    End If
    p = p & ""
    p = Mid$(p, InStrRev(p, " ") + 1)
    v = Split(p, "/")
    t = v(0):    v(0) = v(1):    v(1) = t
    ddMM2MMdd = CDate(Join(v, "/"))
End Function

copy and paste to a Module:

Public Function ddMM2MMdd(ByVal p As Variant)
    Dim v, t
    ddMM2MMdd = p
    If Len(p & "") = 0 Then
        Exit Function
    End If
    p = p & ""
    p = Mid$(p, InStrRev(p, " ") + 1)
    v = Split(p, "/")
    t = v(0):    v(0) = v(1):    v(1) = t
    ddMM2MMdd = CDate(Join(v, "/"))
End Function

Export the filtered in the query to database

Hello guys I have a crosstab query in which I use a combo box on a form to filter that query. How can I export what is filtered in the query to another database located elsewhere on the computer? Thanks to all . This is an example attached

www.access-programmers.co.uk

 

[theDBguy]

good morning . Thank you very much my friend (theDBguy) The expression worked out great. With some errors and the reason for entering data into the table, they can be fixed manually. Thanks are also due to all.

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[gemma-the-husky]

or if using vba, the split function

?split("699 in 17/12/2021", " in ")(0)
699
?split("699 in 17/12/2021", " in ")(1)
17/12/2021

I thought that was the easiest method, although I would have suggested splitting on the space. Note that you do need to be sure that every row is consistent.