Faster way to do this query ??? 1

[Tinkerers]

Hi,

I have a form with lots of combo boxes. I want the user to be able to search for a particular record, based on what he selected in the form.

My existing SQL Query is VERY, VERY slow. Is there a better way to accomplish this, see below ?

Thanks for any help !
Paul

SELECT Assets.BarcodeNumber, Assets.AssetDescription, Assets.Make, Assets.Model, Assets.SerialNumber, Assets.[Year of Manufacture], Assets.Options, Assets.Firmware, Assets.Status, Assets.[Repair History], Assets.State, Assets.Site, Assets.Comments, Assets.Region, Assets.Rack, Assets.Row, Assets.Slot, Assets.UpdatedBy, Assets.Channel, Assets.Address, Assets.CalibrationDate, Assets.ReminderEmail
FROM Assets
WHERE ([barcodenumber]=Forms![AssetsQuery]![Barcodenumber] OR isNull(Forms![AssetsQuery]![Barcodenumber]))
AND
([Make]=Forms![Assetsquery]![Make] OR isNull(Forms![Assetsquery]![Make]))
AND
([Model]=Forms![Assetsquery]![Model] OR isNull(Forms![Assetsquery]![Model]))
AND
([SerialNumber]=Forms![Assetsquery]![SerialNumber] OR isNull(Forms![Assetsquery]![SerialNumber]))
AND
([Status]=Forms![Assetsquery]![Status] OR isNull(Forms![Assetsquery]![Status]))
AND
([AssetDescription]=Forms![Assetsquery]![AssetDescription] OR isNull(Forms![Assetsquery]![AssetDescription]))
AND
([Firmware]=Forms![Assetsquery]![Firmware] OR isNull(Forms![Assetsquery]![Firmware]))
AND
([Options]=Forms![Assetsquery]![Options] OR isNull(Forms![Assetsquery]![Options]))
AND
([State]=Forms![Assetsquery]![combostate] OR isNull(Forms![Assetsquery]![combostate]))
AND
([Site]=Forms![Assetsquery]![site] OR isNull(Forms![Assetsquery]![site]))
AND
([Year of Manufacture] <Forms![Assetsquery]![BeforeDate] OR isNull(Forms![Assetsquery]![BeforeDate]))
AND
([Year of Manufacture] =Forms![Assetsquery]![Year of Manufacture] OR isNull(Forms![Assetsquery]![Year of Manufacture]))
AND
(right([Year of manufacture],4)<=forms![Assetsquery]![BeforeDate] OR isNull(Forms![Assetsquery]![BeforeDate]))
AND
([Region]=Forms![Assetsquery]![Region] OR isNull(Forms![Assetsquery]![Region]))
AND
([Rack]=Forms![Assetsquery]![Rack] OR isNull(Forms![Assetsquery]![Rack]))
AND
([Row]=Forms![Assetsquery]![Row] OR isNull(Forms![Assetsquery]![Row]))
AND
([Slot]=Forms![Assetsquery]![Slot] OR isNull(Forms![Assetsquery]![Slot]))
AND
(Left([UpdatedBy],8)=Forms![Assetsquery]![UpdatedBy] OR isNull(Forms![Assetsquery]![UpdatedBy]))
AND
([Comments]=Forms![Assetsquery]![Comments] OR isNull(Forms![Assetsquery]![Comments]))
AND
([Channel]=Forms![Assetsquery]![Channel] OR isNull(Forms![Assetsquery]![Channel]))
AND
([Address]=Forms![Assetsquery]![Address] OR isNull(Forms![Assetsquery]![Address]))
ORDER BY Site, Rack, Row, Slot;

 

[cmmrfrds]

You are selecting on every field each time the query is run. I am guessing the user only selects a couple or a few comboboxes.

Build the query dynamically only including in the where clause the comboboxes that are selected.

 

[Tinkerers]

Dynamically ??? I think you must mean to search on fewer fields, depending upon which boxes the user puts info into. How could I do this dynamically ? Do you have any code suggestions ?

Thanks,
Paul

 

[sathandle]

