Passing Parameters with > or < or =

[cctonto]

I have a form the captures user input in text boxes. These parameters are then fed to a query using this format:

[forms]![NameOfForm]![NameOfTextBox]

Works fine. However, I would like to allow the user to select through check boxes the "greater than" or "less than", etc. that would preceed this statement. Sort of like this:

>[forms]![NameOfForm]![NameOfTextBox] or
<[forms]![NameOfForm]![NameOfTextBox]

Does anyone know how to do this?

Thanks,
Steve Tucson, AZ

 

[JeremyNYC]

I would build the sql string explicitly in code. That way you can have a combo box with <,<=,=,>=, and > in it and just put the value into your string. Then pass that string to whatever object you're using, as the recordsource.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[cctonto]

Jeremy,

Thanks Jeremy. I think I have the idea now.

 

[cctonto]

Alright, I'm lost. I can build combo boxes all day, but how the choice enters into the SQL and how the SQL goes to the query is evading me. I've looked at the web site's Developers section and I just don't get it.

Could you show me a brief example or point me to some source for help?

Thanks,

Steve
Tucson, AZ

 

[MichaelRed]

strSql = &quot;Select &quot;
strSQL = strSQL & &quot; & Chr(34) & forms!myfrm.myctrl & chr(34)

while woefully incomplete is the generic syntax to use form control's contents to construct an SQL Statement. The BASIC concept it to generate the string from literal expressions (e.g. &quot;Select &quot and concatenate this with valid references (to form controls as in forms!myfrm.myctrl), surrounded with quotes (the Chr(34) above.

Of course the 'example' above is completly inadequate to accomplish anything, it is only to illustrate the generic syntax.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[JeremyNYC]

Yeah, Michael's example is right on.

Here's one with one more level of detail. If you have a form that collects the BankID, to allow you to bring up another form with all the Accounts related to that bank, you might do something like this, in the code behind a command button:

Private Sub btnShowBank_Click()
Dim strSql As String

If Not (IsNull(Me!cmbBank)) Then
strSql = &quot;SELECT BankID, BankName FROM tblBank WHERE BankID = &quot; & Me!cmbBank
Call DoCmd.OpenForm(&quot;frmBank&quot
Forms!frmBank.RecordSource = strSql
Else
Call MsgBox(&quot;Please choose a bank.&quot;, , &quot;No Bank Selected&quot
End If
End Sub


This is setting the recordsource of a form. You can use this method to build much more complex Sql statements and then open forms with those sql statements as the recordsource.

Another strategy is to build just the WHERE clause, and then you can pass that as an argument in the OpenForm statement.

You can indeed work with queryDef objects and change the Sql property based on what you put together, but I would say it's probably better to build a form that holds the results of a query than to work with the query directly.

Hope this helps.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[cctonto]

Thanks again. After reading and trying new code, this last message from Jeremy put a big piece in the puzzle.

If there is one last thing it would be &quot;what to do with all the empty fields?&quot; You see, I have 50+ fields and the user will realistically use any 3 he wants to get the records he needs.

Most likely there will be 50 &quot;If...Then&quot; statements that evaluate each of the form's fields of possible input. Then build the strSql from what is left. Is that how you would approach it?

Now, this is just one thought that has crossed my mind so hear me out before you laugh. What if I were to make 50 Append queries to a common TempDatabase. Only the queries that represent any field input will be run - &quot;If...Then&quot; again. Then a Delete query to delete duplicates entries. Open my form with a Select query that sorts the TempDatabase and I have it done. This will work in the runtime that I deploy and queries run a little faster then SQL. Well, second thought, there will be more than 50 queries because there will be one for each >,<,=, date fields will all be &quot;between&quot; queries. Seems like about the same amount of work. Would this be wrong?


Thanks again,

Steve
Tucson, AZ

 

[JeremyNYC]

Steve,

Yeah, I'd avoid the 985 query approach. Do it in code with a bunch of if then structures. It's a lot of code writing, but it's not all that much processing. By the way, I'm assuming you've checked out and rejected the built-in filter-by-form feature, yeah?

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[cctonto]

Ok.............

I'm changing my login name so nobody will ever associate these questions with me, then moving to Australia to raise Kangaroos.

Thanks for making me see the forest for the trees. I've tried it, it works perfect, made a deployable to test... it also works. Adding &quot;Advanced Filtering&quot; to a customized tool bar even allows the filter criteria to be saved for next use....but as a filter. Can a for be opened using this filter?? It attaches the form name to the word filter1 and comes up with the filter name as EnterscreenFilter1.

Now, if you don't answer this, I'll assume I've missed something in the third grade and start looking at Circle K for night work.

Kidding aside, you have solved my almost week long puzzle and I want to thank you.

Steve

 

[JeremyNYC]

Steve,

don't smack youself too hard. That's why this forum exists.

I actually don't knwo the answer to you last question. Maybe you and I were sitting next to each other in the third grade.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.