Solved

InStrRev

Posted on 2004-11-01
1,231 Views
Last Modified: 2012-08-13
Hi
I am trying to use the InStrRev function in a query and am getting the error message:
"Undefined function 'InStrRev' in expression."
I have a field in a table that contains student register marks as a continuous string.
eg. O///O//OO
where:
/ - attended
O - absent
From this string I am trying to find the first and last attendance mark.
To find the first I am using:
InStr(1,[REGT_Attendance_Pattern],"/",0)
which works fine.

To find the last I thought I would be able to use:
InStrRev([REGT_Attendance_Pattern],"/")
but, although I have followed the syntax advice in the help file it doesnot seem to work.

Any ideas
Cheers
M
0
Question by:mjwerner
    7 Comments
     
    LVL 8

    Expert Comment

    by:bramsquad
    use this

    InStr(1,[REGT_Attendance_Pattern],"/",-1)

    ~b
    0
     
    LVL 77

    Expert Comment

    by:peter57r
    Hello mjwerner,

    Your example works fine for me in a query in A2003.  What version of Access are you using?

    Pete
    0
     
    LVL 83
    What version of Access are you using? I've had similar problems (functions which I know are valid show up as errors, just like you are experiencing). Often this indicates corruption. Try typing this in the Immediate window (Ctrl + G will bring up the window):

    ?INstrrev("abcdefg","d")

    and see what gives. You may want to try and compile your project (Debug - Compile) and see if you receive any errors; also, a compact/repair would be advised.

    Before doing any of this, of course, make a copy of your database.
    0
     

    Author Comment

    by:mjwerner
    1. I am using Access2k

    2. InStr(1,[REGT_Attendance_Pattern],"/",-1) does not work.

    3. I am not using vba code, just a query expression.

    Cheers
    M
    0
     
    LVL 77

    Expert Comment

    by:peter57r
    It appears that in A2000 Instrrev is only implemented in VBA and not in the Access UI.
    So to get roound this you create your own function wrapper and use that instead:

    In a standard module:

    Public Function MyInStrRev(pString, pSearchFor)
    myinstrrev = InstrRev(pstring, psearchfor)
    End Function

    Pete


    0
     
    LVL 77

    Accepted Solution

    by:
    Not sure if I made it clear that  you can then use MyInstrRev() in the Access query grid.

    Pete
    0
     

    Author Comment

    by:mjwerner
    Nice one, works a treat.
    M
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    The Complete Ruby on Rails Developer Course

    Ruby on Rails is one of the most popular web development frameworks, and a useful tool used by both startups and more established companies to build strong graphic user interfaces, and responsive websites and apps.

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    856 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now