Incredibly Frustrated!!!

[kdm3]

Hello Everybody,

I have a problem which is really starting to annoy me (after much time spent messing with it).

I want to base the contents of a subform on a query, which in turn takes its arguments form controls on the main form... simple enough you'd think.

However, if no values are in the controls I want to disregard that control (essentially pass a "*"). I wrote a little function to check the value of the controls, and if they are "" pass a "*", otherwise the value of the control.

It works fine in the debug window, but when I put it in the query, and set the argument as the relevant form control I get a schpiel about too complex a calculation or something if the control is blank... but it works fine if a value is in place. What am I doing wrong - I've been at this for ages and am getting rather pee-ed off!

All suggestions heartily welcomed!

kdm3

 

[KenHigg]

Can you post a screen shot of the form and query?

ken

 

[The_Doc_Man]

I'm gonna guess that you are taking a shot at a dynamically defined search where in some cases you want to match some values and in other cases you want to accept anything (hence the "*")

Well, here is how I did that when I attacked the problem.

I always show a particular data set. So I built a prefix string something like
"SELECT [ParObjNam],[ParObjTyp]," etc etc "FROM [ObjTbl] WHERE ". OK, then when I clicked on the RUN QUERY button, the code visited my object boxes to see whether I had blanks. If it was blank, I did nothing. If not, though, I concatenated "[FieldX] = " and the contents of the box. If it was a text object, it was "[FieldX] = """ & valuefrombox & """ " but if it was numeric then all I needed was "[FieldX] = " & Str(valuefrombox).

Now, I had to set a flag to remember the first time I added something to the dynamic query. So if the flag was not set, I didn't do anything, but if it was, I also appened an " AND " to the string. In any case, if I added something to the string, I also set the flag.

When I was done, I added ";" to the end and executed the SQL.

I suspect your problem is the *, which you normally don't use in context with the "=" relational operator. You have to use "LIKE" if you want to use wildcards like that.

 

[kdm3]

A few screenshots...

I removed most of the fields from the query, once one works the rest will.

I know function is pretty crude, but like I said it ran when I debugged it.

Any assistance welcomed...

 

[kdm3]

Yo thanks Doc Man, I think i will just construct the entire query in code.
Only lately did I realise one could include user-defined functions in queries, and I've started to use this property over-obsessively, without completely knowing what I'm doing!

I looked over what I'd written and it was just awful. I think I'll ditch it and start again and just use SQL!?