Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2003
    Posts
    4

    Unanswered: VBA Filter on a report

    Help

    I have a problem in sending a filter to a report.

    I have a form set up to act as a small query builder with several option boxes and a start date and an end date.

    Within VBA on the form I create a txt field where I create the WhereCondition for use in the DoCmd.OpenReport.

    The report give seems to ignore the StartDate but is ok on the EndDate.

    My code in my form for the date is

    'Set up the where condition
    where_stat = "Start_Date between #" & Me.StartDate & "# and #" & Me.EndDate & "#"

    'Open the report
    DoCmd.OpenReport "R_Client_Into_Training", acViewPreview, , where_stat

    Start_Date is within the RecordSource of R_Client_Into_Training. StartDate and EndDate are the user input.

    If the StartDate is 01/08/2003 and EndDate 01/01/2004 this shall give me report with records between 01/01/2003 and 01/01/2004.

    Any ideas welcome.

    Much Thanks
    Paul Tiernan

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    So sending 1/8/2003 gives you 1/1?

    Put this in your OnOpen event for the report

    MsgBox Me.Recordsource
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Paul

    It's not clear from your post - are you from Blighty and your regional settings set to UK? Are those UK or US dates? If so, is the return more specifically between 08/01/2003 and 01/01/2004?

  4. #4
    Join Date
    Sep 2003
    Posts
    4

    Date Format

    My PC has UK regional settings. Wihtin the form I made the format dd/mm/yyyy and this is also set in the table for my dates.

    I am running Access 97.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Paul

    Could you try a few different dates? see if you get different results for dates that are clearly uk (i.e. the day is 13 or over, like 30/01/2003) to ones that could be confused for US (e.g. 07/04/2004). Only thing is to make sure of is your dates aren't 01/01/2004, 02/02/2004 etc as obviously these are the same either way. Gist is, in certain circumstances VBA ignores all your date format settings and treats dates as US if it can. I may have the wrong end of the stick, but from your example dates this could feasibly be the problem.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Bugger my last suggestion. Quicker to try the solution and see if it works than check to see if the problem is what I think it is.

    Change your WhereCondition to "Start_Date between fVBDate(#" & Me.StartDate & "#) and fVBDate(#" & Me.EndDate & "#)"

    Put this in a module:

    Function fVBDate(datSQL as date)

    if isnull(datSQL) then _
    Exit Function

    if CInt(Mid(datSQL, 1, 2)) <= 12 then

    fVBDate = (Mid([datSQL], 4, 2) & "/" & Mid([datSQL], 1, 2) & "/" & Mid([datSQL], 7)

    else

    fVBDate = datSQL

    end if

    End Function

    Hope it works, t'otherwise stick with Teddy.

    If it does work, I would keep your text box as it was (i.e. "Start_Date between #" & Me.StartDate & "# and #" & Me.EndDate & "#"), if the intention is to show the user SQL. This is to fix a VB anomaly, not a SQL one.

    In any case, if there are any other people who understand precisely why and when VB desides to ignore your carefully formatted date fields, textboxes and regional settings I'd be interested to hear a definitive answer. I have a folder full of stuff off the web purporting to explain and offer fixes that don't work. My own theories change every time I come up against the problem.
    Last edited by pootle flump; 05-07-04 at 17:32. Reason: Forgot to handle null values

  7. #7
    Join Date
    Sep 2003
    Posts
    4
    Cheers pootle flump

    This now works fine, thanks for that.

    SO let me get this right I tell Access that the text box has the format dd/mm/yyyy and the field in the table also has format dd/mm/yyyy and my regional settings are set to UK ie dd/mm/yyyy.

    In VBA swaps my dd and mm to give mm/dd/yyyy. What help is that I have alreadt forced the change 3 times. Is this one for microsoft? I am not going to waste my time and yours by complaining about Microsoft.

    Thanks again for your help.

    Muych Thanks
    Paul Tiernan

  8. #8
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Other resource

    Quite a common issue - check this out for a full explanation and a good solution.

    http://support.microsoft.com/default...NoWebContent=1

    Rasher
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh, so it's concatenation that does it! Could never figure out why you can compare and test date values assigned to a variable no probs, yet as soon as it's popped into a SQL string it goes haywire. Cheers and much appreciated. Knowing the reason is 90% of the battle.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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