Database Form Query

M

mogul0212

Registered User.
Local time
Today, 16:30
Joined
May 19, 2006
Messages
116
I've got a form that has drop down menus to select info from a particular table for search criteria in Access 2003. If I select a certain set of data I get results in from query. If I happen to leave one of the drop down menus blank than I get no results, when I know there are results availabe. What type of language do I need to put in the criteria part of the query to allow a blank search of one or more of my drop down menus? Say I have 10 drop down menu criteria, and I want to use only 3 of them as criteria for a particular search, so I select the appropriate items and leave the rest blank. But using the same form, be able to do a different search using any combination of the drop down menus with a specific response or blank to search every respoonse in that field.

That way I can have one form to do multiple types of queries depending on the information you select in the drop down menus. And if the menu is blank, the query assumes any entry in that column is open to results.

Any ideas? I am a newbie to Access, so I'm probably just missing something simple, or I'm trying to do something far more technical then I ever thought it would be.
 
Last edited:
Below is a sample of code that I have in my database. I have a menu with 7 combo boxes that allows for filters on 15 different reports.

Below the code checks all the combo boxes to see if it is null or not, and then if creates the string filter at the very end.

I hope this helps.


Code: 
Private Sub ShowReport(strReportName As String)

    Dim strFilter As String
    
    If IsNull(Me.cmbMonth.value) = False Then
        strFilter = strFilter & _
            "intMonth = " & Me.cmbMonth.value
    End If
    
    If IsNull(Me.cmbAnalyst.value) = False Then
        
        If IsNull(Me.cmbMonth.value) = False Then
            
            strFilter = strFilter & " And "
            
        End If
                    
        strFilter = strFilter & _
            "strReviewerID = '" & Me.cmbAnalyst.value & "'"
        
    End If
  
    
    If IsNull(Me.cmbAdmin.value) = False Then
        
        If IsNull(Me.cmbMonth.value) = False Or _
            IsNull(Me.cmbAnalyst.value) = False Then
            
            strFilter = strFilter & " And "
        
        End If
        
        strFilter = strFilter & _
            "strAdminID = '" & Me.cmbAdmin.value & "'"
        
    End If
    
    If Me.txtStartDate.value <> "" And _
        Me.txtEndDate.value <> "" Then
        
        If IsNull(Me.cmbMonth.value) = False Or _
            IsNull(Me.cmbAnalyst.value) = False Or _
            IsNull(Me.cmbAdmin.value) = False Then
            
            strFilter = strFilter & " And "
        
               
        End If
        
'        If strReportName = "rptDaysInQC" Or strReportName = "rptGroupsCompletedHoursName" Or _
'         strReportName = "rptGroupsCompletedHours" Or strReportName = "rptGroupsCompletedByAdmin" Or _
'         strReportName = "rpt8oClockTest" Then
'
'
'
'            strFilter = strFilter & _
'                "dtmQCReceivedStamp >= #" & Me.txtStartDate.Value & " 12:00 AM# " & _
'                "And dtmQCReceivedStamp <= #" & Me.txtEndDate.Value & " 11:59:59 PM#"
'
'        Else
'
'            strFilter = strFilter & _
'                "dtmReturned >= #" & Me.txtStartDate.Value & " 12:00 AM# " & _
'                "And dtmReturned <= #" & Me.txtEndDate.Value & " 11:59:59 PM#"
'        End If
        
    End If
    
     If IsNull(Me.cmbWeek.value) = False Then
    
        
        If IsNull(Me.cmbMonth.value) = False Or _
            IsNull(Me.cmbAnalyst.value) = False Or _
            IsNull(Me.cmbAdmin.value) = False Then
            
'            IsNull(Me.txtStartDate.Value) = False Or _
'            IsNull(Me.txtEndDate.Value) = False Then

             
             strFilter = strFilter & " And "
        End If
         
            strFilter = strFilter & _
            "intWeek = " & Me.cmbWeek.value


         
               
     End If
     
     If IsNull(Me.CmbYear.value) = False Then
    
        
        If IsNull(Me.cmbMonth.value) = False Or _
            IsNull(Me.cmbAnalyst.value) = False Or _
            IsNull(Me.cmbAdmin.value) = False Or _
            IsNull(Me.cmbWeek.value) = False Then _
