• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1102
  • Last Modified:

Access 2007 Error: "Undefined function '[InStr$]' in expression"

I am not a heavy Access user.  We have a database that I created that we use only once a year to track property owner's costs for our sidewalk program.  We have never had significant problems from year to year until this year.

In a query, we are extracting data from a propety owner database.  The owner data is in a field where the  last name, first name, and mi are all in one field.  In order to extract the last name and first name from this field we are using the [InStr] function to calculate the total number of characters in the field and then in additional fields; find the spaces so that we can pull out the appropriate data to populate letters to the property owners.

When we open the reports (with the letters to the property owners) we are getting this error:
"Undefined function '[InStr$]' in expression"

After doing some searching online, I found a blog that determined that this is a Visual Basic error and instrcuted me to:

-Open any code module, then select Tools | References from the menu bar. Examine all of the selected references.
-If any of the selected references have "MISSING:" in front of them, unselect them, and back out of the dialog. If you really need the reference(s) you just unselected (you can tell by doing a Compile All Modules), go back in and reselect them.
-If none have "MISSING:", select an additional reference at random, back out of the dialog, then go back in and unselect the reference you just added. If that doesn't solve the problem, try to unselect as many of the selected references as you can (Access may not let you unselect them all), back out of the dialog, then go back in and reselect the references you just unselected.

I folowed this procedure.  There were no "MISSING" references and I could only uncheck the "OLE Automation" and "Microsoft Active X Data Objects 2.7 Library" ("Visual Basic Application" and Microsoft Access 12.0 OLE Library" returned "Can't remove control or reference, in use").  This however did not solve my problem and it return the same [InStr] error.

I am kind of at the limit of what i can do comfortably and I am hoping that there is a solution for thsi problem.  I have to get these letters out as sson as possible.   Any help would be greatly appreciated!!

Doug Wenning
0
douglaswenning
Asked:
douglaswenning
  • 10
  • 6
  • 6
  • +1
1 Solution
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
It does sound like a missing reference issue.

Try compiling the database to see if it get any error.

I would also look at a back up copy to see if it works. If it does then check the reference in that database.

Have you always used Access 2007 for the database?

0
 
douglaswenningAuthor Commented:
TheHiTechCoach:

1). As I said I am not a power user on Access...what will compiling the database do to it and how do you compile it?  I am willing to try this but do not know the ramifications.

2). I tried a backup version on Access 2007 and on Access 2003 and still got the same error.

3).  No, this was created on Access 2003 (I think).  That was my original thought...that in opening in 2007 it messed something up but I tried opening in a older version of Access (see #2) and got the same error.

Thank you for you help.

Doug
0
 
Jeffrey CoachmanCommented:
1. You can do three basic things to make sure everything in the DB is functioning smoothly:
a. Compact/Repair the DB.  This makes sure that the internal db structure is sound.
Office Button-->Manage-->Compact Repair

b. Compile the code
This makes sure that any code can actually be executed
Press F11 to open the VB Editor, then click: Debug-->Compile

c. Decompile the DB:
For lack of a simpler explanations, this tears down the entire db, then rebuilds it
(It has been debated here if this is not really the same thing as creating a blank, new DB and importing all the objects, which might be easire...)
See here:
http://www.granite.ab.ca/access/decompile.htm

No real negative ramifications here, as long as you make a backup...
;-)

But I would check for any Missing references as THTC suggests:
Open the VB Editor and click: Tools--Refernces
Check for anything that says: "MISSING"

Then report back to us...


;-)

JeffCoachman
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
Nick67Commented:
'[InStr$]'  looks awfully strange.

the InStr function doens't have the $ or the []

Does the error message box have a 'debug' button.
If so, and you click it, can you post the line of code that turns yellow?
If one does turn yellow
0
 
douglaswenningAuthor Commented:
Jeff,

Thank you for your response.  I am not having any luck with this.  I followed your instructions and I am still getting the same error.

As I stated in the original email, there were no references "MISSING".  I tried unchecking them and then re-checking them (see original email) to no avail.

Any thoughts?

Thanks again,

Doug
0
 
Nick67Commented:
<In a query>

Can you post the SQL from the query that powers the problem report?
I can walk you through finding that and posting it if need be.
0
 
douglaswenningAuthor Commented:
See attached.  The problem occurs in the [InName] field.

