Results 1 to 11 of 11
  1. #1
    zabronm is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2013
    Posts
    8

    BETWEEN function as parameter to Docmd.OpenReport ..NOT WORKING

    Hi all, please assist I am going crazy..

    What would be wrong with this:

    strCondition = "Format([tblOrderLineTrack_main].[dtAllocation],'dd/MM/yyyy') BETWEEN Format(#06/02/2015#,'dd/MM/yyyy') AND Format(#07/03/2015#,'dd/MM/yyyy')"

    DoCmd.OpenReport strDocName, acViewPreview, , strCondition, acWindowNormal

    The report is failing to filter, is there anything wrong with the BETWEEN function syntax here? The report is showing up all records including those that are outside the specified dates! Please assist.



    Thank you

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,441
    did you debug.print your strcondition? it's not working because you're not comparing a FIELD to a date range you're comparing a value you'd want to replace the value with a field name like:

    strcondition = "[dtallocation] Between ...(the rest of your statement)"

    though I still don't know if it would work because FORMAT has a tendency to change values from dates or numbers to text so you may need to do something more like

    strcondition = "cdate([dtAllocation]) BETWEEN ..."

    See:

    http://allenbrowne.com/ser-36.html

  3. #3
    zabronm is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2013
    Posts
    8
    A million thanks, am not sure if I am getting you well. BUT, pleas note: (1) [tblOrderLineTrack_main].[dtAllocation] is a field name from the query, and it is a date/time value; I have already tried:

    [dtAllocation] Between #" & strDate1 & "# And #" & strDate2 & "#"; which should give me [dtAllocation] Between #06/03/2015# And #07/03/2015#. I debug.write this and it appeared as you suggested, but no joy at all. Please assist...


  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,941
    Just looking quickly

    [dtAllocation] Between #" & strDate1 & "# And #" & strDate2 & "#";

    you might try (
    untested)
    [dtAllocation] Between #" & Cdate(strDate1) & "# And #" & CDate(strDate2) & "#";

  5. #5
    zabronm is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2013
    Posts
    8
    Thank you again, highly appreciated: The above does not work either; HOWEVER, I have discovered something very strange: the sytax:

    [dtAllocation] Between #date1# And #date2# ONLY works for dates after the 12th..that is, I got results when I tested for 13 upwards eg. #13/03/2015#, and it does NOT work for dates before #12/03/2015#

    Would you think of any problem there? My computer regional settings have date format set to "dd/MM/yyyy"

    Please help me, this is really driving me to the sky!!!

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,869
    See Allen Browne's site:

    International Dates in Access
    http://www.allenbrowne.com/ser-36.html
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  7. #7
    zabronm is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2013
    Posts
    8
    Something quite strange here:

    [dtAllocation] Between #date1# And #date2# ONLY works for dates after the 12th..that is, I got results when I tested for 13 upwards eg. #13/03/2015#, and it does NOT work for dates before #12/03/2015#


    Would you think of any problem there? My computer regional settings have date format set to "dd/MM/yyyy"



  8. #8
    zabronm is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2013
    Posts
    8
    Quote Originally Posted by ssanfu View Post
    See Allen Browne's site:

    International Dates in Access
    http://www.allenbrowne.com/ser-36.html
    S

    Ohhhhh YEESS, I tried the date #07/03/2015# in query design, then I immediately switched to SQL design ALAS, the date had been changed to #03/07/2015#.

    Now, is there anywhere in access where I can set so the format remains "dd/MM/yyyy"? Please note (1) I have already tried the Format function and it did not work, (2) My Regional settings on my computer is "dd/MM/yyyy". Could there be anywhere I need to set, OR, how can I resolve this?

    Thanks

  9. #9
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,441
    you're thinking about this the wrong way around I think

    access expects mm/dd/yyyy so give it what it wants how you display the output is up to you

    so do this simple test.

    IN a query include your datefield

    now try this

    Test1: datefield between dateserial(yearnumber, monthnumber, daynumber) and dateserial(yearnumber, monthnumber, daynumber)

    see if you get the expected results

    if not try

    Test2: dateserial(datepart("yyyy", datefield), datepart("m", datefield), datepart("d", datefield)) between dateserial(yearnumber, monthnumber, daynumber) and dateserial(yearnumber, monthnumber, daynumber)

    see if you get the expected results and so on

  10. #10
    zabronm is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2013
    Posts
    8
    You are spot-on.. thank you INDEED guys. I thought if I format my input to 'dd/mm/yyyy' then the underlying query would follow suite!! Anyway, from the suggested link, the second suggestion did the trick:
    SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")

    Whatever those punctuations mean, I never bothered to research further, I am sorted for today!! Many thanks to you all guys!!

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,869
    Whatever those punctuations mean,
    From Help:
    "The following functions return values in a String variable when you append a dollar sign ($) to the function name. These functions have the same usage and syntax as their Variant equivalents without the dollar sign.

    Chr$, ChrB$, CurDir$, Date$, Dir$, Error$, Format$, Hex$, Input$, InputB$, LCase$, Left$, LeftB$, LTrim$, Mid$, MidB$, Oct$, Right$, RightB$, RTrim$, Space$, Str$, String$, Time$ ,Trim$, UCase$ "

    Format$() returns a String
    Format() returns a Variant


    While these functions still work, AFAIK, they have been deprecated (but are still available for backward compatibility).


    ================================================== =======================


    In MS-Basic and GW-Basic, there were also Type declaration Characters (special characters) that defined the variable type.
    (Also deprecated - but are still available for backward compatibility.)

    Character Type of Variable Example Memory required
    $ String variable Name$ String Length
    % Integer variable Marks% 2 Bytes
    ! Single-precision variable Avg! 4 Bytes
    # Double-precision variable Area# 8 Bytes

    Basically, no pun (intended), it was how the variable type was declared in IBM Basic, MS Basic, GW- Basic, etc.

    Both columns declare the same type of variables:
    Dim Name$ Dim Name As String
    Dim Mark% Dim Mark as Integer
    Dim Avg! Dim Avg as Single
    Dim Area# Dim Area as Double

    Back in the day, memory (RAM) was extremely expensive. "Dim Name$" took less memory.
    Code was tighter because you had to count how many bytes were used. Tighter code meant less memory was used.
    Nowadays, memory is inexpensive, code is not so tight, and programs take up lots of memory.

    Also, "Dim Name As String" is used rather than "Dim Name$" because
    1) "Dim Name As String" is easier to read/remember and
    2) memory limitations (RAM) do not have to be considered anymore.



    Am I being redundant?
    Am I being redundant?
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. DoCmd.OpenReport Where Argument
    By snipe in forum Programming
    Replies: 2
    Last Post: 04-24-2014, 10:24 AM
  2. DoCmd.OpenReport not working correctly?
    By XenoZephyr in forum Access
    Replies: 2
    Last Post: 07-02-2013, 12:08 PM
  3. DoCmd.OpenReport WHERE condition
    By bidbud68 in forum Programming
    Replies: 16
    Last Post: 10-19-2012, 05:31 AM
  4. Need help w/ docmd.openreport
    By jwill in forum Reports
    Replies: 3
    Last Post: 06-04-2012, 09:49 PM
  5. DoCmd OpenReport ... where condition with a Like
    By Grooz13 in forum Programming
    Replies: 4
    Last Post: 08-31-2010, 09:04 AM

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
  •  
Other Forums: Microsoft Office Forums - Senior Forums