'            IsNull(Me.txtStartDate.Value) = False Or _
'            IsNull(Me.txtEndDate.Value) = False Then

             
             strFilter = strFilter & " And "
        End If
         
            strFilter = strFilter & _
            "intYear = " & Me.CmbYear.value


         
               
     End If
     
     If IsNull(Me.cmbBuddy.value) = False Then
    
        
        If IsNull(Me.cmbMonth.value) = False Or _
            IsNull(Me.cmbAnalyst.value) = False Or _
            IsNull(Me.cmbAdmin.value) = False Or _
            IsNull(Me.cmbWeek.value) = False Or _
            IsNull(Me.CmbYear.value) = False Then
'            IsNull(Me.txtStartDate.Value) = False Or
'            IsNull(Me.txtEndDate.Value) = False Then

             
             strFilter = strFilter & " And "
        End If
         
            strFilter = strFilter & _
            "strBuddyID = '" & Me.cmbBuddy.value & "'"

          End If
          
            

   
    DoCmd.OpenReport strReportName, acViewPreview, , strFilter
 
selenau837

It sounds like it will. Now being the newbie that I am, It'll take me some time to figure out how to use it! hahahaha
 
mogul0212 said:
selenau837

It sounds like it will. Now being the newbie that I am, It'll take me some time to figure out how to use it! hahahaha
Click to expand...


just change the combo box names I have to the ones you have.

If you can give me the names of yours I can try and adapt it. Also, need the data types of the fields it is using.


PS, I didn't write this, I can't take credit for it, but I have modified it for my uses. It is good if you can get it to work.
 
selenau837

Thanks for your help on this.

As for the data types and fields I have. Here's a list

Field Name : DataType

NC Tool # :Text
Tool Type :Text
Notes :Memo
Max Cut Depth :Text
Tool Material :Text
Shank Diameter :Text
Profile # or Name :Text
Minor Diameter :Text
Board Thickness :Text
NC Division :Text
Tool Description :Text
Profile Type :Text
Tool Vendor Part # :Text
Tool used in Fergus Falls - Veneer :Yes/No
Tool used in Corbin - Thermofoil :Yes/No
Tool used in Vanceburg, KY - Veneer :Yes/No
Tool used in Vanceburg, KY - Wood :Yes/No
Tool used in Arkansas City, KS - Veneer :Yes/No
Tool used in Fergus Falls - Thermofoil :Yes/No

There is alot of criteria there. I know, but all those items are requested by all here that need to search for things. So do what you can. I truly appreciate any help you can provide. I've been banging my head into my desk for too many hours for too many days on this it seems.

Just to let you know, I have my drop down boxes bound so that they are actually look up info from a table, not just a basic combo box. I don't know Access well enough to know if there is a difference for coding purposes.

Thanks again.
 
mogul0212 said:
selenau837

Thanks for your help on this.

As for the data types and fields I have. Here's a list

Field Name : DataType

NC Tool # :Text
Tool Type :Text
Notes :Memo
Max Cut Depth :Text
Tool Material :Text
Shank Diameter :Text
Profile # or Name :Text
Minor Diameter :Text
Board Thickness :Text
NC Division :Text
Tool Description :Text
Profile Type :Text
Tool Vendor Part # :Text
Tool used in Fergus Falls - Veneer :Yes/No
Tool used in Corbin - Thermofoil :Yes/No
Tool used in Vanceburg, KY - Veneer :Yes/No
Tool used in Vanceburg, KY - Wood :Yes/No
Tool used in Arkansas City, KS - Veneer :Yes/No
Tool used in Fergus Falls - Thermofoil :Yes/No

There is alot of criteria there. I know, but all those items are requested by all here that need to search for things. So do what you can. I truly appreciate any help you can provide. I've been banging my head into my desk for too many hours for too many days on this it seems.

Just to let you know, I have my drop down boxes bound so that they are actually look up info from a table, not just a basic combo box. I don't know Access well enough to know if there is a difference for coding purposes.

Thanks again.
Click to expand...