Thanks,
Doug
PARAMETERS instr Text ( 255 );
SELECT qryAssessmentRpt.[Parcel Number], [Troy Parcel Data].MailName1, [Troy Parcel Data].MailAddress1, [Troy Parcel Data].MailAddress2, [LocationHouseNum] & " " & [LocationStreet] AS [Location Address], qryAssessmentRpt.ExtPrice, qryAssessmentRpt.[Code Description], qryAssessmentRpt.[Unit Price], qryAssessmentRpt.Quantity, qryAssessmentRpt.[Value 2], IIf(qryAssessmentRpt![Assessment Code] Like "ADM*" Or qryAssessmentRpt![Assessment Code] Like "TRE*",Null,qryAssessmentRpt!Quantity*qryAssessmentRpt![Value 2]) AS SquareFeet, [Enter Required Date of Completion as (00/00/00)] AS [Completion Date], [InStr](1,[Troy Parcel Data]![MailName1]," ") AS InName, Len([Troy Parcel Data]!MailName1) AS EOFName, Left$([Troy Parcel Data]!MailName1,[InName]-1) AS LastName, Mid$([Troy Parcel Data]!MailName1,[InName]+1,[EOFName]-[InName]) AS FirstName
FROM qryAssessmentRpt LEFT JOIN [Troy Parcel Data] ON qryAssessmentRpt.[Parcel Number] = [Troy Parcel Data].ParcelID
GROUP BY qryAssessmentRpt.[Parcel Number], [Troy Parcel Data].MailName1, [Troy Parcel Data].MailAddress1, [Troy Parcel Data].MailAddress2, [LocationHouseNum] & " " & [LocationStreet], qryAssessmentRpt.ExtPrice, qryAssessmentRpt.[Code Description], qryAssessmentRpt.[Unit Price], qryAssessmentRpt.Quantity, qryAssessmentRpt.[Value 2], IIf(qryAssessmentRpt![Assessment Code] Like "ADM*" Or qryAssessmentRpt![Assessment Code] Like "TRE*",Null,qryAssessmentRpt!Quantity*qryAssessmentRpt![Value 2]), [Enter Required Date of Completion as (00/00/00)], [InStr](1,[Troy Parcel Data]![MailName1]," "), Len([Troy Parcel Data]!MailName1), Left$([Troy Parcel Data]!MailName1,[InName]-1), Mid$([Troy Parcel Data]!MailName1,[InName]+1,[EOFName]-[InName])
ORDER BY qryAssessmentRpt.[Parcel Number];

Open in new window

0
 
Nick67Commented:
Have  a look here
http://bytes.com/topic/access/answers/832769-cstr-function-office-2007-a
The left$ and mid$ no longer play nice in Access 2007, after being deprecated a while back.

Try this as a new query first
PARAMETERS instr Text ( 255 );
SELECT qryAssessmentRpt.[Parcel Number], 
[Troy Parcel Data].MailName1, 
[Troy Parcel Data].MailAddress1, 
[Troy Parcel Data].MailAddress2, 
[LocationHouseNum] & " " & [LocationStreet] AS [Location Address], 
qryAssessmentRpt.ExtPrice, 
qryAssessmentRpt.[Code Description], 
qryAssessmentRpt.[Unit Price], 
qryAssessmentRpt.Quantity, 
qryAssessmentRpt.[Value 2], 
IIf(qryAssessmentRpt![Assessment Code] Like "ADM*" Or qryAssessmentRpt![Assessment Code] Like "TRE*",Null,qryAssessmentRpt!Quantity*qryAssessmentRpt![Value 2]) AS SquareFeet, 
[Enter Required Date of Completion as (00/00/00)] AS [Completion Date], 
[InStr](1,[Troy Parcel Data]![MailName1]," ") AS InName, 
Len([Troy Parcel Data]!MailName1) AS EOFName, 
Left([Troy Parcel Data]!MailName1,[InName]-1) AS LastName, 
Mid([Troy Parcel Data]!MailName1,[InName]+1,[EOFName]-[InName]) AS FirstName
FROM qryAssessmentRpt LEFT JOIN [Troy Parcel Data] ON qryAssessmentRpt.[Parcel Number] = [Troy Parcel Data].ParcelID
GROUP BY qryAssessmentRpt.[Parcel Number], 
[Troy Parcel Data].MailName1, 
[Troy Parcel Data].MailAddress1, 
[Troy Parcel Data].MailAddress2, 
[LocationHouseNum] & " " & [LocationStreet], 
qryAssessmentRpt.ExtPrice, 
qryAssessmentRpt.[Code Description], 
qryAssessmentRpt.[Unit Price], 
qryAssessmentRpt.Quantity, 
qryAssessmentRpt.[Value 2], 
IIf(qryAssessmentRpt![Assessment Code] Like "ADM*" Or qryAssessmentRpt![Assessment Code] Like "TRE*",Null,qryAssessmentRpt!Quantity*qryAssessmentRpt![Value 2]), 
[Enter Required Date of Completion as (00/00/00)], 
[InStr](1,[Troy Parcel Data]![MailName1]," "), 
Len([Troy Parcel Data]!MailName1), 
Left([Troy Parcel Data]!MailName1,[InName]-1), 
Mid([Troy Parcel Data]!MailName1,[InName]+1,[EOFName]-[InName])
ORDER BY qryAssessmentRpt.[Parcel Number];

