Please Help with Query

[cdl1701]

I have been trying to get this to work for about 2 weeks now with verry little luck. Can some one take a look at my DB and tell me what I am doing wrong

This post decribes what I am doing

http://www.tek-tips.com/viewthread.cfm?qid=1196043&page=1

This thread is the problem that I am having now

http://www.tek-tips.com/viewthread.cfm?qid=1196816&page=1

 

[MajP]

Are you still working on this one? You seems to have a lot of threads open on it. I could not figure out your status on this.
However this is how I do this in general. I build a form that produces me a long "where" clause, I do not use a parameter query. But this can be simple or very complext depending on how much flexibility you want. Normally I save this "string where" as a global variable so I can use this form as a front end on a lot of reports and forms.
However, you will need to describe what type of controls you plan to use, and which fields. Then describe how flexible you want to be. For example

Manufacturer Scale Autograph

lets say you choose "Mattel", "1:50" and "True".
1) simple would be find the records that matches any of these cases:"Or Case"
or the reords that match all of these cases:"And Case"
2) More complex would be to allow the user to mix and match. For example: Show all that are made by Mattel, and are 1:50. Or they have an autograph
3)More complex would be multiselect list boxes where you could create something like
Show all made by Mattel or Made by Hasbro, but are either 1:50 or 1:60, or Have an autograph.

So if your interested, povide me your status. Include the names of the pertinent tables, fields, and controls. Decide how complex the search needs to be. If it is just three fields, and it needs to match all three, that will be very easy. I have one I am using now with 6 multi select list boxes, and two option groups, and a checkbox. With just a few simple clicks, you can end up producing behind the scenes a where statement with 100s of criteria and conditions.

 

[cdl1701]

Ok, here is where I am at. I need to be able to select one or more of the fields to use in the search so for example:

Manufacturer Scale Autograph

I would need to select one Item from Manufacture, Scale and true for Autograph. "Mattel", "1:50" and "True" then have the query return all records that match this criteria. So I would say that it probably matches your first option in your post. One main problem I am having though is that if I leave some of the fields Null nothing is returned. I then placed something like this in the criteria of each item of the query:

[Forms]![Search]![Manufacturer] Or [Forms]![Search]![Manufacturer] Is Null

I did this for each combobox and seams to work except for one field which is the Year field, and I can not get anything to work for the check boxes.

Unfortunitly I am at work right now so I can not open my DB and list the names of the tables, fields and controls but if you would like to take a look and evaluate it and tell me what I am doing wrong I can send it to you. My e-mail is cdl1701@yahoo.com

 

[MajP]

This is my general approach. I just think this gives a lot of flexibility. You can use this where string anywhere for calling forms, reports, and doing aggregate funtionsNow you probably will want to tweak it, but this gives an idea.

field names
strManufacturer
strScale
blnHasAutograph

control names
cmbManufacturer
cmbScale
optGrpAutograph

global variable
glblWhere

Private Sub cmdStrWhere_Click()
    MsgBox fncCriteria
    DoCmd.OpenForm "frmModels", , , fncCriteria
End Sub

Public Function fncCriteria() As String
    On Error GoTo errLbl
    Dim strWhere As String
    Dim manufacturerWhereClause As String
    Dim scaleWhereClause As String
    Dim autographWhereClause
    Dim ANDClauseStarted As Boolean
    
 
    If Not Trim(Me.cmbManufacturer & " ") = "" Then
      ANDClauseStarted = True
      manufacturerWhereClause = "strManufacturer = '" & Me.cmbManufacturer & "'"
    End If
        
    If Not Trim(Me.cmbScale & " ") = "" Then
      ANDClauseStarted = True
      scaleWhereClause = "strScale = '" & Me.cmbScale & "'"
    End If
   
    Select Case Me.optGrpAutograph
    Case 1
      autographWhereClause = "(blnHasAutograph = False)"
    Case 2
      autographWhereClause = "(blnHasAutograph = True)"
    Case 3
      autographWhereClause = ""
    End Select

    If Not manufacturerWhereClause = "" Then
      strWhere = manufacturerWhereClause
    End If
    If Not scaleWhereClause = "" Then
      strWhere = strWhere & " AND " & scaleWhereClause
    End If
    If Not autographWhereClause = "" Then
      strWhere = strWhere & " AND " & autographWhereClause
    End If
    
    If Left(strWhere, 4) = " AND" Then
      strWhere = Mid(strWhere, 5)
    End If
    glblWhere = strWhere
    fncCriteria = strWhere
    Exit Function
errLbl:
   MsgBox Err.Number & "  " & Err.Description
End Function

 

[cdl1701]

So after tweeking this I just place this code behind a command button?

 

[MajP]

