Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Can't filter a report between date range (2000)

    Hi,

    I'm trying to open a report between a date range. I've used code similar to code given to me from the lounge in the past but keep getting an error message that but I keep getting an error message asking for an end of statement. I know I'm missing a comma or bracket or something but am not sure where it goes. The code works fine until I add in the date related information. The code I'm using is as follows:


    DoCmd.OpenReport "rptBatchInvoice", acViewPreview, , "AccountID = " & Me.lstBillingHistory & " _
    " And ([DateWorked] Between #" & Me.StartDate & "# And #" & Me.EndDate & "#)"


    Me.StartDate and Me.EndDate are both unbound controls on the form that are used to filter the report.

    Thanks,
    Leesha

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Can't filter a report between date range (2000)

    This works:
    DoCmd.OpenReport "rptBatchInvoice", acViewPreview, , "AccountID = " & Me.lstBillingHistory & _
    " And ([DateWorked] Between #" & Me.StartDate & "# And #" & Me.EndDate & "#)"

    Notice that I have left off the trailing " on the first line.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Can't filter a report between date range (2000)

    Thanks Pat, that worked! Now can you tell me why?

    Thanks,
    Leesha

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Can't filter a report between date range (2000)

    In any command where you use quotes you must have an even number of them else it cannot figure out what you are trying to do.

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Can't filter a report between date range (2000)

    I guess that makes sense. So, how do I know when I need quotes??? I'll tell you, for me its more trial and error and if I had my way I'd leave them out totally.

    Leesha

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Can't filter a report between date range (2000)

    Quotes are used to surround strings.

    Let us take a look at your posted example:

    DoCmd.OpenReport "rptBatchInvoice", acViewPreview, , "AccountID = " & Me.lstBillingHistory & " _
    " And ([DateWorked] Between #" & Me.StartDate & "# And #" & Me.EndDate & "#)"

    The first pair of quotes surround the report name rptBatchInvoice, the next 2 quotes surround the text 'AccountID = '. Then you made the error, your next 2 quotes are virtually contiguous (the 1st is at the end of the first line and the 2nd is at the start of the 2nd line, then from there it's all downhill.
    Hope this explains it reasonably well.

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Can't filter a report between date range (2000)

    It's a great explanation, whether I'll ever totally "get it" or not remains to be seen. The whole "string" thing is beyond me!

    Leesha

Posting Permissions

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