Give me a little bit and I'll see what I can do. I'll try and convert mine for you....or at least give you a head start so you can finish it....

best way to learn it is to do it. That is how I figured mine out. He left..I had no choice but to figure it out.
 
I've attached a copy of my menu...

I need to see yours so I can see how to do it.


I can't follow the info you gave me. :( Sorry honey...
 

Attachments

To the left of the colon is the Field Name, to the right of the colon is the data type.

I can't get a damn screen shot to work for whatever reason right now.

I'm working on try to get you that.
 
selenau837

Got a new keyboard that has a "F Lock" button that turns the F keys and those next to it Off.

Guess my frustrations of banging my head into my desk over this database are starting to show through.

Attached a screen cap for you to see.
 

Attachments

mogul0212 said:
selenau837

Got a new keyboard that has a "F Lock" button that turns the F keys and those next to it Off.

Guess my frustrations of banging my head into my desk over this database are starting to show through.

Attached a screen cap for you to see.
Click to expand...


Holy cow that is alot of criteria. It will be tomorrow before I can do it. I can start it and see if you can finish it if you like, let me do a few..so you can see....

Give me about 20 min.
 
I greatly appreciate any help that you can give me. With just a few of them I could at least get a better idea of what to do.

I'm assuming this code goes into the query somewhere right? But where exactly?
 
Here is a start, this will go in a sub routine that you have that will pass the report Name to this sub.

For instance

ShowReport.Click Event

ShowReport("Your Report Name")


Below once you pass the report name, it then looks at all the combo buttons and etc on that form. and attaches it to strFilter...and goes from there..

First one. ( I went left to right at the top)

It looks at the comand button NCTool, if it has info it will make the Field Name NC Tool equal to the value in the combo box named NCTool.

second one.

if combo button NC Division is not null and combo button NCTTool is not null then it, takes the value from strFilter and attaches and 'and' to it and then attaches the value of NC Divison to it.

If any of them are null, it ignores it and doesn't pass a value to it.

Do you see a pattern here.

You would complete that patter all the way until you have completed all the check boxes and combo boxes on that form.

That is a start, I Hope it helped, if you are still confused I'll try and help you more tomorrow too.

Give it ago and see if you can get it working.
Code: 
Private Sub ShowReport(strReportName As String)

    Dim strFilter As String
    
    If IsNull(Me.cmbNCTool.value) = False Then
        strFilter = strFilter & _
            "NC Tool = " & Me.cmbNCTool.value
    End If
    
    If IsNull(Me.cmbNCDivision.value) = False Then
        
        If IsNull(Me.cmbNCTTool.value) = False Then
            
            strFilter = strFilter & " And "
            
        End If
                    
        strFilter = strFilter & _
            "NC Division = '" & Me.cmbNCDivison.value & "'"
        
    End If
  
    
    If IsNull(Me.cmbToolMaterial.value) = False Then
        
        If IsNull(Me.cmdNCTool.value) = False Or _
            IsNull(Me.cmbNCDDivision.value) = False Then
            
            strFilter = strFilter & " And "
        
        End If
        
        strFilter = strFilter & _
            "Tool Material = '" & Me.cmbToolMaterial.value & "'"
        
    End If

....................
 
selenau837

I know it's a total Newbie questions, but where does the code go exactly?
 
mogul0212 said:
selenau837

I know it's a total Newbie questions, but where does the code go exactly?
Click to expand...


IT would need to go in the module for that form. So you can call it once you have the criteria set, and have it pull your report for you.
 
Here is the full code so you can try it for the first three combo boxes on your form.

Place that code on a click event on that form. Let say.

create a button called "View Report"

on that click event put that code.

MAke sure your combo boxes are named the same as I have them named in the mod. Also, make sure the field names match what I have, if not change either in the code to fit what you have.

Then to run it, use the first three critiera on your form and see if it works.


Code: 
Private Sub ShowReport()

    Dim strFilter As String
    Dim strReportName as string

