Selecting only some of the drop down options

I have a form set up that uses a query as a records source.  I have 5 
different drop downs to choose from with the criteria in the query set up as 
like below to link to the form

>=[Forms]![Selected Criteria Form]![Year]


By setting up this way, you need to select something from all 5 drop down 
lists for it to work.  How would I set it up where I could select anywhere 
from 1 to 5 of the drop down options?



0
Utf
4/24/2007 6:30:01 PM
access.forms 6864 articles. 2 followers. Follow

3 Replies
486 Views

Similar Articles

[PageSpeed] 5

Not necessarily.  There is a technique you can use that will solve that 
problem; however, I need to know if the drop downs are on the form you are 
filtering, or are you making selection that will be used to open a new form.  
The technique is a little different depending on what you want to do.  
However, here is the basic principle.  You will need a function that will 
build the criteria for you.  the idea is that you only add to the criteria 
for the combos that have a value.

Function BuildWhereString() As String
Dim strWhere As String

    If Not IsNull(Me.Combo1) Then
        strWhere = "[FirstField] = '" & Me.Combo1 & "'"
    End If

    If Not IsNull(Me.Combo2) Then
        If Len(strWhere) > 0 Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "[SecondField] = " & Me.Combo2
    End If

    If Not IsNull(Me.Combo3) Then
        If Len(strWhere) > 0 Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "[SecondField] = " & Me.Combo3
    End If

    If Not IsNull(Me.Combo4) Then
        If Len(strWhere) > 0 Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "[SecondField] = " & Me.Combo4
    End If

    If Not IsNull(Me.Combo5) Then
        If Len(strWhere) > 0 Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "[SecondField] = " & Me.Combo5
    End If

    BuildWhereString = strWhere

End Function

It will return a string that would be the same as an SQL WHERE clause 
without the word WHERE, which is what you would need in any situation.  Note, 
you will need to adjust the code to use real names and correct syntax for the 
data type.
-- 
Dave Hargis, Microsoft Access MVP


"Supe" wrote:

> I have a form set up that uses a query as a records source.  I have 5 
> different drop downs to choose from with the criteria in the query set up as 
> like below to link to the form
> 
> >=[Forms]![Selected Criteria Form]![Year]
> 
> 
> By setting up this way, you need to select something from all 5 drop down 
> lists for it to work.  How would I set it up where I could select anywhere 
> from 1 to 5 of the drop down options?
> 
> 
> 
0
Utf
4/24/2007 6:48:03 PM
The drop downs are on the form I am filtering.


"Klatuu" wrote:

> Not necessarily.  There is a technique you can use that will solve that 
> problem; however, I need to know if the drop downs are on the form you are 
> filtering, or are you making selection that will be used to open a new form.  
> The technique is a little different depending on what you want to do.  
> However, here is the basic principle.  You will need a function that will 
> build the criteria for you.  the idea is that you only add to the criteria 
> for the combos that have a value.
> 
> Function BuildWhereString() As String
> Dim strWhere As String
> 
>     If Not IsNull(Me.Combo1) Then
>         strWhere = "[FirstField] = '" & Me.Combo1 & "'"
>     End If
> 
>     If Not IsNull(Me.Combo2) Then
>         If Len(strWhere) > 0 Then
>             strWhere = strWhere & " AND "
>         End If
>         strWhere = strWhere & "[SecondField] = " & Me.Combo2
>     End If
> 
>     If Not IsNull(Me.Combo3) Then
>         If Len(strWhere) > 0 Then
>             strWhere = strWhere & " AND "
>         End If
>         strWhere = strWhere & "[SecondField] = " & Me.Combo3
>     End If
> 
>     If Not IsNull(Me.Combo4) Then
>         If Len(strWhere) > 0 Then
>             strWhere = strWhere & " AND "
>         End If
>         strWhere = strWhere & "[SecondField] = " & Me.Combo4
>     End If
> 
>     If Not IsNull(Me.Combo5) Then
>         If Len(strWhere) > 0 Then
>             strWhere = strWhere & " AND "
>         End If
>         strWhere = strWhere & "[SecondField] = " & Me.Combo5
>     End If
> 
>     BuildWhereString = strWhere
> 
> End Function
> 
> It will return a string that would be the same as an SQL WHERE clause 
> without the word WHERE, which is what you would need in any situation.  Note, 
> you will need to adjust the code to use real names and correct syntax for the 
> data type.
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "Supe" wrote:
> 
> > I have a form set up that uses a query as a records source.  I have 5 
> > different drop downs to choose from with the criteria in the query set up as 
> > like below to link to the form
> > 
> > >=[Forms]![Selected Criteria Form]![Year]
> > 
> > 
> > By setting up this way, you need to select something from all 5 drop down 
> > lists for it to work.  How would I set it up where I could select anywhere 
> > from 1 to 5 of the drop down options?
> > 
> > 
> > 
0
Utf
4/24/2007 7:08:02 PM
Ok, then use the function I provide earlier, then use that string to filter 
the form:

Me.Filter = strWhere
Me.FilterOn = Trye

-- 
Dave Hargis, Microsoft Access MVP


"Supe" wrote:

> The drop downs are on the form I am filtering.
> 
> 
> "Klatuu" wrote:
> 
> > Not necessarily.  There is a technique you can use that will solve that 
> > problem; however, I need to know if the drop downs are on the form you are 
> > filtering, or are you making selection that will be used to open a new form.  
> > The technique is a little different depending on what you want to do.  
> > However, here is the basic principle.  You will need a function that will 
> > build the criteria for you.  the idea is that you only add to the criteria 
> > for the combos that have a value.
> > 
> > Function BuildWhereString() As String
> > Dim strWhere As String
> > 
> >     If Not IsNull(Me.Combo1) Then
> >         strWhere = "[FirstField] = '" & Me.Combo1 & "'"
> >     End If
> > 
> >     If Not IsNull(Me.Combo2) Then
> >         If Len(strWhere) > 0 Then
> >             strWhere = strWhere & " AND "
> >         End If
> >         strWhere = strWhere & "[SecondField] = " & Me.Combo2
> >     End If
> > 
> >     If Not IsNull(Me.Combo3) Then
> >         If Len(strWhere) > 0 Then
> >             strWhere = strWhere & " AND "
> >         End If
> >         strWhere = strWhere & "[SecondField] = " & Me.Combo3
> >     End If
> > 
> >     If Not IsNull(Me.Combo4) Then
> >         If Len(strWhere) > 0 Then
> >             strWhere = strWhere & " AND "
> >         End If
> >         strWhere = strWhere & "[SecondField] = " & Me.Combo4
> >     End If
> > 
> >     If Not IsNull(Me.Combo5) Then
> >         If Len(strWhere) > 0 Then
> >             strWhere = strWhere & " AND "
> >         End If
> >         strWhere = strWhere & "[SecondField] = " & Me.Combo5
> >     End If
> > 
> >     BuildWhereString = strWhere
> > 
> > End Function
> > 
> > It will return a string that would be the same as an SQL WHERE clause 
> > without the word WHERE, which is what you would need in any situation.  Note, 
> > you will need to adjust the code to use real names and correct syntax for the 
> > data type.
> > -- 
> > Dave Hargis, Microsoft Access MVP
> > 
> > 
> > "Supe" wrote:
> > 
> > > I have a form set up that uses a query as a records source.  I have 5 
> > > different drop downs to choose from with the criteria in the query set up as 
> > > like below to link to the form
> > > 
> > > >=[Forms]![Selected Criteria Form]![Year]
> > > 
> > > 
> > > By setting up this way, you need to select something from all 5 drop down 
> > > lists for it to work.  How would I set it up where I could select anywhere 
> > > from 1 to 5 of the drop down options?
> > > 
> > > 
> > > 
0
Utf
4/24/2007 7:12:01 PM
Reply:

Similar Artilces:

Selecting all images in spreedsheet at once
Hi, I have a spreadsheet that runs a macro that lets me add images to spreadsheet from a particular folder and position them following particular grid. I use it to make lists of products to be made in particular day. But i need to resize the images to fit the grid afte the fact before i can print the list. There are over 2000 images in the folder, so modifying the origina images to fit isnt really an option. Currently, I am selecting all th images by hold shift and clicking. Then i resize them all using forma picture>size tab. This works well. Except that if i accidently mi...

