Using Query Builder for dynamic query

[martiros]

Hi,
can I specify in Criteria field a variable or combo box value?
I need to build WHERE clause from info coming from text boxes and combo boxes.
Now I'm doing everything by hand & just wanted to know if there is a way of using Access's visual Query Builder for the same task to save a little time?

Thanks

 

[scriverb]

Yes, in the Query Builder you can build criteria references to a forms textbox or combobox. After placing your cursor in the column's criteria row click the Expression Builder button from the toolbar. Select in the left column forms and select the form that you want to reference. Now in the center column are all of the controls from that form. Select the textbox or combobox that you want its value to be your criteria reference. You can also use a full list of operators and functions to build your expression.

This help you get started.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[martiros]

Thank you, Bob!

 

[martiros]

Bob,
I've tried to do everything in Qry Builder but failed -ended up with inserting variable for the name of the Month column by hand:

sql = sql & "SELECT tblClients.clFirstName, tblClients.clLastName, tblClients.clShip, tblClients.clPrintNum,"
sql = sql & &quot; IIf([clPrintNum]<1000,(([clPrintNum]*[costCostPerPrint])+50+10)+(([clPrintNum]*0.355)+3),&quot;
sql = sql & &quot;(([clPrintNum]*[costCostPerPrint])+10)+(([clPrintNum]*0.355)+3)) AS PrintCharges,&quot;
sql = sql & &quot; tblClients.clMay, tblCostPerPrint.costPrintNum, tblCostPerPrint.costCostPerPrint&quot;
sql = sql & &quot; FROM tblClients, tblCostPerPrint&quot;
sql = sql & &quot; WHERE (((tblClients.clShip)='Bulk') AND ((tblClients.clPrintNum)>199)&quot;
sql = sql & &quot; AND ((tblCostPerPrint.costPrintNum) Between [clPrintNum] AND ([clPrintNum]-24)))&quot;
sql = sql & &quot; AND (([tblClients].[&quot; & strMonth & &quot;]) = Yes)&quot;

Couldn't figure out how to to in QBuilder this last line with strMonth coming from combo box...

 

[scriverb]

Is this the part you are talking about:

(([tblClients].[&quot; & strMonth & &quot;]) = Yes)&quot;

What is the name of your form and the name of the control? Post that for me and I will show you the proper syntax for doing this.


Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[martiros]

Form -frmClients
control - cboMonth

I know how to do it in code and everything is working fine - just was curious how to do it in Query Builder...

Is it possible?

Thank you very much!

 

[scriverb]

Sure, you can do this. In the top row where you identify the field to be displayed or analyzed click the expression builder. Now select Forms, select the form name, and now select the combobox control and paste. This will put the expression FORMS![FormName]![controlName] in the column and row. Now in the QBW put a Yes in the criteia row and uncheck the column. Now check out the where statement. You will have the following:

WHERE [Forms]![YourFormName]![ComboBoxControlName]=Yes;

Is this what you are meaning. I am just wondering why you would want to analyze the value of the combobox within a query. This can and should be done in VBA code before the query is called for execution. You see it is a condition of even calling the query. You are not using the value as a criteria for selecting a particular record from the table. If the forms control says yes the records are selected and if NO then nothing is selected. See what I mean.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[martiros]

Thanks again, Bob!
I have 12 month columns (yes/no) in the table and I need to select on the form the month.
How would you suggest to do this query?
So far it worked OK for me but if there is another approach I'd be interested to know.

 

[scriverb]

I would restructure the table to have one field called Month_Field and have my form populate it from a dropdown combobox with the values 1 thru 12 to indicate the month. This way you have one field to check for the month of the record. Much easier to use and perform a select by just this one field.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[martiros]

The thing is that 1 record can relate to many months.
It shows yearly shedule of our clients and most of them skip 2-5 months.
If I'll have just 1 column how can show that the client has scheduled delivery for Jan, March, june ?

 

[scriverb]

Okay, I understand now. The correct way for this to be setup is a one to many relationship from your master table to another table that shows scheduled months. The second table would just have the IDfield and the SchedledMonths.

You could display the months is a subform on your main form. And add or delete as needed. Does that sound like a better situation?

I can help you with this.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[martiros]

I see.
Right now there are 12 month checkboxes on the form and this is very convenient for everybody because they can very easily change the schedule and/or see it without scrolling.
Can this subform with month have checkboxes?

How my report query will change?

 

[scriverb]

martiros: I want to apologize but my system is going down for a few days and I won't be able to work on this with you right now. I have to install a new harddrive and then reinstall everything and get back up and running. Hopefully, another member of TT will pick this up and give you a hand.

My thoughts at this time are that the 1 to many is the way to go with the table designs. I was thinking initially about a subform but I think a better way would be a list box that shows the new 12 months from the current and using the multi-select option would mirror the records in a schedule table where the records that are selected would be flagged in the corresponding months record. By designing it this way you can easily select lists of scheduled clients by selecting on the particular month and all records would be queried and those found selected would be listed in a query /report along with their linked master record information. The listbox would show all 12 months without any scrolling necessity and a simple click of months would highlight it. The AfterUpdate event procedure of the listbox would would run a query to update the corresponding months to any changes that take place on the form.

I hope this helps you in your design. I am throwing a whole new design at you and it would require a bit of coding and setup but it is possibile and I think would be very user friendly. If you would send me an email(see my profile) I will work on this design over the weekend and return it to you so you can see what I mean. Good luck.

Once again I apologize for getting started and having to back out of this but I am having terrible system problems right now and this PC is about to crash every 5 minutes. If I go to ACCESS Help or a certain type of link in the browser I only get about 10% of the characters to show. Something is drastically wrong so desperate measures are needed.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[martiros]

That's OK.
I'm leaving for several days anyway.
No hurry with this project ...and my PC is screwed up also but each time I'm ready to wipe everything out it magically fixes itself up to the point that's just enough for me to say ...&quot;OK, not today ..but next time I show you...

Thanks.
I'll be back Sunday.

 

[scriverb]

Our PC's must be simese twins. But, mine is getting a whole new C drive. I have some bad sectors that just keep getting corrupted.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???