Part of a Value of a Field = Value of Another Field

[zashaikh]

Hello.

I want to write a code to pull data from a field I enter. I often do a very repetitive task, and it would be easier of I could somehow automate it. Basically, I want to copy and paste this:




2017090304356077601-001-0000, Found on 06/17/2017; At the port of ROCHESTER, NY;


Into a field. Then I want to press a button and have the date copied to another field and the city copied to another field.
Can I make access search through the field for "xx/xx/xxxx". And once it finds an instance of this, it will copy and paste it into a my [txtDateX] Field on the same form? Simiarlily, can I make it so that every word that comes after "at the port of" and before the next comma gets added to the [txtCityName]Field?

Im not sure if this is possible nor how easy such a thing would be. I know how to make the value of a field = the value of another field. But I dont know how to make PART of the value of a field equal the value of another field.

[Bulzie]

If your data is static meaning those semi colons will always be in those positions, date will always be 10 characters, etc. this might work. you might need to adjust the plus or minus values.

Here are some functions to use:

Instr or InstrRev functions to get the position of a character or group of characters, then add or subtract values depending on where the string is you need
Left, Mid, Right functions to get at the specific data in combination with the Instr, InstrRev


2017090304356077601-001-0000, Found on 06/17/2017; At the port of ROCHESTER, NY;


vDate = Mid([Yourfield], Instr([Yourfield], ";")-10,10)
vCityState = Mid([Yourfield], Instr([Yourfield], "port of ")+9,Instrrev([Yourfield], ";") - Instr([Yourfield], "port of ")+9)

[zashaikh]

Thank you! I will give this a shot. I image that this will go into my VBA and I need to set the Dims

[Bulzie]

yes if on a button would be on the OnClick of the button. Would be something like
Me.YourDateField = Mid([Yourfield], Instr([Yourfield], ";")-10,10)
me.YourCityStateField = Mid([Yourfield], Instr([Yourfield], "port of ")+9,Instrrev([Yourfield], ";") - Instr([Yourfield], "port of ")+9)

You can also put those in a query to test it out if you want, just use the table, add that field the value is in, then do
vDate: Mid([Yourfield], Instr([Yourfield], ";")-10,10)
and run the query, see if it does pick out the value you want from the initial field. If not, adjust the values in your function as needed.

[zashaikh]

Thank you so much! I didnt think itd work that easily. So It all works fine, except that had to change it to

Me.txtCity = Mid([txtInfo], InStr([txtInfo], "port of ") + 8, InStrRev([txtInfo], ";") - InStr([txtInfo], "port of ") + 9)

The "+8" accounts for "p-o-r-t- - o-f -". Correct? The InstrRev is not working right however.

ROCHESTER, NY;

Is being returned. I just want ROCHESTER. I was able to do this by using:

Me.txtCity = Mid([txtInfo], InStr([txtInfo], "port of ") + 8, InStrRev([txtInfo], "; ") - InStr([txtInfo], "port of ") + 18).

But the city will not always be that many characters long. It will however, always be right before a "; "

[June7]

There will always be state info at the end?

Me.txtCity = Mid([txtInfo], InStr([txtInfo], "port of ") + 8, Len(Mid([txtInfo], InStr([txtInfo], "port of ") + 8))-5)

[zashaikh]

That worked!! Thank you. That will work for all cases.

But if I may ask, what is the "+9" in InStr([txtInfo], "port of ") + 9

[June7]

See revised post 6.

[Bulzie]

This should get you the city regardless of the length: (if you have a variable length string you are trying to capture, then you need to always find the start and end of that space, not use a static number as in cases where the length of a value will never change like that date) This part finds the end of the city value - (InStrRev([txtInfo],";")-4) as it starts at the end of the string, finds that last ; then counts back 4.

Me.txtCity = Mid([txtInfo],InStr([txtInfo],"port of ")+8,(InStrRev([txtInfo],";")-4)-(InStr([txtInfo],"port of ")+8))

[zashaikh]

Thank you!. This worked very very well. I really appreciate the help. A lot.

I just have one last (i promise) question. You see, I use a Dlookup on my City Field in order to automatically get the state.

Private Sub txtCity_AfterUpdate()

If IsNull(DLookup("[City]", "tblCityState", "[City] = '" & Me!txtCity.Text & "'")) Then
Me.cmbPharmaInvolved.SetFocus
Else
Me.txtState.Value = Me.txtCity.Column(1)
End If

End Sub


However, now that I am Me.txtCity = Mid([txtInfo], InStr([txtInfo], "port of ") + 8, Len(Mid([txtInfo], InStr([txtInfo], "port of ") + 8))-5) in order to get my value for txtCity, txtCity no longer updates txtState.

I have tried using Me.txtCity.requery in the AfterUpdate() event for txtCity. yet this does nothing.

[June7]

txtCity is a combobox? I use txt as prefix for textbox name and cbx for combobox.

What is the RowSource for the combobox?

[zashaikh]

Yes. I usually use cbo. I wasnt thinking when I made this prefix. My Row Source is below

SELECT tblCityState.City, tblityState.[State/Province] FROM tblCityState;

[June7]

Looks like typo in post, missing C:

SELECT tblCityState.City, tblCityState.[State/Province] FROM tblCityState;

Could be simply:

SELECT City, [State/Province] FROM tblCityState;

Advise no spaces or punctuation/special characters (underscore only exception) in names. Better would be State_Province or StateOrProvince.

Populating the combobox value programmatically should still make the associated row in the list active and referencing columns should return correct data. Works for me.

What is the combobox LimtToList property set to?

So is ROCHESTER a new city that needs to be added to tblCityState?

What is the form RecordSource?

[zashaikh]

Sorry. That was a typo in the post, not the code.

And after I update txtInfo with " 2017090304356077601-001-0000, Found on 06/17/2017; At the port of ROCHESTER, NY;", the txtState still does not get filled in.

I see no option for "combo LimitToList". But it should be whatever the default is. And no. Rochestor does not need to be added to tblCityState. It is already there. I want my form to automatically fill in the state that Rochester is in.

The form recordsource is blank. This form does not update anything in the table. I use this form to quickly update information in another form.

[orange]

I'm not sure I follow your issue with City and State. Certainly there is a Rochester NY and a Rochester MN (I realize the latter is not a port). Just suggesting that City name is not unique to identify state. May not be relevant to your issue.

Perhaps you could tell us more about your CityState table.

?? Is the state abbreviation always positioned just before your terminating semicolon???