Look in text box and record selected item.

[CBONG]

Hi

I am trying to create a query which it record dates and reason from textbox.

From

Item Number	TextBox
1	29-Dec-2015 - AMY - Booking Date: 20-June-2016, Booking Reason: Business – Change Venue, 21-Dec-2015 - SAM - Booking Date: 20-May-2016, Booking Reason: Business – Offsite meeting.
2	01-Dec-2015 - AMY - Booking Date: 23-June-2016, Booking Reason: Business – Change Venue, 21-Dec-2015 - SAM - Booking Date: 20-May-2016, Booking Reason: Business – Offsite meeting.

<tbody>
</tbody>

To give this:
Booking Date (variable length Character but always end with “,”)
Booking Reason (variable length Character but always end with “–“)
If both not found, return: not available
Only look for latest recorded entry

Item Number	Booking Date	Booking Reason
1	20-June-2016	Business
2	23-June-2016	Business

<tbody>
</tbody>

This is how I tracked in Excel, please advise me how this work in access. ($AC is TextBox)
=IFERROR(MID($AC4,SEARCH("Booking Date:",$AC4)+24,11),"not available")

Many thanks!

 

[Micron]

What you want requires robust expressions or code, which I can probably figure out. However, the data structure needs to be reliably consistent for anyone to decide how to achieve it. This one: Booking Reason (variable length Character but always end with “–“) can be a problem. A hyphenated word will cause it to fail (e.g. co-worker). Other factors can be considered (such as if it will always be one word, a space can be used also).

Will there always be a space between the booking data and the colon?

 

[CBONG]

What you want requires robust expressions or code, which I can probably figure out. However, the data structure needs to be reliably consistent for anyone to decide how to achieve it. This one: Booking Reason (variable length Character but always end with “–“) can be a problem. A hyphenated word will cause it to fail (e.g. co-worker). Other factors can be considered (such as if it will always be one word, a space can be used also).

Will there always be a space between the booking data and the colon?

Thank you.

Data structure is always consistence.

Booking Date: 23-June-2016 (this is how it always be) and always end with a comma
Booking Reason: (reason - with variable length character) and always end with "-")

No Space between the booking date and colon. its always writen as "Booking Date:" or "Booking Reason:", but space after the colon before capture the date/reason

The textbox is always repeated, however I just want to capture the last booking information.

Many thanks for you help.

 

[Micron]

This is what I came up with for extracting what you want. It works for me, but note this: I spent about an hour trying to figure out why it didn't work when it was looking for a hyphen (-). In desperation, I actually copied the hyphen from the source string and pasted into the code part that I wrote and magically, it performed as expected. I'm baffled by this, and can only think there is a difference between them that I cannot see. I have a few options to choose from when it comes to comparing characters (e.g. binary or text) and none of them made any difference. My source was this forum post, yours will be your database - not sure where the issue lies.

First, the code that you will have to call somehow (button click?). Don't know if you know how to call the function and assign the result to something. If not, I include examples at the end of this post.

Function ParseString(str As String, strLimiter As String, BookInfo As String) As String
Dim strParsed As String
Dim ParseStart As Long, ParseEnd As Long, ParseLen As Long
Dim cnt As Integer, intTrim As Integer

-set to zero; prevents incrementing numbers when testing
ParseStart = 0
ParseEnd = 0
ParseLen = 0

If BookInfo = "Booking Reason: " Then
    cnt = 15 'the length of the string 'Booking Reason:'
    intTrim = 1
Else
    cnt = 14
    intTrim = 0
End If

ParseStart = InStr(1, str, BookInfo) + cnt 'find the start of reason or date
ParseEnd = InStr(ParseStart, str, strLimiter) - intTrim 'find the end of reason or date
ParseLen = ParseEnd - ParseStart 'calculate length
strParsed = Mid(str, ParseStart, ParseLen) 'get n characters from reason/date start point
ParseString = strParsed
End Function

These two subs pass the string, the character used to locate them (- or ,) and the text that comes before the part you want to extract. You might change str to get the string from a textbox; (str = Forms!YourFormName.YourControlName)

Private Sub BookReason()
Dim str As String
str = "29-Dec-2015 - AMY - Booking Date: 20-June-2016, Booking Reason: Business – Change Venue, 21-Dec-2015 - SAM - Booking Date: 20-May-2016, Booking Reason: Business – Offsite meeting."
ParseString str, "–", "Booking Reason: "
End Sub

Private Sub BookDate()
Dim str As String
str = "29-Dec-2015 - AMY - Booking Date: 20-June-2016, Booking Reason: Business – Change Venue, 21-Dec-2015 - SAM - Booking Date: 20-May-2016, Booking Reason: Business – Offsite meeting."
ParseString str, ",", "Booking Date: "
End Sub

The above was tested without benefit of your db, but it works on mine. If anyone sees room for improvement, let me know.
First example of calling the function using command button cmdGetReason:

Private Sub cmdGetReason_Click()
Dim str As String
str = Me.NameOfControlWithLongString
Me.txtReason = ParseString str, "–", "Booking Reason: " 'assign function result to textbox
End Sub

You can also write this to get and assign both parts in one click operation.

Private Sub cmdGetInfo_Click()
Dim str As String
str = Me.NameOfControlWithLongString
Me.txtReason = ParseString str, "–", "Booking Reason: " 'assign function result to textbox
Me.txtDate = ParseString str, ",", "Booking Date: "
End Sub

Good luck!