get last digits in field using query

  • Thread starter Thread starter mwm
  • Start date Start date
M

mwm

Hello all,

I am trying to write an expression for a calculated field in an Access
2000 query.

Here is an example of the field data:
Microsoft Outlook - Memo Style owned by USERNAME was printed on
Assessing Department Lasertjet 4 via port IP_10.2.1.5. Size in bytes:
51102; pages printed: 1

I want to place the pages printed in its own field. This could be a 1
2 3 or 4 (or more) digit number. The logical method seems to be read
the field from the right until a space is encountered, then take the
stuff between the space and the end of line.

Anyone ever done somehting like this in a quesry?

Thanks in advance
 
Dear wjq:

This may be a little easier:

Right(strA, InStr(StrReverse(strA), " ") - 1)

In the above, strA represents your memo field. The code finds the
last space in the string by position (this value is 2 for your
example) and subtracts 1 from that to find the number of digits in the
string for "pages printed:" Then it takes that many characters from
the end of the string.

If the memo field contains anything after the digits for pages
printed, you may have to accomodate for that. For example, I
sometimes see a newline after such messages, and this is recorded in
the message. So, watch for that!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
There is a function InStrRev() to do exactly what you want. However, there
is a bug that Query cannot use this function directly and you have to use a
little wrapper function.

In a Standard Module, create a function:

Public Function QInStrRev(strCheck As String, strMatch As String) As Long
QInStrRev=InStrRev(strCheck, strMatch)
End Function

Then in your Query use the calculated Field:

PrintedPages: CLng(Mid([YourField], QInStrRev([YourField], " ") + 1))

Beware that if you have any characters after the last digit, the above
expression can give an error.
 
Back
Top