IDENTITY INSERT issue with INSERT INTO..SELECT
Hi, Here's my DML statements that duplicates records from a resultset qualfying to GESPRAECH_ID = @Quell_Gespraech_ID Column [TB_GESPRAECHPARTNER].POS_ID has its property IDENTITY set to true. SET IDENTITY_INSERT [TB_GESPRAECHPARTNER] ON INSERT INTO [TB_GESPRAECHPARTNER] ([GESPRAECH_ID] ,POS_ID ,[KUNDENGRUPPE_ID] ,[KUNDE_INTERN_ID] ,[KUNDE_EXTERN_ID] ,[VERLAGSNUMMER] ) SELECT @Gespraech_id_Neu , POS_ID ,[KUNDENGRUPPE_ID] ,[KUNDE_INTERN_ID] ,[KUNDE_EXTERN_ID] ...

same options for multiple charts
I have monthly pie charts which are in separate sheets that are updated at the end of the month. I would like each pie slice to maintain consistency with color throughout the months. How do I go about having the same options for every single pie chart I create without having to invidiviually go in and change each chart every month? I also have a YTD pie chart which I would like to be updated every time I add data to the new month's chart. Is there a way this can be automatically updated? PLEASE HELP! Thanks!! What kind of options? What changes about the data or chart that cause...

Drop Down Lists #9
I am trying to compare Region, Person, Year growth rate for a three year span in 19 different categories in two different colums. In my Excel Spreadsheet I am using Drop Down Validation list, (Region 1 2007 vs Region 1 2008,etc.) in two different columns, with the rate (percentage of growth) in a column between them. The problem is what formula or combination of formulas do I use to get different years when changed to reference the same cell? Example: 2006 vs 2007 answer in one cell, then if I switch 2005 vs 2007 I want the answer to appear in same cell because the categoires in the clo...

Combo Box selection returns a Date() in another field
Can anyone help me? I had this figured out in MS Access 2003 but now I'm working in 2007 and my previous code doesnt work. I have a form in which I have a Combo Box named "Status1" and the Control Source is "Status". The Row Source is "Pending";"Active";"On Hold";"Completed". Type is Value List. I also have a field named "completed" who's Control Source is "Date Completed" I want a AfterUpdate when the Combo Box is set to "Completed" the Date Completed fills in todays date. But ...

WHY select the first item didn't receive OnPaint in LISTCTRL!
hello everyone, I write a custom listctrl inherit from the CListCtrl( MFC ). I don't use CUSTOMDRAW and OWNDRAW, and rewrite the OnPaint fucntion. The OnPaint function like this: void CListCtrlST::OnPaint() { CPaintDC dc(this); // device context for painting ///////////////////////////////////////////////// CRect rtList; GetClientRect( &rtList ); CDC* pDC = &dc; CMemDC memDC( pDC, &rtList ); // double buffer..... memDC.SetBkMode( TRANSPARENT ); // don't use the system paint, because it's too slow... // if you resize the listctrl...

Select on current date
Hi all, having a table in SQL Server 2000 with a DateTime field and some values like: ReferenceDate 2009-05-26 22:30:00.000 2009-05-27 12:30:00.000 2009-05-27 22:10:00.000 2009-05-30 22:40:00.000 2009-05-31 14:55:00.000 how can I write a Select that gets records that have DateReference = getdate()? So records where this field fall inside actual date. Thanks in advance. Luigi Hi SELECT * FROM tbl WHERE ReferenceDate>=GETDATE() AND ReferenceDate<DATEADD(d,1,GETDATE()) Note if you have huge table to check for ranges ,created clustered index on Referen...

RichTextBox.selection
IHello I would like to change the font size, color, name, when I click on a word and reurn it back to its previous style if i click it again. I try this but it works only if I make a double click on the word... When i want to select a piece of word without an apostrophe for example it crashes: What's wrong? Private Sub RTB_txt_SelectionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles RTB_txt.SelectionChanged If RTB_txt.SelectionFont.Name = ("Tahoma") And _ RTB_txt.SelectionFont.Size = "12" And _ RTB_txt....

