Query by form w/combo & text boxes - some entries null

[Webwolf88]

Hello,

I need some help with a query I'm writing. I've read up on union and sub queries but have not figured my problem out as of yet. I am not even sure I can do this in the criteria definition.

I have a table that contains several columns (fields) that represent features of a part. Let me use a house for an example.

Database name: Houses

Type Paint Windows Levels Remarks
Ranch white 8 1 Small yard
CapeCod blue 10 2 2 acres
Colonial white 12 3 1/4 acre
Country white 6 2 1/2 acre


The form used to drive the query has combo boxes to limit the selection for fields: Type, Paint, Windows, Levels. It also has a text box for addition comments (ie "Remarks&quot. What I want to do is be able to search on one or more of these fields. For instance, if I want to search on Paint and Levels those are the only two fields on the form that will have a value - the rest will be null.

So if my form values are cboPaint = white, and cboLevels = 3, then the query should find....

Type Paint Windows Levels Remarks
Colonial white 12 3 1/4 acre

Likewise if I want to search on the Remark field with txtRemark = "1/2", then it should return...

Type Paint Windows Levels Remarks
Country white 6 2 1/2 acre

Can I do this in the criteria line or do I need to write a VBA Module? Does anyone have any suggestions of how to code this?

The ultimate end product of all this work will be to develop an engineering database to search 15,000 different parts. So any help would be greatly appreciated.

Thanks!

 

[GoDawgs]

I have a few forms like this, and the easiest way I found to do this was to create an SQL string in VBA. Just create the query, then add on the WHERE clauses to the string according to whether or not those combo boxes are null. Let me know if you need help with that...but that should get you started. Hope it helps.

Kevin

 

[Webwolf88]

Thanks for responding Kevin

Yes, I do need help with the code. I've only recently tackled the query portion of Access and have no prior experience to draw upon. I have read several threads on the boards and attempted to apply them to my situation with no success.

Mic

 

[Webwolf88]

Little help?

 

[Webwolf88]

Ok.. a friend showed me how to do this. So I'm going to post it for anyone else with similar problems. However, I'm just too tired right now to adjust it to fit the "house" analogy above. What this does is takes input from a form which consists of combo boxes and one text box. It then searches the database for matching records on a file that contains the form fields which are not null. If the remarks text box is not null, it finds records that contain what your user enters in the form. For any field on the form that is null, it just ignores.

Here is a cut & paste of a parts database. The specific table being searched on is the endshield (ie end bracket) of a motor.

SELECT EndshieldDefinitions.PartNumber, EndshieldDefinitions.Casting, EndshieldDefinitions.TenonDiameter, EndshieldDefinitions.[Std/Cface], EndshieldDefinitions.Material, EndshieldDefinitions.XP, EndshieldDefinitions.BrgSize, EndshieldDefinitions.ShaftHoleSize, EndshieldDefinitions.BrgBoreDepth, EndshieldDefinitions.Tenon2Face, EndshieldDefinitions.TolRing, EndshieldDefinitions.ThruBoltHoleTappedDepth, EndshieldDefinitions.ThruBoltHoleTappedSize, EndshieldDefinitions.ThruBoltHoleThruHoleDia, EndshieldDefinitions.DrainHoleSize, EndshieldDefinitions.DrainHoleLocation, EndshieldDefinitions.DrainHoleQuantity, EndshieldDefinitions.SlingerSpotFace, EndshieldDefinitions.BrgClamp, EndshieldDefinitions.FanShroudMtg, EndshieldDefinitions.LeadLoc, EndshieldDefinitions.Finish, EndshieldDefinitions.Remarks
FROM EndshieldDefinitions
WHERE ([Forms]![EndshieldForm1]![cboPartNumber] is null or ((EndshieldDefinitions.PartNumber)=[Forms]![EndshieldForm1]![cboPartNumber])) and
([Forms]![EndshieldForm1]![cboCasting] is null or ((EndshieldDefinitions.Casting)=[Forms]![EndshieldForm1]![cboCasting])) and
([Forms]![EndshieldForm1]![cboTenonDiameter] is null or ((EndshieldDefinitions.TenonDiameter)=[Forms]![EndshieldForm1]![cboTenonDiameter])) and
([Forms]![EndshieldForm1]![cboFlangeType] is null or ((EndshieldDefinitions.[Std/Cface])=[Forms]![EndshieldForm1]![cboFlangeType])) and
([Forms]![EndshieldForm1]![cboMaterial] is null or ((EndshieldDefinitions.Material)=[Forms]![EndshieldForm1]![cboMaterial])) and
([Forms]![EndshieldForm1]![cboXP] is null or ((EndshieldDefinitions.XP)=[Forms]![EndshieldForm1]![cboXP])) and
([Forms]![EndshieldForm1]![cboBearingSize] is null or ((EndshieldDefinitions.BrgSize)=[Forms]![EndshieldForm1]![cboBearingSize])) and
([Forms]![EndshieldForm1]![cboShaftHoleSize] is null or ((EndshieldDefinitions.ShaftHoleSize)=[Forms]![EndshieldForm1]![cboShaftHoleSize])) and
([Forms]![EndshieldForm1]![cboBrgBoreDepth] is null or ((EndshieldDefinitions.BrgBoreDepth)=[Forms]![EndshieldForm1]![cboBrgBoreDepth])) and
([Forms]![EndshieldForm1]![cboTenonToFace] is null or ((EndshieldDefinitions.Tenon2Face)=[Forms]![EndshieldForm1]![cboTenonToFace])) and
([Forms]![EndshieldForm1]![cboToleranceRing] is null or ((EndshieldDefinitions.TolRing)=[Forms]![EndshieldForm1]![cboToleranceRing])) and
([Forms]![EndshieldForm1]![cboThruboltHoleDepth] is null or ((EndshieldDefinitions.ThruBoltHoleTappedDepth)=[Forms]![EndshieldForm1]![cboThruboltHoleDepth])) and
([Forms]![EndshieldForm1]![cboTappedHoleSize] is null or ((EndshieldDefinitions.ThruBoltHoleTappedSize)=[Forms]![EndshieldForm1]![cboTappedHoleSize])) and
([Forms]![EndshieldForm1]![cboThruboltHoleDia] is null or ((EndshieldDefinitions.ThruBoltHoleThruHoleDia)=[Forms]![EndshieldForm1]![cboThruboltHoleDia])) and
([Forms]![EndshieldForm1]![cboDrainHoleSize] is null or ((EndshieldDefinitions.DrainHoleSize)=[Forms]![EndshieldForm1]![cboDrainHoleSize])) and
([Forms]![EndshieldForm1]![cboDrainHoleLocation] is null or ((EndshieldDefinitions.DrainHoleLocation)=[Forms]![EndshieldForm1]![cboDrainHoleLocation])) and
([Forms]![EndshieldForm1]![cboDrainHoleQty] is null or ((EndshieldDefinitions.DrainHoleQuantity)=[Forms]![EndshieldForm1]![cboDrainHoleQty])) and
([Forms]![EndshieldForm1]![cboSlingerSpotFace] is null or ((EndshieldDefinitions.SlingerSpotFace)=[Forms]![EndshieldForm1]![cboSlingerSpotFace])) and
([Forms]![EndshieldForm1]![cboBrgClamp] is null or ((EndshieldDefinitions.BrgClamp)=[Forms]![EndshieldForm1]![cboBrgClamp])) and
([Forms]![EndshieldForm1]![cboFanShroudMtg] is null or ((EndshieldDefinitions.FanShroudMtg)=[Forms]![EndshieldForm1]![cboFanShroudMtg])) and
([Forms]![EndshieldForm1]![cboLeadLocation] is null or ((EndshieldDefinitions.LeadLoc)=[Forms]![EndshieldForm1]![cboLeadLocation])) and
([Forms]![EndshieldForm1]![cboFinish] is null or ((EndshieldDefinitions.Finish)=[Forms]![EndshieldForm1]![cboFinish])) and
([Forms]![EndshieldForm1]![txtboRemarks] is null or ((EndshieldDefinitions.Remarks) like "*"& [Forms]![EndshieldForm1]![txtboRemarks]&"*"));