Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Lounger
    Join Date
    Mar 2010
    Location
    UK
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi All,

    Me again,

    Ive hit a snag with a little project im doing, and short of repeatidly running into a wall (which i actually doubt will help anyone, unless i video tape it and send it off to "you've been framed") i thought i would ask here.

    Basically, I want to make a report to show a records where a both start date and end date fall between 1 date range.

    for example, I want to see all of my courses that either start in, or end in January.

    As i am exceptionally lazy when it comes to being an end user, I only want to enter the date range once.

    So, I created a form (FrmFilter) which displays the months of the year, in number form (Eg 1 for January 2 for February etc)

    I used a button on the form to open the report.

    On the on open event of the report i have the following code.


    Code:
    Option Compare Database
    
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    
    Set db = CurrentDb()
    
     
    'Looking for data
     strsql = "SELECT * FROM tbldata "
     strsql = strsql & "WHERE month(tbldata.coustart) Like '" & "" & Form.frmfilter.cboStart & "" & ""
     strsql = strsql & "**' OR month(tbldata.coufinish) Like '" & "" & Form.frmfilter.CboEndDate & "" & ";"
     
     Set rst = db.OpenRecordset(strsql, dbOpenSnapshot, dbSeeChanges)
    
    DoCmd.GoToRecord , , acFirst
    'Not Found anything
    
    If rst.EOF = True Then
        
        'Alert the user
        MsgBox "Nothing found", vbInformation, " Filtering Complete"
     
        
    'Found Something
    Else
    
        Me.rptcount.Report.RecordSource = strsql
        
    
    End If
    
    rst.Close
    db.Close
    End Sub
    I get a wide range of colourful and shiny compile errors like "Invalid Outside Procedure" IT then highlights the "Set db = Currentdb()" line, and once that is removed it starts attacking the strsql lines.

    Does anyone have any ideas, I admit im not that strong at VBA so it could be somethign glaringly obvious i'm missing, but i'm completley stumped, but sadly none of my colleague are here to help me today.

    Any pointers you can give will be greatly appreciated, Thank you very much!

    Chris.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I would check for missing references if you are getting compile errors for CurrentDB() and the strSQL lines. Also, I would put in an Option Explicit so you get compile errors if a variable is not defined.
    Wendell

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Code:
    Option Compare Database
    
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    
    Set db = CurrentDb()
    
     
    'Looking for data
     strsql = "SELECT * FROM tbldata "
     strsql = strsql & "WHERE month(tbldata.coustart) Like '" & "" & Form.frmfilter.cboStart & "" & ""
     strsql = strsql & "**' OR month(tbldata.coufinish) Like '" & "" & Form.frmfilter.CboEndDate & "" & ";"
     
     Set rst = db.OpenRecordset(strsql, dbOpenSnapshot, dbSeeChanges)
    
    DoCmd.GoToRecord , , acFirst
    'Not Found anything
    
    If rst.EOF = True Then
        
        'Alert the user
        MsgBox "Nothing found", vbInformation, " Filtering Complete"
     
        
    'Found Something
    Else
    
        Me.rptcount.Report.RecordSource = strsql
        
    
    End If
    
    rst.Close
    db.Close
    End Sub
    This looks like Odd Fragments of Code to me

    The Option Explicit and Option Compare Text should be at the Top of the Module as they are

    The On Report Open Procedure has no

    Private Sub Report_Open (Cancel as Integer) heading

    Can you also show the Complete Code on the Forms Button the Opens the Report
    So it is possible to see all the related VBA

    I also suspect that

    Form.frmfilter.cboStart and Form.frmfilter.cboEndDate

    Should be Forms!frmfilter.cboStart and Forms!frmfilter.cboEndDate

    I assume you are setting the control source of a SubForm rptcount
    I have in the past known Access to be a bit quirky setting the recordsource of a subform
    Andrew

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    You can do all this with almost no code at all.

    Just create a query that uses the combo boxes on the form as parameters, and set the recordsource of the subreport to that query.

    Code:
     strsql = "SELECT * FROM tbldata "
     strsql = strsql & "WHERE month(tbldata.coustart) Like '" & "" & Form.frmfilter.cboStart & "" & ""
     strsql = strsql & "**' OR month(tbldata.coufinish) Like '" & "" & Form.frmfilter.CboEndDate & "" & ";"
    I don't see why you are using Like here. To get a range of dates you need to use the Between ...And syntax.

    Code:
     strsql = "SELECT * FROM tbldata "
     strsql = strsql & "WHERE month(tbldata.coustart) Between " & Form.frmfilter.cboStart 
     strsql = strsql & " And " Form.frmfilter.CboEndDate
    Do you have years included in your combo boxes, or just month? Do you need to allow for a date range that spans more than one year?
    Regards
    John



  5. #5
    Lounger
    Join Date
    Mar 2010
    Location
    UK
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Wendellb: I have checked my refferences, they are all ok, or rather i think they are, i have the dao library on (which was standard) as well as the other one access usually uses

    AKW: one of the problems i kind of feel a little foolish for - i may have accidently had the "Private sub" but highlighted when pasting in this code.

    Its code i thoguht i could modify, having used it for a similar job on a form.

    Also, the report is a stand alone report not a subform, as the aim is to get a print out, and in my mind its just easier to use reports for that.

    John Hutchinson: your right, i shouldnt be using like, its a hangover from the codes prvious incarnation. but i'm not sure if between would be right as im (hopfully) stripping the month from the two dates (coustart and coufinish) and then matching that with dates from cbostart and cboenddate

    date ranges spanning one year might not be a problem i would only really want to check last months or this months data but not last years

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by Christopher Theobald View Post
    but i'm not sure if between would be right as im (hopfully) stripping the month from the two dates (coustart and coufinish) and then matching that with dates from cbostart and cboenddate
    If you want to find the events that happened from Feb to August, then doesn't that mean that the month is between 2 and 8?

    But if some of the events happened this year, and some last year, do you want all of them? or just this years?

    What about events between December and January?

    Because you used this code
    Code:
     Me.rptcount.Report.RecordSource = strsql
    it looked like you where talking about a subreport.

    Is this code executing in the form or the report?
    Me refers to whatever object is executing the code. So if the code was in the report all you need is:
    Code:
     Me.RecordSource = strsql
    But if you just use a saved query you don't need this at all.
    Regards
    John



  7. #7
    Lounger
    Join Date
    Mar 2010
    Location
    UK
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i was using it on the report, thanks for clearing that one up.

    i had a little play with a query, but couldnt seem to get it to work without me filling in the same date range twice. or do you mean that i shoudl refference the comboboxes in the sql view query?

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    You build a query in the normal way, with the form sitting open out of sight.

    In the criteria line for the relevant field you right click and choose Build then use the expression builder to construct something like this.
    [attachment=88371:formCriteria.gif]

    Do you need help using the Expression Builder?
    Attached Images Attached Images
    Regards
    John



  9. #9
    Lounger
    Join Date
    Mar 2010
    Location
    UK
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i'll give it a play and see what happens,

    Thank you! I relly didnt know you could do that. I asked a few colleagues this morning, and they didnt mention it was possible to do it that way.

    I'll let you know how i get on!

  10. #10
    Lounger
    Join Date
    Mar 2010
    Location
    UK
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts
    So, its not working, it doesnt error, it just opens a blank report. or 1 record.

    Blank report is when i do 1 month or 2 months. (jan - mar for example) it returns one report when i do a full year, it shoudl return 3.

    would it be that possible my months are wrong?

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Forget about the report to start with. Just get the query working.

    Run the query with no criteria at all and look at the data in the month column.
    What is the row source of the combos you use for the criteria?

    What have you got in the criteria line that is not working?
    Regards
    John



  12. #12
    Lounger
    Join Date
    Mar 2010
    Location
    UK
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i've been playing around with the query, if i put in month 1 and month 2, and im getting all results, including the ones in june (which ends in december)

    if i could work out how to put pictures into my post, i would show you my criteria (the image upload box doent open properly, i guess its teh downside of having to use IE 6)

    Anyway.

    i created a list for the combos like this 01;jan;02;feb;03;mar etc. (so eventually i will select the month not the number) i have both columns displayed in the combo, so i can see they do corespond.

    The dates in the records are as follows

    Recno CouStart CouFinish
    1 01/02/2010 02/02/2010
    2 08/06/2010 12/12/2010
    3 05/09/2010 25/02/2011

    Yet the all show up when i put in date 1 and date 2 as the date range. Interestingly, i have just discovered that putting 01, 02 doesnt do anything.

    So, Does the Month(coustqart) expr strip it down to just a single number (so 2 rather than 02?)


    Edit-------------
    I think i have solved the issue.

    Having not accounted for years, i get shown record 3, because the month it ends in is 2. and th emonth function does strip it down to 2 rather than 02. so i can accoutn for this in my list.

    My last question (provided this works) is there a "Year" function, or should i juste use "Left(coustart, 6,Length)" ?

  13. #13
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Yes there is a Year function that works exactly the same as the Month function.
    Regards
    John



  14. #14
    Lounger
    Join Date
    Mar 2010
    Location
    UK
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yeah, i found it,

    I have been playing around and aside from one little irritation, which is no doubt actually my understanding of this, it all seems to be working.

    I would once again like to thank you John as you have helped me a lot! this place is great! but i will try not to post asking for help again for a little while atleast

  15. #15
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I am glad that has all helped. Come back whenever you have your next question.
    Regards
    John



Page 1 of 2 12 LastLast

Posting Permissions

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