Excel Drop Down box or check box data to Excel Database
I desperately need help with this! I have an Excel form which I have included check boxes, drop down boxes and Yes no. How do I incorporate the data into the Excel database? Maybe you could use some of the techniques that Debra Dalgleish shows at: http://contextures.com/xlUserForm01.html TotallyConfused wrote: > > I desperately need help with this! I have an Excel form which I have > included check boxes, drop down boxes and Yes no. How do I incorporate the > data into the Excel database? -- Dave Peterson Hi, You can send me the workbook and I may be able to assist. ...

Sub Procedure for File Save As PDF or XPS menu option
Hi, I need to give a message to the user once he/she clicks on the File Save As PDF or XPS menu option in Word 2007. I have tried with Sub FileSavePdfOrXps() but it is not working. Can any one let me know which is the exact sub procedure which is invoked? Thanks in advance. You haven't placed any of the code you tried in the Sub, so it would be hard to troubleshoot. Have you used the Office 2007 Developer Reference on MSDN? It's really great. Perhaps you can customize what happens when someone uses that command--there may be other ways to accomplish your object...

Pause Option Disabled in Media Player
Dear Team, I implemented DirectShow Source Filter SHOUTCast protocol in WinCE 6.0. The filter works fine and I can listen to the Radio for any number of time. What I noticed is that the PAUSE option in the Media Player of WinCE6.0 is not getting enabled if my filter gets loaded. I can set the option while playing normal .mp3 files. Can somebody provide a pointer for location in Direct Show Filter how the state should be implemented. Regards. ...

Need a simple way to have only one mode per link with an option
I use items like the following to display links in an iFrame within my page. <asp:TreeNode NavigateUrl="http://en.wikipedia.org/wiki/Church" Target="LinksFrame" Text="Wikipedi Item on Church"></asp:TreeNode> I can envision situations where the iFrame might be too small and the user would like to display in another window. I'd like to have a check box to allow that option. On way I could implement is to have two statements for each link and use the check box to select the appropriate one. Is there a simple way to have only o...

Macro to select all and name range
I have a workbook with some macros that work on another workbook ("Data.xls"). One thing I want the macro to do, is to select everything in a worksheet named Survey and name the range "Database". This would be equivalent to go to the Data workbook and Survey worksheet, hitting Ctrl+Shift+End and enter Database in the name box. I can't record this action because the code specifies the cells included in the current version of Data.xls, and the number of rows will change. What code can I use to accomplish this? Richard Richard, Try this With Workbooks("D...

Junk Mail Filter Option
I have my junk mail filter options set to "Safe List Only" but still receive junk mail in the inbox from senders not on my white list. Specifically, the OEM Software type messaes and the Low Cost Drug messages. Is this a known issue? Or is there something else that needs to be done that I haven't done as of yet. I have verified my white list and it only includes senders whom I've approved. Thanks in advance, Dan is your address on the safe list and do the spammers have your address in the from field? (it's a common tactic) -- Diane Poremsky [MVP - Outlook] A...

Folder Options not taking effect
Hi, Regarding the Tools > Folder Options... dialog, is there any reason a change made there would not "stick"? I like to see my file extensions as it helps me identify a file quickly, but everytime I deselect "Hide file extensions for known file types", click Apply and OK, once the dialog closes, the setting is re-applied like I hadn't done anything. When I open the folder options dialog again, I see that it's selected again. The same is true of "Hide protected operating system files". What's wrong with my installation? Does anyon...

OL 2002 Appt. Drop Down issue
When I click on the down arrow for an appointment time, the dropdown box gives me a scrolling list of times to select from, but as soon as I do a mouseover on one of the times, the dropdown box retracts. This forces me to select appointment times using the arrow and tab keys, which is very annoying. I'm running Outlook 2002 in WinXP Professional. I've reinstalled the mouse driver, video driver, etc. No luck. The problem only occurs in Outlook, and only in the Calendar section. Any help is much appreciated! ...

Using values in drop down boxes in formulae
Is there any way to use the value shown in a drop down box in a sum formula. I am trying to work out the total of a column including whatever number is shown in the drop down box. The formul at the moment is =SUM(C16:C28) with the drop down box positioned on cell C28. Is there any way of linking the drop down box to the cell so the number shown is added as well? Thx --------- www.coffeecozy.com Use your Bodum and give up cold coffee for good! Hi what drop down boy are you suing ('Data - validation')? -- Regards Frank Kabel Frankfurt, Germany "44203 - ExcelForums.com" <...

read receipt options
How can I set Outlook to tell me when an my emails are read without requesting a read receipt from the recipient >How can I set Outlook to tell me when an my emails are >read without requesting a read receipt from the recipient You can't. Read Receipt IS the mechanism used to tell you when your message had been opened (not read, by the way. Nothing can tell you if it's been read and, more importantly, understood). -- Brian Tillman Smiths Aerospace 3290 Patterson Ave. SE, MS 1B3 Grand Rapids, MI 49512-1991 Brian.Tillman is the name, smiths-aerospace.com is the domain. I don...

