1. select tname from ttask
    where left(toutnum, instr(toutnum,'.')-1) = :tnum;
    When I execute the above query in Access I get the following msg:
   This expression is typed incorrectly, ot it is too complex to be evaluated. For eg, a numeric expr may contain too many complicated elements. Try simplifying the expr by assigning parts of the expr to variables.

I'm executing the above query from a combobox. When I use other functions like len instead of instr my query goes thro. Help!

2. I've been getting the following error/message in an access form that is created. The message pops up during execution. I have a couple of combo-boxes which have a query each as source. In a couple of them I get the following msg:

   <My form name> can't find the macro '.'

The macro (or its macro group) doesn't exist, or the macro is new but hasn't been saved. Note that when you enter the macrogroupname.macroname syntax in an argument, you must specify the name the macro's macro group was last saved under.

I do not have a single macro in the form. When I tried to execute the form on somebody else's computer the msg didn't pop up. What can it be. Is there a problem with the installed software or is it some problem with the form that has been created. This message started of in one place and it pops up in three places now and consistently.

Can anyone help please??
kvidy.

1. Assuming you typed your Where clause exactly, ":tnum" isn't a syntax that I'm familiar with. What purpose does the colon ":" serve? Other than that, the syntax should be okay. However, you may want to consider changing the apostrophies to quotes.

2. Tricky question. This could be buried in the SQL of one of your combo boxes and if you're using queries for those combo boxes, it could be buried in one of those queries. For example, you may have a parameter on a query that you use.

Some ideas:

While '.' (apostrophies) may be correct for an SQL statement, you may have to use "." (quotes) in your forms.

Consider that opening the form on someone else's computer may not go through the same logic as opening the form on your computer. This could be why the other computer doesn't have the problem. Or it could be that your computer needs Access repaired. But before you try Help | Detect and Repair... in Access, you should try starting Access with the Decompile parameter and then open your database.

Step 1: Go to Start | Run...
Step 2: Enter the full path to your MSACCESS.EXE program, (quote it if the path contains spaces) followed by /decompile. e.g. Step 3: Open your database and test your form.

The act of copying the file to another computer can remove the previous compile information.

If that doesn't work your database may be corrupt. Create a new blank database and copy the objects from the one that isn't working.

Good luck.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

InStr() returns a boolean, either True or False, typically checked for like this:

If InStr(stPhraseToBeChecked, stValueCheckedFor) = 0 Then

   If the value returned is 0, then the string was not found.
   If the value <> 0 then the string was found.

Perhaps you need to do something like:

where left(toutnum, (instr(toutnum, ".")<> 0)-1) = :tnum;

Of course, I am not sure why you are using this complicated expression. Perhaps you want to break it down into more manageable sections.

Can't find macro is a catch all error that means Access couldn't figure out what you were trying to do and assumes it must be a macro name.



mmcdonal

Actually, mmcdonal, Instr returns the position of the search string within the string being searched.

Ooops, that's right. I always check for the presence by checking for 0 or not 0, which confused me into thinking it was a boolean (0 or -1).


mmcdonal

But, since you pointed that out, two other possibilities came to mind. I still don't understand the colon. But maybe because the statement is complex it should read:

Where """" & left(toutnum, (instr(toutnum, ".") - 1) & """" = :tnum;

That may be causing the macro error message because somewhere in the interpretation of the statement there is a "." (dot) which tends to imply a macro in some cases.

The other thing is a potential future problem. What if toutnum doesn't have a "." in it? Then the Left() will be Left(toutum,-1) which is invalid. So the clause needs to be even more complex...

Where iif(instr(toutum,".")= 0, Null,"""" & left(toutnum, (instr(toutnum, ".") - 1) & """") = :tnum;

Of course I don't know what the ":" means. And we don't know if perhaps it is something else in the query that is causing the error.

Have you tried executing the query without the Where clause?

Perhaps package the variable up front, like:

If InStr(toutnum, ".") = 0 Then
   ...
Else
   ...
End If

I think if you split on the "." and then take the (0) of the array, you won't get out of range. So maybe take the Where from the array.

NumArray = Split(toutnum, ".")

tnum = NumArray(0)

SELECT
     tname
FROM
     ttask
WHERE
     ? = tnum

In any event, I think you need to clarify the variables, and then put the finished variables into the SQL statement. Access doesn't like to work out variables and expressions in the middle of SQL statements.

mmcdonal

Thank you all...very much for the prompt replies and for taking time to give suggestions to fix my problems. I'm again going to address the same 2 queries I had mentioned earlier and in greater detail. Hope this helps you in getting a better picture of what I'm trying to do. It is a form which adds records onto 1 table. I have a few combo boxes that perform a query each to list values that the user can choose from. My form is mostly query driven and qith very very minimal code...meaning I have a query linked to each of the combo boxes...ie. the source for the combo box is a query.
1. Instr
  : in the query prompts for a parameter value. I use that when I run the query from outside the form. In the form I ref it like...[Forms]![TaskForm]![tnum]......tnum being a text field in the form taskform.
When I have the instr part in the select list itself...I get the desired result...but when I have it in the where clause I get the expression error that I've mentioned earlier.
The sql query is something like this...
Select task_name from msp_tasks
where proj_id = [Forms]![Taskform]![projid]
and left(outline_num, instr(outline_num,".")-1) = [Forms]![Taskform]![tnum];
I've changed the '.' to "." ater reading the replies posted.
My outline_num values would be like 2, 3.1.18, 14.3.6.7, 20.2 31.6.1, 2.1.1 so on just for example. Im trying to get the records matching the first part of the num...ie 2, 3, 14, 20, 31, 2 will be compared with a value in a field. Both are text fields. If the 2 is the value then from the above eg. 2 records should be chosen. I'm forced to do everything in that same query...so I can't break it into stages and do it because has to appear in the drop down list of the combobox.
What am I doing wrong??

The second query I'll explain in the next post.

Thanks
kvidy.

Now the '.' Macro Issue:

Thanks rjweers for the alternatives you had given.
I followed most of what you had suggested but the problem still persists.
-- The 2 combo boxes that threw up the error msgs had '(apostrophies) which I changed to " (quotes).
-- I tried the decompile too.
-- I created a blank database and pasted the objects from the one that has the problem.
But I have not done a detect and repair yet.
Btw, I once again tried running it on a different computer which has a diff version of access..2003 but WITHOUT the decompile option(because I got the reply after I'd done it) and I had the macro msg pop up there too. Thought I must mention this cos I had earlier asked somebody else to execute it in their computer and they said it was fine....but I don't know really.
When I installed access 2000 on my computer, I had some virus and had to re-install access. I now have an Norton anti virus protection. I'm not sure if this would cause it to happen. And now, I don't know if it is the query or the access software. It is a very very small app and I'm stuck withh this error. Would it make any sense if I mailed you a copy of this?
Awaiting a reply/post.
Thanks again
kvidy.

I will try the iif(instr............... that you've mentioned and let you know.
The case of a . not being there in some of the numbers is def possible but isn't the select going to ignore those records? the left(instr(... could result in a left(num,-1) which would result in data not matching the compared data and the record won't appear in the selected list.....I assumed. If my assumption is not true then I'll have to get iif into the picture.
kvidy