Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

InStrRev

Posted on 2004-11-01
7
Medium Priority
?
1,240 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
Comment
Question by:mjwerner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 8

Expert Comment

by:bramsquad
ID: 12463161
use this

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

~b
0
 
LVL 77

Expert Comment

by:peter57r
ID: 12463232
Hello mjwerner,

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

Pete
0
 
LVL 85
ID: 12463236
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:mjwerner
ID: 12463365
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
ID: 12463829
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:
peter57r earned 400 total points
ID: 12463848
Not sure if I made it clear that  you can then use MyInstrRev() in the Access query grid.

Pete
0
 

Author Comment

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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

609 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