Just drop the function in the forms code. For demo purposes I put this in behind the command button.

Private Sub cmdStrWhere_Click()
MsgBox fncCriteria
DoCmd.OpenForm "frmModels", , , fncCriteria
End Sub

but once the function is built you can use it differenct ways. This is how I normally use it:
1) I would call this form "frmWhere"
2) then before opening an output form, I would call this form
4) then when I close this form I open the output form

Public sub filteredOutput ()
docmd.openform "frmWhere",,,,acdialog

'since the form frmWhere is dialog the code stops
'executing until the frmWhere is closed
'the functions sets a global variable "gblWhere"
'now open your other form filtered on your glblWhere
docmd.openform "frmOutPut",,,glbWhere
end sub

There are lots of variations the key is making the string, and either returning it or setting a glbl variable to be used later.

This is one approach. I am sure other people will have other techniques. But like I said, I am usually making far more complicated where strings.

 

[cdl1701]

Okay I am going to give this a try tonight.. one more question. when I put this code in the form will this be a new blank bound or unbound form or an existing form that I am already using?

 

[MajP]

The function goes on the form that has the search controls. It could be new, bound or unbound. I think you could use the form that you were using. I use a option group for autograph. Most likely the controls are unbound, because those controls are not putting values in a table but used to search.

 

[drrocket5292]

MajP,
I'm pretty new to databases and VBA but I'm trying to learn as much as I can about them and have been reading through the threads to get a good idea on how to solve real problems. I was wondering, if you dont mind and have the time, if you could explain a couple of things about your code so that I can understand programming a little better. I understand the majority of it like trimming off the space to see if theres anything selected but I dont understand A)why you would need the ANDClauseStarted boolean B)how the function knows which autographwhereclause case is the right one (i know the field can only be true, false or nothing but you can refer to a field in a string like that and it'll be recognized?) and C) if you have the if clauses for the strwhere string, why is there a need for the left(strwhere, 4) and mid(strwhere, 5) code? Thanks for enlightening me. I really appreciate it.

 

[MajP]

A) Nice catch, It was a test. The AndClauseSstarted is just to confuse you. No, actually that was a cut and paste job. Sorry about that. That all can go by. The reason that was in there originally was that I was using multiple select list boxes. I could select more than one item in a list. Actually it should have been called "ORClauseStarted". Something like

strManufacturer = 'Matel' OR strManufacturer = 'Hasbro'
AND strScale = '1:50'

This version can not do that.
B) I used three Radio Buttons inside an Option Group called optGrpAutograph. An Option Group with three options, returns the value 1,2, or 3. So this might make more sense

Select Case Me.optGrpAutograph.value
Case 1 '(The option group has a value of 1, First Button Selected)

Personally, I do not use Check Boxes in tri-state mode to return a null. It just is to confusing. You could if you want.
C) This strips off the word "And" at the beginning of the string in case you do not pick a manufacturer. If you do not choose a manufacturer you could get something like

AND strScale = '1:50'

 

[MajP]

One thing. On the Trim(variable & " ") = ""
See this thread705-1197284
Just disregard everything I stated wrong in that post. But we beat the horse on that issue and why it catches so many different cases. You will learnt the subtle differences between Null, Empty, "", " " etc.

 

[cdl1701]

Im sorry I just can not get this to work.. I am afraid that this is just a little over my head. I edited the code to put in the fieldnames that are on my form and I placed the code on the form. I then added a control button with the code for it but I am not exactly sure if I am running everything correctly. Here is what I am using. Thanks for the help MajP

Table:
CollectionTable1

Form:
FrmSearch (unbound)

Fields:
Manufacture (combo Box)
Scale (Combo Box)
Year (combo Box) this format is like 2005, 2006 ect. and I have the format set to General Number
ModelCar (combo Box)

Check Boxes:
ChkAutograph
ChkTransporter
ChkBeanieBears
ChkDolls
ChkActionFigure
ChkPosters
ChkTins
ChkPlates
ChkCokeBottles
ChkOrnaments
ChkClocks
ChkAirplanes
ChkGlassware
ChkKnives
ChkCerealBoxes
ChkClothing

 

[drrocket5292]

thanks for the reply MajP, I should have caught the whole left, mid code, it makes perfect sense. About the other thread, a text field can only be a null if its never been edited, is that right? Also a trimmed null value = "" and a trimmed "" or " " also = ""? Thats pretty much what I took away from that thread and I just wanted to make sure thats right. Its funny how much more practical stuff you learn from reading posts then you do from reading a text book.

 

[cdl1701]

If I wanted to use one check box control for each check box item do I still need

