Remove unwanted contents of string 1

[air1access]

I have a memo field that I need to trim the date from the left and leave everything else out...
How can I get just "Thursday, December 11, 2014 8:27 PM" out and into a seperate field...?
"Thursday, December 11, 2014 8:27 PM" is the very fist part of the value in the memo field...

Any suggestions or examples..?

Thanks in advance..!!!!

Jw

 

[Andrzejek]

Is this what you have:
[tt]
MyMemoField[blue]
Thursday, December 11, 2014 8:27 PM[/blue] something more here
[blue]Friday, December 12, 2014 9:00 AM[/blue] more text goes here
[blue]Saturday, December 13, 2014 5:55 PM[/blue] some text goes here
[blue]Sunday, December 14, 2014 11:34 AM[/blue] more goes here
...
[/tt]
And you want the [blue]BLUE[/blue] part to go to [tt]MyDateField[/tt], and leave the rest?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[air1access]

Yes - I only want the blue part...

 

[MajP]

Does every record have the date in the exact same format?
dddd, MMMM dd, yyyy hh:mm AM/PM

Does any character follow AM/PM, such as a :.

If not then it would be pretty tough to find where to split it.
If multiple formats are used, then I think the only thing to do is read the string starting at the first character and then reading until isdate = true. Read until last character.

 

[air1access]

Same format in every record.. And after AM/PM there is a space...

 

[dhookom]

Since days of the week and months never end with "M", you should be able to use:

Left([MemoFieldNameHere], Instr([MemoFieldNameHere],"M "))

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

The way I see it is: the : is always 5 characters (including spaces) before the end of the data part. So:

Dim str As String
Dim intColPos As Integer
Dim strDatePart As String
Dim strTheRest As String

str = "Thursday, December 11, 2014 8:27 PM something more here"

intColPos = InStr(1, str, ":")

strDatePart = Left(str, intColPos + 5)
strTheRest = Mid(str, intColPos + 7)
[green]'or[/green]
strDatePart = Left(str, InStr(1, str, ":") + 5)
strTheRest = Mid(str, InStr(1, str, ":") + 7)

Which translates to something like:
[tt]
Update MyTable
Set MyDateField = Left(str, InStr(1, MyMemoField, ":") + 5),
MyMemoField = Mid(MyMemoField, InStr(1, MyMemoField, ":") + 7)
[/tt]

Update not tested, but it should be close.
Not the most 'elegant' way, but it is a start...


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Andrzejek]

Oops, it should be:

[pre]
Update MyTable
Set MyDateField = Left(MyMemoField, InStr(1, MyMemoField, ":") + 5),
MyMemoField = Mid(MyMemoField, InStr(1, MyMemoField, ":") + 7)
[/pre]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[randy700]

Why not simply:
MyMemoField = Trim(MyMemoField)



Randy

 

[air1access]

Thank you Andrzejek..!! I got it to work..!!!

 

[Andrzejek]

You are welcome air1access,
Did you have to do a lot of modifications to what I gave you?

randy700,
What is that supposed to do? Except from stripping spaces from the beginning and the end of the (memo) field.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[randy700]

How can I get just "Thursday, December 11, 2014 8:27 PM" out and into a seperate field

Same format in every record.. And after AM/PM there is a space

What is that supposed to do? Except from stripping spaces from the beginning and the end of the (memo) field.

Isn't that what was asked for?



Randy

 

[MajP]

I like Duane's suggestion, very eloquent. Using that

Public Function GetDatePart(Memo As Variant) As String
  If Not IsNull(Memo) Then
    GetDatePart = Left(Memo, InStr(Memo, "M "))
  End If
End Function
Public Function GetOtherPart(Memo As Variant, DatePart As String) As String
  If Not IsNull(Memo) Then
    GetOtherPart = Replace(Memo, DatePart, "")
  End If
End Function

In a query

SELECT Table1.memoFld, getDatePart([memoFld]) AS DatePart, getOtherPart([Memofld],[datePart]) AS MemoPart
FROM Table1

Results

[tt]
memoFld	                                                     DatePart	                                 MemoPart
Thursday, December 11, 2014 8:27 PM something more here	     Thursday, December 11, 2014 8:27 PM         something more here
Friday, December 12, 2014 9:00 AM more text goes here	     Friday, December 12, 2014 9:00 AM	         more text goes here
Saturday, December 13, 2014 5:55 PM some text goes here	     Saturday, December 13, 2014 5:55 PM         some text goes here
[/tt]

 

[dhookom]

You really don't need a user-defined function to implement my expression however a function does offer future flexibility. You can take MajP's function and parameterize the "What to search for" and the "before or after". Then you can use it like the following:

Public Function GetPart(Memo As Variant, strFind As String, strBA As String) As String
   [COLOR=#4E9A06]'strBA is either "B" for before or any other character for after[/color]
  If Not IsNull(Memo) Then
    If strBA = "B" Then
        GetPart = Left(Memo, InStr(Memo, "M "))
      Else
       GetPart = Mid(Memo, InStr(Memo, "M ") + Len(strFind))
    End If
  End If
End Function

Duane
Hook'D on Access
MS Access MVP