Pulling out the first occurence of a four-digit number from a field

[Rydel]

I posted the question in SQL Server forum, but actually the question is about MS Access: Thread932239

Sorry for confusing the forums! And I hope someone can help me.



regards,
rydel n23

http://pravapis.org/

http://rydel.net/

 

[hneal98]

you can try using the instring function.

 

[dhookom]

I doubt instr() will work unless you know what the values will be. I expect a user created function will be required that loops through every character in the field until it finds four consecutive numeric digits.

Rydel,
Try write the pseudo code of what you would do manually. Come back with the pseudo code if you need help with actual VBA code.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Rydel]

dhookom: I can do it in VBA too (and if I don't get an answer here, that's what I'll do I guess). I just wanted to do it directly just by running _one_ SQL statement without having to write ~30 lines of VBA code.

regards,
rydel n23

http://pravapis.org/

http://rydel.net/

 

[hneal98]

dhookom, while you are correct that it would not work in most cases, I was assuming that there is some consistency in this respect. However, if there is not consistencey, your are totally correct.

 

[PHV]

Perhaps something like this ?
BirthYear: Mid([field name], InStr([field name], "19"), 4)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244