Access query to read from right until space...

[mwmm]

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

 

[lespaul]

are they all single digit numbers?

If so, then

Right(FIELDNAME, 1)

could be used, but if some of them are two digit numbers, that wouldn't work.

I'll give it some thought....

Leslie

 

[SgtJarrow]

The "simpliest" method for this is to create a function to start from the right of the field, keep track of characters, and walk backwards until it hits a space. Then take the temp variable and convert it to a integer. Pass this back to the query.

Create a moudle with the following:

Public Function FindPageCount(strString As String) As Integer

Dim strTemp As String
Dim i As Integer
strTemp = ""
i = Len(strString)
Do Until i = 0
If Mid(strString, i, 1) = " " Then Exit Do
strTemp = Mid(strString, i, 1) & strTemp
i = i - 1
Loop
FindPageCount = CInt(strTemp)

End Function

Then in the query, in the field you want to get the page count, use the following in the field line:

PageCount: =FindPageCount([fieldname])

and replace fieldname with the name of your field.

****************************
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[OhioSteve]

This can be done in Access SQL.

The Val([expr]) function converts expr from string to numeric data. Therefore, Val("17&quot=17. However, Val("A17&quot=0. If the string does not have an obvious equivalent in numbers, the function simply returns zero.

I would make four calculated fields. The first field would find the val() of the rightmost character. The second one would find the val() of the rightmost two characters. The third would find the val() of the rightmost three characters. The fourth field would return the greatest value from among the first three fields.

If it is a 1 digit #, the comparison will be something like: 7,0,0. If it is a two digit #, the comparison will be something like: 7, 17, 0. If it is a three digit number, the comparison will be something like 7,17,117.

Good luck!