Strange Query Behavior

J

JK

I have a query that is acting very strange. I am trying to query two
related tables that have a one-to-many relationship. The query has
certain criteria that is referring to a form, so depending on what the
user has typed in, the query will change (and thus the list box results
- which is based on the query in question - will change with what the
user types).

I'm getting an "Invalid Argument" Error when I run the query on its
own, and when I run the form, I just get zero results in the list box.

The part of the query in question is in the HAVING clause after the
GROUP BY.

The original line of the query in question looked like this:
[tblCommitments]![ObligationID] Like
[Forms]![frmFindCommitment]![txtSearchObligationID]

txtSearchObligationID on the form would be whatever the user had typed
into another text box with "*" on both sides. For example, if a user
typed 78 in the text box, txtSearchObligationID would equal *78*. The
problem was that users would type in numbers that were there but would
get no results.

So, I went to the query directly and changed the line to:
[tblCommitments]![ObligationID] Like '*1*' to see if it made a
difference.
This worked fine.

I then changed the number to read:
[tblCommitments]![ObligationID] Like '*33*'
I get an "Invalid Argument" error. There are records where
[tblCommitments]![ObligationID] has 33 within it (note ObligationID is
an AutoNumber)

I then tried:
[tblCommitments]![ObligationID] Like '*78*'
and that worked!

71 doesn't work, 79 does. 80, 81, 82 do not work. 13 and 12 work. 11
does not.

Note that in my tblCommitments Table, there are numbers ranging up to
14377.

This doesn't make any sense. Anyone have any idea what might be going
on???

Thanks,
JK
 
J

JK

I figured out the problem, although I don't exactly see how it was
related to the symptoms I outlined below.

In my query was a memo field that I was also trying to restrict by
criteria using wildcards ("*") - and that was causing the "Invalid
Argument" error.

How that was paying attention to what number I put in the other field
is beyond me. Anyway, I took out the memo field from the query and it
now works fine.

JK
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top