Open in new window


The [] around InStr bother me, but <PARAMETERS instr Text ( 255 );>
make me think someone may have been naughty with naming conventions
0
 
Jeffrey CoachmanCommented:
douglaswenning,

Was that SQL ever working?
In your SQL you are referencing Instr like a Field:
 [InStr]

Instr is a "Function"
It is used to find the location of a character inside a string.
For example if you wanted to find the location of the "o" in the LastName field, you would use something like this:
Instr(Lastname,"o")
If the Last Name is Coachman, the Instr() function will return: 2
(Notice how here that Instr is NOT enclosed in square brackets as you have in your SQL...
Including the square brackets, as you have, will produce the error you are getting...)


It looks like you can simply deleted the square brackets surrounding the [Instr] then you should be OK...
ex:
InStr(1,[Troy Parcel Data]![MailName1]," ")
...in the SQL you posted

(I am not sure about the Parameter line though, ...so you can delete it as a test to see if this runs first)

Here is a Tip...
Instead of creating a Huge SQL statement and asking why it does not work...
Build it up step by step, ...each time adding more.
This way when it breaks, you know that the last thing you added was what caused it.
Make sense?

;-)

JeffCoachman




0
 
Nick67Commented:
@Jeff

He built it a long time ago, and it worked for years.
Now they upgraded to Access 2007 and it went BANG!
I asked him to post the SQL because I figured that's where the BANG is, not in VBA references

Nick
0
 
Jeffrey CoachmanCommented:
Again, the Square brackets seem to be the issue.

When I leave them in (In Access 2007) I get the exact error they are getting...
When I remove them, it all works fine.

I have never use this syntax (Square bracket around function names) so I will try this tonight to see if such a syntax would work in Acc03...

But I will admit that I don't exactly know what the PARAMETERS is trying to do there, ...
That is why I suggested removing it temporarily, just to see if the error still persists

;-)

Jeff
0
 
Nick67Commented:
@Jeff

I figured those [] were evil, but the Parameters thing made hesitant to say deep-six them.
Without the table structure, I can't put it in the Designer to have a look if that's just cruft from designing a complex query or not
Bare SQL syntax isn't my forte, but if you figure that Parameters and [] its evil too, then we'll have the author give 'er!

Try this Doug
SELECT qryAssessmentRpt.[Parcel Number], 
[Troy Parcel Data].MailName1, 
[Troy Parcel Data].MailAddress1, 
[Troy Parcel Data].MailAddress2, 
[LocationHouseNum] & " " & [LocationStreet] AS [Location Address], 
qryAssessmentRpt.ExtPrice, 
qryAssessmentRpt.[Code Description], 
qryAssessmentRpt.[Unit Price], 
qryAssessmentRpt.Quantity, 
qryAssessmentRpt.[Value 2], 
IIf(qryAssessmentRpt![Assessment Code] Like "ADM*" Or qryAssessmentRpt![Assessment Code] Like "TRE*",Null,qryAssessmentRpt!Quantity*qryAssessmentRpt![Value 2]) AS SquareFeet, 
[Enter Required Date of Completion as (00/00/00)] AS [Completion Date], 
InStr(1,[Troy Parcel Data]![MailName1]," ") AS InName, 
Len([Troy Parcel Data]!MailName1) AS EOFName, 
Left([Troy Parcel Data]!MailName1,[InName]-1) AS LastName, 
Mid([Troy Parcel Data]!MailName1,[InName]+1,[EOFName]-[InName]) AS FirstName
FROM qryAssessmentRpt LEFT JOIN [Troy Parcel Data] ON qryAssessmentRpt.[Parcel Number] = [Troy Parcel Data].ParcelID
GROUP BY qryAssessmentRpt.[Parcel Number], 
[Troy Parcel Data].MailName1, 
[Troy Parcel Data].MailAddress1, 
[Troy Parcel Data].MailAddress2, 
[LocationHouseNum] & " " & [LocationStreet], 
qryAssessmentRpt.ExtPrice, 
qryAssessmentRpt.[Code Description], 
qryAssessmentRpt.[Unit Price], 
qryAssessmentRpt.Quantity, 
qryAssessmentRpt.[Value 2], 
IIf(qryAssessmentRpt![Assessment Code] Like "ADM*" Or qryAssessmentRpt![Assessment Code] Like "TRE*",Null,qryAssessmentRpt!Quantity*qryAssessmentRpt![Value 2]), 
[Enter Required Date of Completion as (00/00/00)], 
InStr(1,[Troy Parcel Data]![MailName1]," "), 
Len([Troy Parcel Data]!MailName1), 
Left([Troy Parcel Data]!MailName1,[InName]-1), 
Mid([Troy Parcel Data]!MailName1,[InName]+1,[EOFName]-[InName])
ORDER BY qryAssessmentRpt.[Parcel Number];