Quickest method - Filter by Form. (Greatest tool invented). This also allows your user to save the filter they set-up as a query and re-run it again on the form. (Save filter option on the filter toolbar)

Suppose a form is for customer names for a Nationwide company.

User selects filter by form from either your custom menu or Records, Filter, Filter by Form.
User selects &quot;NY&quot; in the State combo box.
User clicks on the tab &quot;Or&quot; in the bottom left side
User selects &quot;CA&quot; in the State combo box on the second tabbed form.
User clicks on the Filter icon (looks like an old TV tube in the toolbar)

Now the list of customers will just display those customers in NY & CA.

Advantage - no Programming.
Disadvantage - training; difficult to use with subforms - only filters that subform, not customers with X product purchased.

Depending on the number of users and their skill level, you make the decision...



SATHandle

definition of 'less behind': &quot;not fully caught up, digging out slowly, one-week delay to &quot;The IT hit the fan.&quot;

 

[cmmrfrds]

Create some variable to store the sql statement and then use the sql statement in the variable as the recordsource for the Form that will display the results.

Dim sqlA, sqlB, sqlC as variant

sqlA = &quot;SELECT Assets.BarcodeNumber, Assets.AssetDescription, Assets.Make, Assets.Model, Assets.SerialNumber, Assets.[Year of Manufacture], Assets.Options, Assets.Firmware, Assets.Status, Assets.[Repair History], Assets.State, Assets.Site, Assets.Comments, Assets.Region, Assets.Rack, Assets.Row, Assets.Slot, Assets.UpdatedBy, Assets.Channel, Assets.Address, Assets.CalibrationDate, Assets.ReminderEmail
FROM Assets
WHERE &quot;

If len(Forms![AssetsQuery]![Barcodenumber]> 0 then
sqlB = &quot;[Make]= &quot; & Forms![Assetsquery]![Make]
end if
If len(Forms![AssetsQuery]![Barcodenumber]> 0 and len(sqlB) > 0 then
sqlB = &quot;AND [Model]= &quot; & Forms![Assetsquery]![Model]
else
sqlB = &quot;[Model]= &quot; & Forms![Assetsquery]![Model]
end if

etc.......

Me.Recordsource = sqlA & sqlB

This is the general idea.

 

[Tinkerers]

Thanks for the replies. Given the limited expertise of the users, I'd like to automate this as much as possible.

I like the idea of using the variables in this way. It should speed things up quite a bit. I didn't realize I could use the recordsource for the form this way. I display the results in another form, so I will try it out.

Thanks for the help !
Paul

 

[IcesMaster]

Why not create function to develop SQL Text and run it as Recourd source, in the form or at other form. It's only create WHERE clause based on the existing value 0f (text/combo)box.

Example:

Dim SdlA, SqlB As String

SqlA = &quot;SELECT Assets.BarcodeNumber, Assets.AssetDescription, Assets.Make, Assets.Model, Assets.SerialNumber, Assets.[Year of Manufacture], Assets.Options, Assets.Firmware, Assets.Status, Assets.[Repair History], Assets.State, Assets.Site, Assets.Comments, Assets.Region, Assets.Rack, Assets.Row, Assets.Slot, Assets.UpdatedBy, Assets.Channel, Assets.Address, Assets.CalibrationDate, Assets.ReminderEmail
FROM Assets
WHERE &quot;

SqlB = &quot;&quot;
IF len(Forms![AssetsQuery]![Barcodenumber]> 0 then
SqlB = SqlB & &quot;[Marcodenumber]= &quot; & Forms![Assetsquery]![Barcodenumber]
IF len(Forms![AssetsQuery]![Make]> 0 then
SqlB = SqlB & &quot;AND [Make]= &quot; & Forms![Assetsquery]![Make]
...
...
* You can create an invisible text box or to a public mem
Me.TheSqlText = SqlA & SqlB

* then open other form where the Resoursource set as above. Exp
DoCmd.OpenForm &quot;TheOtherForm&quot;

---
On Open TheOtherFormProperty:
Me.Recordsource = Forms!AssetsQuery.SqlText

I hope it satisfy you, and it's only adding something of cmmrfrds idea