Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Dates in where clause

Discussion in 'Information Technology' started by John, Jul 28, 2006.

Tags:
  1. John

    John
    Expand Collapse
    Guest

    Hi

    I have a form with tow fields for dates in dd/mm/yyyy format. I am trying to
    use the fields in a query's where clause as below;

    "SELECT * " & _
    "FROM Orders " & _
    " WHERE Orders.[Delivery Date])>= #" & Format([Forms]![Batch
    Invoices]![FromDate], "dd/mm/yyyy") & "# And Orders.[Delivery Date]<= #" &
    Format([Forms]![Batch Invoices]![ToDate], "dd/mm/yyyy") & "# "

    My problem is that query doe snot read the dates correctly and rather takes
    them as in mm/dd/yyyy format i.e. if 01/07/2006 (dd/mm/yyyy) was entered,
    query brings records for 07/01/2006 (mm/dd/yyyy).

    What is the ideal way to deal with the dates in this case so the dates are
    taken as dd/mm/yyyy by the query?

    Thanks

    Regards
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News BREAKING: Young Sikh Shot Dead In Jalalabad, Afghanistan . - Sikh24 News & Updates Breaking News Oct 2, 2016
    Pacific Oil slicks spotted in search for missing Malaysia Airlines plane (Live updates) Breaking News Mar 8, 2014
    India Candidates flock to Dera Sacha Sauda Breaking News Jan 17, 2012
    India Seven SAD Candidates of SGPC Win Unopposed Breaking News Aug 27, 2011
    GurmatChanan Updates Gurbani Download Aug 27, 2011

  3. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    use US format or military format
    ie
    mm/dd/yyyy or yyyy-mm-ddd

    Pieter



    "John" <John@nospam.infovis.co.uk> wrote in message
    news:ubVzM0gpGHA.1592@TK2MSFTNGP04.phx.gbl...
    > Hi
    >
    > I have a form with tow fields for dates in dd/mm/yyyy format. I am trying
    > to use the fields in a query's where clause as below;
    >
    > "SELECT * " & _
    > "FROM Orders " & _
    > " WHERE Orders.[Delivery Date])>= #" & Format([Forms]![Batch
    > Invoices]![FromDate], "dd/mm/yyyy") & "# And Orders.[Delivery Date]<= #" &
    > Format([Forms]![Batch Invoices]![ToDate], "dd/mm/yyyy") & "# "
    >
    > My problem is that query doe snot read the dates correctly and rather
    > takes them as in mm/dd/yyyy format i.e. if 01/07/2006 (dd/mm/yyyy) was
    > entered, query brings records for 07/01/2006 (mm/dd/yyyy).
    >
    > What is the ideal way to deal with the dates in this case so the dates are
    > taken as dd/mm/yyyy by the query?
    >
    > Thanks
    >
    > Regards
    >
     
  4. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    use US format or military format
    ie
    mm/dd/yyyy or yyyy-mm-ddd

    Pieter



    "John" <John@nospam.infovis.co.uk> wrote in message
    news:ubVzM0gpGHA.1592@TK2MSFTNGP04.phx.gbl...
    > Hi
    >
    > I have a form with tow fields for dates in dd/mm/yyyy format. I am trying
    > to use the fields in a query's where clause as below;
    >
    > "SELECT * " & _
    > "FROM Orders " & _
    > " WHERE Orders.[Delivery Date])>= #" & Format([Forms]![Batch
    > Invoices]![FromDate], "dd/mm/yyyy") & "# And Orders.[Delivery Date]<= #" &
    > Format([Forms]![Batch Invoices]![ToDate], "dd/mm/yyyy") & "# "
    >
    > My problem is that query doe snot read the dates correctly and rather
    > takes them as in mm/dd/yyyy format i.e. if 01/07/2006 (dd/mm/yyyy) was
    > entered, query brings records for 07/01/2006 (mm/dd/yyyy).
    >
    > What is the ideal way to deal with the dates in this case so the dates are
    > taken as dd/mm/yyyy by the query?
    >
    > Thanks
    >
    > Regards
    >




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4197 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     
  5. John

    John
    Expand Collapse
    Guest

    Where? How should I modify the where clause? I can't expect the user to
    enter date in non-dd/mm/yyyy format as they are all used to it due to the
    locale.

    Thanks

    Regards

    "Pieter Wijnen"
    <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway>
    wrote in message news:exz2J6gpGHA.4236@TK2MSFTNGP03.phx.gbl...
    > use US format or military format
    > ie
    > mm/dd/yyyy or yyyy-mm-ddd
    >
    > Pieter
    >
    >
    >
    > "John" <John@nospam.infovis.co.uk> wrote in message
    > news:ubVzM0gpGHA.1592@TK2MSFTNGP04.phx.gbl...
    >> Hi
    >>
    >> I have a form with tow fields for dates in dd/mm/yyyy format. I am trying
    >> to use the fields in a query's where clause as below;
    >>
    >> "SELECT * " & _
    >> "FROM Orders " & _
    >> " WHERE Orders.[Delivery Date])>= #" & Format([Forms]![Batch
    >> Invoices]![FromDate], "dd/mm/yyyy") & "# And Orders.[Delivery Date]<= #"
    >> & Format([Forms]![Batch Invoices]![ToDate], "dd/mm/yyyy") & "# "
    >>
    >> My problem is that query doe snot read the dates correctly and rather
    >> takes them as in mm/dd/yyyy format i.e. if 01/07/2006 (dd/mm/yyyy) was
    >> entered, query brings records for 07/01/2006 (mm/dd/yyyy).
    >>
    >> What is the ideal way to deal with the dates in this case so the dates
    >> are taken as dd/mm/yyyy by the query?
    >>
    >> Thanks
    >>
    >> Regards
    >>

    >
    >
    >
    > --
    > ----------------------------------------
    > I am using the free version of SPAMfighter for private users.
    > It has removed 4197 spam emails to date.
    > Paying users do not have this message in their emails.
    > Get the free SPAMfighter here: http://www.spamfighter.com/len
    >
    >
     
  6. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    You're using the Format function to explicitly put the dates in dd/mm/yyyy
    format. Change that to mm/dd/yyyy or yyyy-mm-dd.

    You might find it usefule to read Allen Browne's "International Dates in
    Access" at http://allenbrowne.com/ser-36.html, or what I have in my
    September 2003 Access Answers column for Pinnacle Publication's "Smart
    Access" newsletter. (The column and accompanying database can be downloaded
    for free at http://www.accessmvp.com/djsteele/SmartAccess.html)


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "John" <John@nospam.infovis.co.uk> wrote in message
    news:OdSqVThpGHA.3324@TK2MSFTNGP05.phx.gbl...
    > Where? How should I modify the where clause? I can't expect the user to
    > enter date in non-dd/mm/yyyy format as they are all used to it due to the
    > locale.
    >
    > Thanks
    >
    > Regards
    >
    > "Pieter Wijnen"
    > <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway>
    > wrote in message news:exz2J6gpGHA.4236@TK2MSFTNGP03.phx.gbl...
    >> use US format or military format
    >> ie
    >> mm/dd/yyyy or yyyy-mm-ddd
    >>
    >> Pieter
    >>
    >>
    >>
    >> "John" <John@nospam.infovis.co.uk> wrote in message
    >> news:ubVzM0gpGHA.1592@TK2MSFTNGP04.phx.gbl...
    >>> Hi
    >>>
    >>> I have a form with tow fields for dates in dd/mm/yyyy format. I am
    >>> trying to use the fields in a query's where clause as below;
    >>>
    >>> "SELECT * " & _
    >>> "FROM Orders " & _
    >>> " WHERE Orders.[Delivery Date])>= #" & Format([Forms]![Batch
    >>> Invoices]![FromDate], "dd/mm/yyyy") & "# And Orders.[Delivery Date]<= #"
    >>> & Format([Forms]![Batch Invoices]![ToDate], "dd/mm/yyyy") & "# "
    >>>
    >>> My problem is that query doe snot read the dates correctly and rather
    >>> takes them as in mm/dd/yyyy format i.e. if 01/07/2006 (dd/mm/yyyy) was
    >>> entered, query brings records for 07/01/2006 (mm/dd/yyyy).
    >>>
    >>> What is the ideal way to deal with the dates in this case so the dates
    >>> are taken as dd/mm/yyyy by the query?
    >>>
    >>> Thanks
    >>>
    >>> Regards
    >>>

    >>
    >>
    >>
    >> --
    >> ----------------------------------------
    >> I am using the free version of SPAMfighter for private users.
    >> It has removed 4197 spam emails to date.
    >> Paying users do not have this message in their emails.
    >> Get the free SPAMfighter here: http://www.spamfighter.com/len
    >>
    >>

    >
    >
     
  7. Michel Walsh

    Michel Walsh
    Expand Collapse
    Guest

    Hi,


    Alternatively, if the string is used as record source or as row source, you
    can bring back the FORMS!FormName!ControlName in the string:

    " SELECT * FROM Orders " &
    " WHERE Orders.[Delivery Date])>= [Forms]![Batch Invoices]![FromDate] " &
    " And Orders.[Delivery Date]<= [Forms]![Batch Invoices]![ToDate] "


    no delimiter, no format.

    Hoping it may help,
    Vanderghast, Access MVP



    "John" <John@nospam.infovis.co.uk> wrote in message
    news:ubVzM0gpGHA.1592@TK2MSFTNGP04.phx.gbl...
    > Hi
    >
    > I have a form with tow fields for dates in dd/mm/yyyy format. I am trying
    > to use the fields in a query's where clause as below;
    >
    > "SELECT * " & _
    > "FROM Orders " & _
    > " WHERE Orders.[Delivery Date])>= #" & Format([Forms]![Batch
    > Invoices]![FromDate], "dd/mm/yyyy") & "# And Orders.[Delivery Date]<= #" &
    > Format([Forms]![Batch Invoices]![ToDate], "dd/mm/yyyy") & "# "
    >
    > My problem is that query doe snot read the dates correctly and rather
    > takes them as in mm/dd/yyyy format i.e. if 01/07/2006 (dd/mm/yyyy) was
    > entered, query brings records for 07/01/2006 (mm/dd/yyyy).
    >
    > What is the ideal way to deal with the dates in this case so the dates are
    > taken as dd/mm/yyyy by the query?
    >
    > Thanks
    >
    > Regards
    >
     

Share This Page