strReportName = "Put name of report here"

    If IsNull(Me.cmbNCTool.value) = False Then
        strFilter = strFilter & _
            "NC Tool = " & Me.cmbNCTool.value
    End If
    
    If IsNull(Me.cmbNCDivision.value) = False Then
        
        If IsNull(Me.cmbNCTTool.value) = False Then
            
            strFilter = strFilter & " And "
            
        End If
                    
        strFilter = strFilter & _
            "NC Division = '" & Me.cmbNCDivison.value & "'"
        
    End If
  
    
    If IsNull(Me.cmbToolMaterial.value) = False Then
        
        If IsNull(Me.cmdNCTool.value) = False Or _
            IsNull(Me.cmbNCDDivision.value) = False Then
            
            strFilter = strFilter & " And "
        
        End If
        
        strFilter = strFilter & _
            "Tool Material = '" & Me.cmbToolMaterial.value & "'"
        
    End If

DoCmd.OpenReport strReportName, acViewPreview, , strFilter

end sub
 
Ok, thanks, appreciate all the info. I'll play around with this the rest of the day here at work (My least favorite 4 letter word). I really appreciate the help on this. When I have more questions I'll post them here ok?

Again, thanks for your help and effort. Truly appreciate it. Have a good evening.
 
mogul0212 said:
Ok, thanks, appreciate all the info. I'll play around with this the rest of the day here at work (My least favorite 4 letter word). I really appreciate the help on this. When I have more questions I'll post them here ok?

Again, thanks for your help and effort. Truly appreciate it. Have a good evening.
Click to expand...

You're very welcome, and good luck. I'll be watching the forum for the next few days to see if you need any help.
 
Selenau837...

I'm struggling with the whole code and module aspects. I would like my results to come up in a form named "result-Tooling" that will ultimately look like the form "Tooling Information".

I think I modified the code correctly, but when I hit the "view form" button, I get an error saying code is wrong. I'm assuming the field name is wrong since NCTool is actually NC Tool # in the field it is looking up, but I can't add spaces or # sign without a syntax error.

If IsNull(Me.cmbNCTool.Value) = False Then
strFilter = strFilter & _
"NC Tool = " & Me.cmbNCTool.Value
End If

I've attached my database as a zip if you want to take a look if you wish.

I do have the form Search-Tooling set up as a starting point with the 3 criteria with a refresh button and a run macro button that opens the results-tooling form. The NC Tool # is a unique # for each record in the table so that particular entry will be blank 99.99% of the time unless someone wants to specifically see that record.

Appreciate any help you could give. Thanks.
 

Attachments

mogul0212 said:
Selenau837...

I'm struggling with the whole code and module aspects. I would like my results to come up in a form named "result-Tooling" that will ultimately look like the form "Tooling Information".

I think I modified the code correctly, but when I hit the "view form" button, I get an error saying code is wrong. I'm assuming the field name is wrong since NCTool is actually NC Tool # in the field it is looking up, but I can't add spaces or # sign without a syntax error.

If IsNull(Me.cmbNCTool.Value) = False Then
strFilter = strFilter & _
"NC Tool = " & Me.cmbNCTool.Value
End If

I've attached my database as a zip if you want to take a look if you wish.

I do have the form Search-Tooling set up as a starting point with the 3 criteria with a refresh button and a run macro button that opens the results-tooling form. The NC Tool # is a unique # for each record in the table so that particular entry will be blank 99.99% of the time unless someone wants to specifically see that record.

Appreciate any help you could give. Thanks.
Click to expand...

Deary, I don't see what you are talking about in your attached database.

As for fieldnames, it is a VERY bad idea to have fieldnames in your tables with spaces etc. If you want it to be shown like you what, change the caption to it to have spaces and stuff, but not in the actual field name itself.
 
You must log in or register to reply here.

Similar threads

E
Emmanuel Katto : How to Customize MS Access to Look Like a Standalone Application
Replies
15
Views
667
gemma-the-husky
gemma-the-husky
asteropi
Solved I need some help with a find-item button
2
Replies
27
Views
784
Pat Hartman
P
M
Please I need Urgent Assistant
Replies
5
Views
473
Pat Hartman
P
asteropi
Solved Return without GoSub error
Replies
6
Views
1,085
asteropi
asteropi
Y
Linking Table
Replies
7
Views
267
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom