find and display specific piece of text in a memo field

[J Kai]

I had to pull data from another system and place into access. one of the
fields is a memo field that contains a file number. The format of the file
number is ##-###.

Is there a way to find the text using wildcards *#-##* in a query and only
have the file number display in the result.

 

[Jerry Whittle]

Something like below, with the proper table and field names, will look for
the first - in the memo field and return the characters around it. If there
are any other dashes, such as in a phone number like 555-555-5555, all bets
are off.

SELECT Mid([MEMOFIELD], InStr([MEMOFIELD], "-")-2,6) AS TheFileNumber
FROM YourTable
WHERE InStr([MEMOFIELD],"-")>1;

 

[J Kai]

Jerry,

It was perfect. I kept thinking of finding the whole file number. Searching
for the dash was great.

J Kai

Something like below, with the proper table and field names, will look for
the first - in the memo field and return the characters around it. If there
are any other dashes, such as in a phone number like 555-555-5555, all bets
are off.

SELECT Mid([MEMOFIELD], InStr([MEMOFIELD], "-")-2,6) AS TheFileNumber
FROM YourTable
WHERE InStr([MEMOFIELD],"-")>1;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I had to pull data from another system and place into access. one of the
fields is a memo field that contains a file number. The format of the file
number is ##-###.

Is there a way to find the text using wildcards *#-##* in a query and only
have the file number display in the result.