Select Case Me.optGrpAutograph
Case 1
autographWhereClause = "(blnHasAutograph = False)"
Case 2
autographWhereClause = "(blnHasAutograph = True)"
Case 3
autographWhereClause = ""
End Select
or would I use someting like this?

If Not Trim(Me.ChkAutograph & " ") = "" Then
ANDClauseStarted = True
autographWhereClause = "Autograph = '" & Me.ChkAutograph & "'"
End If

And set the Dim autographWhereClause as String?

 

[MajP]

Controls grouped together in an option group work different then seperate controls. You could do it either way, but I think the logic is confusing with seperate controls and requires more complicated code. In an option group you can only select one choice.

Lets say you had two checkboxes.
chkHasAutograph
chkNoAutograph

Four combinations are possible. What does it mean if they are both checked? What does it mean if neither is checked?

Lets say you went with three
chkHasAutograph (1)
chkNoAutograph (2)
chkEither (3)

There are 8 combinatins: None selected, 1 & 2, 1& 3, 2 & 3, 1, 2, 3, and all selected. What does all unchecked mean. Was does "has autograph" and "no autograph" selected, but not either mean?

 

[cdl1701]

What I could do is break out the Autograph check box from the others and make it the only one as a seperate control. The other 15 could be placed in an option group since really only one of those would be selected at a time. I mean really you could not have a Beanie Bear be a Coke Bottle at the same time but you could have a Beanie Bear be autographed. Does that sound more feasable?

 

[MajP]

Not sure what you are doing now. Originally you said

Manufacturer Scale Autograph

Now it sounds like you have a "typeItem" field (Beanie Baby, Coke Bottle, etc.). You could build a check for each item type, but the problem with that is when you add an item type (i.e. "beer can") you have to redesign the form. That is why I recommend a combobox.
Again the problem with one checkbox is there is really three choice.

records with an autograph
records without autograph
and both.

For ease, I am still pushing for the Autograph option group . However, you could have other option groups such as "item type". Each option group will be in its own frame.

The other problem with building an "item type" option group is that an option group returns an integer. So now you have to write code to change the choice "Beanie Baby" from a value of 3 to a string of "Beanie Baby".

So what are the fields that you want to query?

 

[cdl1701]

As you can see below I have a mix of combo boxes and check boxes. I need the query to be able to report based on any one or all of these items:

CmbManufacture
CmbScale
CmbYear
CmbModelCar
ChkAutograph

and only one of these items:

ChkTransporter
ChkBeanieBears
ChkDolls
ChkActionFigure
ChkPosters
ChkTins
ChkPlates
ChkCokeBottles
ChkOrnaments
ChkClocks
ChkAirplanes
ChkGlassware
ChkKnives
ChkCerealBoxes
ChkClothing

....on any given search and then fill out a Query results form showing all found records matching the selected criteria. Also on the results form there is one other field from the table that will be filled in that are not part of the query. That one is:

Picture

This field just stores the path to the picture of the item.

Here is a full list of the Tables, fields, forms & controls in my DB. Maby it will make more sence if you see it like this? Something else that I just thought of that would probably make this all a world easier would be to put the items that are check boxes into a Misc combo box... What do you think?

Table:
CollectionTable1

Fields (in table):
Key (auto number)
Manufacture (text)(look up)
Scale (text)(look up)
Year (text)(look up)
ModelCar (text)(look up)
Picture (text)
Autograph (yes/no)
Transporter (yes/no)
BeanieBears (yes/no)
Dolls (yes/no)
ActionFigure (yes/no)
Posters (yes/no)
Tins (yes/no)
Plates (yes/no)
CokeBottles (yes/no)
Ornaments (yes/no)
Clocks (yes/no)
Airplanes (yes/no)
Glassware (yes/no)
Knives (yes/no)
CerealBoxes (yes/no)
Clothing (yes/no)

Form:
FrmSearch (unbound)

Controls:
CmbManufacture
CmbScale
CmbYear
CmbModelCar
ChkAutograph
ChkTransporter
ChkBeanieBears
ChkDolls
ChkActionFigure
ChkPosters
ChkTins
ChkPlates
ChkCokeBottles
ChkOrnaments
ChkClocks
ChkAirplanes
ChkGlassware
ChkKnives
ChkCerealBoxes
ChkClothing

 

[MajP]

Well, the first thing anyone is going to say is that this is not normalized. Either you fix it or work around it.

should be

Fields (in table):
Key (auto number)
Manufacture (text)(look up)
Scale (text)(look up)
Year (text)(look up)
ModelCar (text)(look up)
strItemType (text)

Item type then has values of

Autograph )
Transporter
BeanieBears
Dolls
etc.

Do you want to fix it?

 

[cdl1701]

Yes I want it all done right. So In place of all of my check boxes I create a Field StrItemType as text with look up right?