Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    May 2014
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DoCmd Open report with multiple Where clauses

    I have a form that I would like users to make a selection on, which filters a report, but I can't work out the VBA

    The first box is a drop down called SchoolName which allows them to select the school

    The next two boxes are the dates they want to filter the report for TxtStartdate and txtEnd date,

    when the users has selected dates the report will filter the form to show all training courses attended by that school between the dates that have been selected.

    I can get it to filter on school or between the dates but not both?

    P.s I'm new to VBA

    Help?

    Thanks

    Kelly

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,235
    Thanks
    202
    Thanked 796 Times in 729 Posts
    Kelly,

    You want something like this:
    Code:
       Dim zWhere as String
    
       zWhere = "[SchoolName] = " & Form!YourFormName!SchoolName & " And  [TxtStartDate] >= " & _
                         Form!YourFormName!TxtStartDate & " And [TxtEndDate] <= " & _
                         Form!YourFormName!TxtEndDate
    
       DoCmd.OpenReport "Your Report Name Here", acViewPreview, zWhere
    Note: Items in [ ] are your db field names.
    HTH
    Last edited by RetiredGeek; 2014-07-07 at 09:49.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Azar (2014-09-19)

  5. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,537
    Thanks
    0
    Thanked 23 Times in 23 Posts
    RG's zWhere may need to be changed, especially if SchoolName is not a number field, and depending on where you live you may have format the dates as well, viz:
    zWhere = "[SchoolName] = " & Chr(34) & Form!YourFormName!SchoolName & Chr(34) & " And [TxtStartDate] >= " & _
    "#" & Format(Form!YourFormName!TxtStartDate,"mm/dd/yyyy") & "# And [TxtEndDate] <= " & _
    "#" & Format(Form!YourFormName!TxtEndDate,"mm/dd/yyyy") & "#"

    I think RG has made a mistake in the date check, I think he meant to show:
    " AND RecordDate Between #" & Format(Form!YourFormName!TxtStartDate,"mm/dd/yyyy") & "# And #" & Format(Form!YourFormName!TxtEndDate,"mm/dd/yyyy") & "#"
    where recordDate is the name of the date field in the reports source code.

    so zWhere should be:
    zWhere = "[SchoolName] = " & Chr(34) & Form!YourFormName!SchoolName & Chr(34) & " AND RecordDate Between #" & Format(Form!YourFormName!TxtStartDate,"mm/dd/yyyy") & "# And #" & Format(Form!YourFormName!TxtEndDate,"mm/dd/yyyy") & "#"

    Not treading on toes RG.

  6. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,235
    Thanks
    202
    Thanked 796 Times in 729 Posts
    Patt,

    Ouch! ROTFLOL.gif

    Not a problem. Thanks for correcting the code.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  7. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Azar (2014-09-19)

  8. #5
    New Lounger
    Join Date
    May 2014
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Guys

    Thanks for your replies but neither code is working. I am based in the UK if that makes a difference. It tells me that it is an invalid outside procedure?

    maybe i didn't explain it correctly, i have a list of training courses that have been attended and there is a db field called 'course date' there is also a DB field called school.

    on the form there is a field called schoolname to select a school and txtstartdate and txtenddate so they can pick any courses dates that have been attended during that period?

    the form is called 'selectSchoolforTrainingAttReport and the report is called 'attended training courses'

    thank you

    kelly

  9. #6
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,015
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I would suggest something like this on an open report button

    This allows for ALL or NONE of the filter components to be entered

    It is a bit more long winded, but more flexible

    Code:
    Dim strFilter as string, strThisPart as String
    Dim conSPM = """"
    
    'Build Filter Components from data on the form
    'I could have done it in a shorter way, but this is easier to debug
    'It is always worth considering an err trap as well
    
    If Not IsNull(schoolname) Then
    	strThisPart = " [School] = " & conSPM & schoolname & conSPM & " "
    	'Build Filter either this is the first bit or we need to add
    	If strFilter = "" Then
    		strFilter = strThisPart
    	Else
    		strFilter = strFilter & " AND " & strThisPart
    	End If
    End If
    
    If Not IsNull(txtstartdate) Then
    	strThisPart = " [course date] >= #" & Format(txtstartdate,"mm/dd/yyyy") & "# "
    	If strFilter = "" Then
    		strFilter = strThisPart
    	Else
    		strFilter = strFilter & " AND " & strThisPart
    	End If
    End If
    
    If Not IsNull(txtenddate) Then
    	strThisPart = " [course date] <= #" & Format(txtenddate,"mm/dd/yyyy") & "# "
    	If strFilter = "" Then
    		strFilter = strThisPart
    	Else
    		strFilter = strFilter & " AND " & strThisPart
    	End If
    End If
    
    'Now open the Report and pass it the filter
    If strFilter = "" Then
           DoCmd.OpenReport ReportName:="attended training courses"
    Else
           DoCmd.OpenReport ReportName:="attended training courses", WhereCondition:=strFilter 
    End iF
    This could have been done more quickly, but since you are new to VBA,
    have a look at it and see if you can refine it.
    Andrew

  10. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,537
    Thanks
    0
    Thanked 23 Times in 23 Posts
    By the way Kelly, would you post here what you used for a WHERE clause, and let us see what you tried.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •