Finding the right numbers in a comment field with a function

[Joyce]

What function to use when looking for part of a string?

Hmm, I've been wondering how to explain this.

I have a column that is either filled through a query, but also with manual comments. So as you've guessed they don't always match in layout. The layout should be XXXX (building number) XX (date) (text) (invoice number). But when entered manually it's sometimes: (invoice number) (text) XXXX XX.

Now I need the date XX, which I can't get when using a right or left, cause they're not always in the same position.

I have 1 column with the XXXX building numbers and I somehow need to match these 4 XXXX in the other column and then go right 2 positions.

For example:

The second line is as it should be, so the date is 00.
The first line is entered wrongly, so it should look for 7112 and then give me 03.

Gebouw1 TransactieOmschrijving
7112 209909 NUON 711203, voorschot
7112 711200 Af030708240609 31072803

Does anyone have any ideas?

Regards,

Joyce

 

[JamesMcS]

I would say, unhelpfully, that you need to set up some rules about how data is entered. Try splitting the data up into fields, that way you can control what and how data is entered into the database.

 

[Joyce]

You are very right, unfortunately I've only been working here two months and we're talking about data starting from 2005 and its too much to correct right now, so I'm really hoping there is some solution.

 

[JamesMcS]

Unfortunately, not one I can see - I recommend finding the person who wrote the original DB and, well... use your imagination

How many records are we talking about here?

 

[Joyce]

Unfortunately, not one I can see - I recommend finding the person who wrote the original DB and, well... use your imagination

How many records are we talking about here?

about a 1000 for 2010 only

 

[JamesMcS]

I think you may have your work cut out then.... sorry! We might have been able to write some VBA but I can't see a pattern to the data.

 

[Joyce]

no logical pattern thanks to manual input.

all I can think of is that it should look for the XXXX and then count from there and display the 2 numbers after that.

 

[Brianwarnock]

Is the pattern always like this?

7112 209909 NUON 711203, voorschot
7112 711200 Af030708240609 31072803


ie the 2 characters that you require follow 4 characters that are the same as the first 4 characters?

Brian

 

[Joyce]

yes, the 4 digit buildingnumber is always followed by the 2 digit month number. its just that this string of 6 digits is not always at the beginning of the string.

the 4 digit buildingnumber is in a different column.

 

[Brianwarnock]

Then I think that this is a simple use of mid and instr something like

mystr = Mid(txtf1, InStr(txtf1, buildingnum) + 4, 2)

forgive me for not using your field names.

Brian

 

[Joyce]

 

[JamesMcS]

Ah well that's different, I didn't think there was a pattern? Hey ho Brian's on the case, all good!

 

[Brianwarnock]

created a little test this works

Function mystr(txtf1, buildingnum) As String
mystr = Mid(txtf1, InStr(txtf1, buildingnum) + 4, 2)
End Function

expr1:mystr(txtf1,buildingnum)

mystr is a string to retain leading 0 but maybe that is not what you want but the process is correct.

Brian

 

[Joyce]

I really appreciate all your help!

I have to fit this into a query and thusfar I have this:
test: InStr(1;[tbl_energierapportage]![TransactieOmschrijving];Left([gebouw];4))

which of course gives me the first position of where the string starts.

now I want something like a Mid test+3;2 and then I should be fine. I hope

 

[Brianwarnock]

Ok I didn't use your field names but I have given you the function and how to use it.

Brian

 

[Joyce]

Well, as you might have figured out by now I'm pretty much a noob.

But i did it!

My first own function.

Function berekenenperiode(omschrijving, gebouwnummer) As String

berekenenperiode = Mid(omschrijving, InStr(1, omschrijving, Left(gebouwnummer, 4)) + 4, 2)

End Function

Thanks for the help!