Word 2007 ink options
I have a 'pen mouse' (A 'bamboo tablet' if this makes things clearer) and have only just discovered that on the word 2007 toolbar there are 'ink' options which allow me to scribble and draw in word documents. Every time I start Word the pens go back to default settings - e.g. the felt tip pen is always red and a certain width. Is there anyway to change the default settings on this particular function? Thanks -- doghouseriley ...

Select range from ActiveCell do to Lastcell in ColumnA
Range("A10:M" & Range("A10000").End(xlUp).Row.Cut will cut a range from Cell A10 across to Column M and down to last cell in Column A How do you replace reference to Cell A10 with ActiveCell in Column A? -- Thank you Aussie Bob C Little cost to carry knowledge with you. Win XP P3 Office 2007 on Mini Mac using VMware. Dim lRow as long Dim myRange as Excel.range Dim aWS as excel.worksheet set aWS = activesheet Set myRange = aws.Range("A10") lRow = aws.cells(aws.rows.count,myrange.row).end(xlup).row set myrange = myrange.resize(lrow-myrang...

Excel 2007 Selection Tool Broken?
I am using Excel 2003 at work and Excel 2007 at home. I have a file that uses hundreds of text boxes and images. At work, using Excel 2003 I can use the “Selection” tool to quickly “lasso” several text boxes or images to work on them, move them, delete them etc. At home it is nearly impossible to use the selection tool in Excel 2007 to work on multiple objects. Especially if the objects are spaced more than one screen apart. I reported this problem in Office Online –“Excel Application Errors” but had no response. http://www.microsoft.com/office/community/en-us/default.mspx...

Reading data in parts and compound path selecting
I have two questions that are related, so I’m asking them in this single post. 1. I have large XML files that have to be read and edited, so I want to be able to read into memory a limited range of xmldata, and edit it; then save it out to a new file and move on to the next portion. I’ve heard it can be done, but how? (as you an see from my vocabulary, I’ve been working with Xpath and DOM) 2. My XML data consists of library books and related information about each book. A simplified view of the node path looks like this: ...

"The page can not be dispalyed" in password change option
Hi all, I tried to enable the password change option in FE/BE exchange 2003 servers. I created iisadmpwd virtual directory and enable password registy key in FE and BE servers. But, when I click change password, I got this error " the page can not be dispalyed". BTW, I had URLSCAN installed but I removed .htr extension. I am out of my mind now and can anyone shred light on me? Thanks. ************************************ The page cannot be found The page you are looking for might have been removed, had its name changed, or is temporarily unavailable. -------------------...

Lock worksheet, based on dropdown selection prior worksheet
Hi, I am creating a workbook, where I have several worksheet that are labeled with the same first 7 characters "ab123 -". The user selects one of these sheets from a dropdown in the first worksheet. I provide a conditionally formatted hyperlink within worksheet A, based on their selection, but for quality control want an additional step that either locks an incorrect selection or presenting a msgbox to re-direct. Can this be done? Thanks Michele What is conditionally formatted hyperlink? How will Excel know if an incorrect selection has been made from the dropdown? Gord Di...

selecting range with "End + direction"
Hi, I would like to change the option that selects the range with regard to the number of non empty cells in the row or column of the base cell when using "End + arrow". Example I have A1:A100 filled, and b1:b50 filled. If my selected cell is A1, with Shift+Right and then End+shift+down, the selected range is a1:b100, whereas i would like to have a1:b50 selected. If anyone knows how to change this option, it would be very useful for me. Thanks in advance mehdi Can't you just tab to B1 then Shift-End-Down -- HTH RP (remove nothere from the email address if mailing d...