Open in new window

@Nick67
0
 
douglaswenningAuthor Commented:
Nick and Jeff,

Yes, as Nick says, I built this in 2002 or 2003 and has worked very well but this year decided to act up on me.

I do not know much about the SQL coding but that is what Nick asked for.  This was all built as a query within Access.  I tried removing the wildcard "$" and the brackets, etc...depending on what combination...I would either get the same error or it would return the entire string (COACHMAN JEFF A) and then the following fields that built upon this field would contain ERROR#.

Thanks for your input!

Doug

0
 
Jeffrey CoachmanCommented:
< I tried removing the wildcard "$">
Then I am confused, there was no "$" in the SQL you posted...

But I, like Nick, would really need to see a sample database:

Sample database notes:
(13 steps)
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the database window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. Post explicit steps to replicate the issue.
13. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see the issue, and if applicable, also include a Graphical representation of the Exact results you are expecting.


JeffCoachman
0
 
Nick67Commented:
There were $
Look at the Left$ and Mid$ in his original SQL.

But, it's not playing nice.
Now,
To troubleshoot a query, you copy the SQL and paste it into a new query.
Now, tear out these 'built' columns one at a time till you find the bad one--if I'm not barking up the wrong tree

Start simple and build it back up
Start with this, does it return records or errors
SELECT qryAssessmentRpt.[Parcel Number], 
[Troy Parcel Data].MailName1, 
[Troy Parcel Data].MailAddress1, 
[Troy Parcel Data].MailAddress2, 
[LocationHouseNum] & " " & [LocationStreet] AS [Location Address], 
qryAssessmentRpt.ExtPrice, 
qryAssessmentRpt.[Code Description], 
qryAssessmentRpt.[Unit Price], 
qryAssessmentRpt.Quantity, 
qryAssessmentRpt.[Value 2], 
IIf(qryAssessmentRpt![Assessment Code] Like "ADM*" Or qryAssessmentRpt![Assessment Code] Like "TRE*",Null,qryAssessmentRpt!Quantity*qryAssessmentRpt![Value 2]) AS SquareFeet, 
[Enter Required Date of Completion as (00/00/00)] AS [Completion Date]

FROM qryAssessmentRpt LEFT JOIN [Troy Parcel Data] ON qryAssessmentRpt.[Parcel Number] = [Troy Parcel Data].ParcelID

ORDER BY qryAssessmentRpt.[Parcel Number];

Open in new window

0
 
Nick67Commented:
@Jeff

Did you see anything in the original SQL that required the use of the Group By clause?
I didn't see any counts or sums or the like

Nick67
0
 
Jeffrey CoachmanCommented:
...

Oh, I thought that you guys meant the $ in Instr$ as in the original post:
<Access 2007 Error: "Undefined function '[InStr$]' in expression">
0
 
Nick67Commented:
Hey Doug,

How did you fix it?

Nick
0
 
douglaswenningAuthor Commented:
I ended up having to rebuild the query because it would not let me save it without the brackets...but that ended up fixing the error message (removing the brackets and wildcard).  Thank you to everyone for their help.  I've never used this site before...how/who do I reward points to?

With sincere appreciation.

Doug
0
 
Nick67Commented:
You already did!
You accepted Jeff's post at ID 35741818 as the sole answer, and so the system gave him the points.
Other tactics can include posting up the code/solution that worked, and making it the solution
You can't give yourself points, so the system then wants you to pick assisting posts that helped you get there.

There's stuff in the help tab about how to finalize your questions

Nick67
0
 
douglaswenningAuthor Commented:
I kinda feel bad that it couldn't be given to both of you.  You were both very helpful!!

Thanks
Doug
0
 
Nick67Commented:
It could have been split, but it's a done deal now, and not a big deal in any case.
Glad you got it cased!
0
 
Jeffrey CoachmanCommented:
douglaswenning,

Points distribution is always a tricky topic...

With most members signing up as Premium members, with unlimited points, "Running out of points" is never really an issue.

I think in this case Nick is due some points because he actually took the time to reformat the SQL into a more readable format.
He also helped move the post along.
And he actually pointed out the issue of the square brackets before I did...

It is never to late to change the points distribution.
You can click the Request Attention link and explain how you want the points distributed.

I will have no problem with whatever you decide.

Enjoy the weekend

;-)

Jeff
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 10
  • 6
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now