Query by form database - 2000

[McObraz]

Hello.

Somewhere in this forum, I came across the Query by Form Access 2000 database, and I wish to use some elements of it for a different purpose. The only challenge I am facing is on the query. Using the same method employed in the age and operator combo boxes (IIf......), How would I do the same using the inequalities and signs, for, say a Payment field like the one I have introduced in the database? Basically, what I would like to do is for example, sort out each person by payment, so that if I enter =500, <500,<=500, >=500, or >500, the number of values I get in the subform are correct. I need a field in the query that gives me the right result.

I am new to this, so any help will be appreciated.

Thanks.

 

[jzwp22]

Since you can only pass values to the query and not operators (=, <,>,LIKE, >= etc.), you have to use a different approach than what you have been using. You will have to build the query within code (i.e. the SQL text). Once constructed you can assign the SQL text to the record source property of the subform to display the search results. As to the report, you would have to replace the SQL text in the saved query with the SQL text you constructed in code (i.e. delete the existing saved query and then save the constructed query under the same name). In your case the SELECT and FROM clauses of the query would stay the same so that part is easy to construct. It is the WHERE clause that will require the coding work. For example, you use several IIF() functions in your WHERE clause, you would have to transcribe these into IF..THEN...ELSE...END IF statements in code depending on whether the user inputs a value into the respective control.

 

[McObraz]

jzwp22

Thanks for your response. Sorry my connection to the internet is not 24x7, I would have responded sooner.

Did you take a look at the database I attached? A search form (with a continuous subform) is already there, and on it, there is an an option for entering the age (Combined with a control for entering >,<,= e.t.c. and when this is used, the details that appear (as far as age is concerned) in the search are correct. I was wondering if the solution I am looking for does not have something to do with this.

For the reports, I don't have a problem with them. My issue is including a field in the querry (for example, for the age, what the developer for this one used is:

IIf([Forms]![Main Form]![cboOperator] Is Null Or [Forms]![Main Form]![txtAge] Is Null,True,IIf([DateOfbirth] Is Null,Null,Eval(DateDiff("yyyy",[DateOfBirth],Date())+(Format(Date(),"mmdd")<Format([DateOfBirth],"mmdd")) & [Forms]![Main Form]![cboOperator] & [Forms]![Main Form]![txtAge])))

What I would like is, looking at the same query (qrySearch), I have introduced a payments field. Now, I would like that if I enter, say >500 (with > in the cboOperator1 box and 500 in the payment box, that the results of the search would only be those payments that exceed 500.

As I said, I am really, really new at this, so coding would be a challenge.

Again, thank you very much for your help. Much appreciated.

Maurice.

 

[jzwp22]

My apologies, I overlooked the age operator part. I thought that you could not pass an operator as a query parameter and that part was true, but I see that the Eval() function can be used to construct the entire expression and do the subsequent evaluation of that expression. I learned something new again.

I've adjusted the WHERE clause to include the Eval() function for the payment; the amended database is attached.

 

[McObraz]

jzwp22

Thank you very much for your help. I have just downloaded it, so after implementing, I will let you know.

I hope that someday, I will also have acquired the necessary skills and experience to help other users.

Thanks.
Maurice

 

[McObraz]

jzwp22

It works, and this is EXACTLY the solution I was looking for.

Much, much appreciated.

Thanks.
Maurice.

 

[McObraz]

jzwp22

It works, and this is EXACTLY the solution I was looking for.

Much, much appreciated.

Thanks.
Maurice.

 

[jzwp22]

You're welcome & thanks for giving me the opportunity to learn about the Eval() function and its capabilities.