Find records with date field before today

Discussion in 'ASP General' started by !TG, Jul 26, 2005.

  1. !TG

    !TG Guest

    I have a table with a date field.
    All I want to do it get all the records with a date before today.
    I tried the following:
    "SELECT * FROM StateLicenses Where (Exp < #07/26/2005#) Order By
    BranchNo,Satellite;"
    "SELECT * FROM StateLicenses Where (Exp < 07/26/2005) Order By
    BranchNo,Satellite;"
    "SELECT * FROM StateLicenses Where (Exp < " & Now() & ") Order By
    BranchNo,Satellite;"
    "SELECT * FROM StateLicenses Where (Exp < #" & Now() & "#) Order By
    BranchNo,Satellite;"
    "SELECT * FROM StateLicenses Where (Exp < " & FormatDateTime(Now(),2) &
    ") Order By BranchNo,Satellite;"
    "SELECT * FROM StateLicenses Where (Exp < #" & FormatDateTime(Now(),2) &
    "#) Order By BranchNo,Satellite;"

    and get errors such as:
    Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

    [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.


    The expected numbe is sometimes 1 and sometimes 3.
    Please tell me what I am doing wrong.
    !TG, Jul 26, 2005
    #1
    1. Advertising

  2. sql = "SELECT * FROM StateLicences WHERE Exp < DATE()"

    I assume Exp should have a better column name...



    "!TG" <> wrote in message
    news:%...
    >I have a table with a date field.
    > All I want to do it get all the records with a date before today.
    > I tried the following:
    > "SELECT * FROM StateLicenses Where (Exp < #07/26/2005#) Order By
    > BranchNo,Satellite;"
    > "SELECT * FROM StateLicenses Where (Exp < 07/26/2005) Order By
    > BranchNo,Satellite;"
    > "SELECT * FROM StateLicenses Where (Exp < " & Now() & ") Order By
    > BranchNo,Satellite;"
    > "SELECT * FROM StateLicenses Where (Exp < #" & Now() & "#) Order By
    > BranchNo,Satellite;"
    > "SELECT * FROM StateLicenses Where (Exp < " & FormatDateTime(Now(),2) & ")
    > Order By BranchNo,Satellite;"
    > "SELECT * FROM StateLicenses Where (Exp < #" & FormatDateTime(Now(),2) &
    > "#) Order By BranchNo,Satellite;"
    >
    > and get errors such as:
    > Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
    >
    > [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.
    >
    >
    > The expected numbe is sometimes 1 and sometimes 3.
    > Please tell me what I am doing wrong.
    Aaron Bertrand [SQL Server MVP], Jul 26, 2005
    #2
    1. Advertising

  3. !TG

    Curt_C [MVP] Guest

    !TG wrote:
    > I have a table with a date field.
    > All I want to do it get all the records with a date before today.
    > I tried the following:
    > "SELECT * FROM StateLicenses Where (Exp < #07/26/2005#) Order By
    > BranchNo,Satellite;"
    > "SELECT * FROM StateLicenses Where (Exp < 07/26/2005) Order By
    > BranchNo,Satellite;"
    > "SELECT * FROM StateLicenses Where (Exp < " & Now() & ") Order By
    > BranchNo,Satellite;"
    > "SELECT * FROM StateLicenses Where (Exp < #" & Now() & "#) Order By
    > BranchNo,Satellite;"
    > "SELECT * FROM StateLicenses Where (Exp < " & FormatDateTime(Now(),2) &
    > ") Order By BranchNo,Satellite;"
    > "SELECT * FROM StateLicenses Where (Exp < #" & FormatDateTime(Now(),2) &
    > "#) Order By BranchNo,Satellite;"
    >
    > and get errors such as:
    > Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
    >
    > [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.
    >
    >
    > The expected numbe is sometimes 1 and sometimes 3.
    > Please tell me what I am doing wrong.



    "SELECT * FROM StateLicenses Where (Exp < '" & Now() & "') Order By
    BranchNo,Satellite;"

    --
    Curt Christianson
    site: http://www.darkfalz.com
    blog: http://blog.darkfalz.com
    Curt_C [MVP], Jul 26, 2005
    #3
  4. > "SELECT * FROM StateLicenses Where (Exp < '" & Now() & "') Order By
    > BranchNo,Satellite;"


    Why do this outside of the database? Access knows what NOW() is. Plus, I
    don't think single quote delimiters will work well, assuming Exp is a Date
    column...
    Aaron Bertrand [SQL Server MVP], Jul 26, 2005
    #4
  5. !TG

    Curt_C [MVP] Guest

    Aaron Bertrand [SQL Server MVP] wrote:
    >>"SELECT * FROM StateLicenses Where (Exp < '" & Now() & "') Order By
    >>BranchNo,Satellite;"

    >
    >
    > Why do this outside of the database? Access knows what NOW() is. Plus, I
    > don't think single quote delimiters will work well, assuming Exp is a Date
    > column...
    >
    >


    Till you said it I didn't realize it was Access. The reason I was
    pushing it this way is that the DB may be in a different timezone then
    the client, thinking the client wanted their time....guess it depends
    what was used for the time when it was entered... hopefully it was
    standardized with server time as you indicated though.....

    --
    Curt Christianson
    site: http://www.darkfalz.com
    blog: http://blog.darkfalz.com
    Curt_C [MVP], Jul 26, 2005
    #5
  6. !TG

    !TG Guest

    Thanks for the replies.
    EXP is type Date/Time in access database.
    I also specified a format of short date after the problems began in a
    wild stab at the dark.

    I tried the below as recommended by Aaron
    PSQL = "SELECT * FROM "
    ThisTBL = PSQL & "StateLicenses Where (Exp < Date()) Order By
    BranchNo,Satellite;"
    PRS.Open ThisTBL, PDB
    And got:
    [Microsoft][ODBC Microsoft Access Driver] Too few parameters.
    Expected 2.

    I tried the below as recommended by Curt_C
    PSQL = "SELECT * FROM "
    ThisTBL = PSQL & "StateLicenses Where (Exp < " & Now() & ")
    Order By BranchNo,Satellite;"
    PRS.Open ThisTBL, PDB
    And got:
    [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
    operator) in query expression '(Exp < 7/26/2005 2:48:25 PM)'.

    Since that error was different I thought I might be on the right track
    so I surrounded Now() with single quotes and pound signs, both of which
    generated:
    [Microsoft][ODBC Microsoft Access Driver] Too few parameters.
    Expected 2.

    I tried the below as recommended by Aaron
    PSQL = "SELECT * FROM "
    ThisTBL = PSQL & "StateLicenses Where (Exp < Now()) Order By
    BranchNo,Satellite;"
    PRS.Open ThisTBL, PDB
    And got:
    [Microsoft][ODBC Microsoft Access Driver] Too few parameters.
    Expected 2.


    I thought this query would be so simple and all the recommendations I
    found on the internet were the same as yours...
    I do appreciate the input, anything else?
    Questions for me regarding my programming?
    !TG, Jul 26, 2005
    #6
  7. Is Exp a reserved word? Try the very first query but surround Exp with
    [Exp]

    Also try

    set PRS = PDB.Execute(ThisTBL)

    I also suggest using more standard names, they cause far less confusion.
    Typically connection objects are named conn and recordset objects are named
    rs or objRS. Just makes the code easier for others to follow...

    A


    "!TG" <> wrote in message
    news:ul$...
    > Thanks for the replies.
    > EXP is type Date/Time in access database.
    > I also specified a format of short date after the problems began in a wild
    > stab at the dark.
    >
    > I tried the below as recommended by Aaron
    > PSQL = "SELECT * FROM "
    > ThisTBL = PSQL & "StateLicenses Where (Exp < Date()) Order By
    > BranchNo,Satellite;"
    > PRS.Open ThisTBL, PDB
    > And got:
    > [Microsoft][ODBC Microsoft Access Driver] Too few parameters.
    > Expected 2.
    >
    > I tried the below as recommended by Curt_C
    > PSQL = "SELECT * FROM "
    > ThisTBL = PSQL & "StateLicenses Where (Exp < " & Now() & ")
    > Order By BranchNo,Satellite;"
    > PRS.Open ThisTBL, PDB
    > And got:
    > [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
    > operator) in query expression '(Exp < 7/26/2005 2:48:25 PM)'.
    >
    > Since that error was different I thought I might be on the right track so
    > I surrounded Now() with single quotes and pound signs, both of which
    > generated:
    > [Microsoft][ODBC Microsoft Access Driver] Too few parameters.
    > Expected 2.
    >
    > I tried the below as recommended by Aaron
    > PSQL = "SELECT * FROM "
    > ThisTBL = PSQL & "StateLicenses Where (Exp < Now()) Order By
    > BranchNo,Satellite;"
    > PRS.Open ThisTBL, PDB
    > And got:
    > [Microsoft][ODBC Microsoft Access Driver] Too few parameters.
    > Expected 2.
    >
    >
    > I thought this query would be so simple and all the recommendations I
    > found on the internet were the same as yours...
    > I do appreciate the input, anything else?
    > Questions for me regarding my programming?
    Aaron Bertrand [SQL Server MVP], Jul 26, 2005
    #7
  8. !TG

    !TG Guest

    Aaron Bertrand [SQL Server MVP] wrote:
    > sql = "SELECT * FROM StateLicences WHERE Exp < DATE()"
    >
    > I assume Exp should have a better column name...
    >
    >
    >
    > "!TG" <> wrote in message
    > news:%...
    >
    >>I have a table with a date field.
    >>All I want to do it get all the records with a date before today.
    >>I tried the following:
    >>"SELECT * FROM StateLicenses Where (Exp < #07/26/2005#) Order By
    >>BranchNo,Satellite;"
    >>"SELECT * FROM StateLicenses Where (Exp < 07/26/2005) Order By
    >>BranchNo,Satellite;"
    >>"SELECT * FROM StateLicenses Where (Exp < " & Now() & ") Order By
    >>BranchNo,Satellite;"
    >>"SELECT * FROM StateLicenses Where (Exp < #" & Now() & "#) Order By
    >>BranchNo,Satellite;"
    >>"SELECT * FROM StateLicenses Where (Exp < " & FormatDateTime(Now(),2) & ")
    >>Order By BranchNo,Satellite;"
    >>"SELECT * FROM StateLicenses Where (Exp < #" & FormatDateTime(Now(),2) &
    >>"#) Order By BranchNo,Satellite;"
    >>
    >>and get errors such as:
    >>Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
    >>
    >>[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.
    >>
    >>
    >>The expected numbe is sometimes 1 and sometimes 3.
    >>Please tell me what I am doing wrong.

    >
    >
    >

    Returned
    [Microsoft][ODBC Microsoft Access Driver] Too few parameters.
    Expected 2.
    !TG, Jul 26, 2005
    #8
  9. !TG

    !TG Guest

    Curt_C [MVP] wrote:
    > !TG wrote:
    >
    >> I have a table with a date field.
    >> All I want to do it get all the records with a date before today.
    >> I tried the following:
    >> "SELECT * FROM StateLicenses Where (Exp < #07/26/2005#) Order By
    >> BranchNo,Satellite;"
    >> "SELECT * FROM StateLicenses Where (Exp < 07/26/2005) Order By
    >> BranchNo,Satellite;"
    >> "SELECT * FROM StateLicenses Where (Exp < " & Now() & ") Order By
    >> BranchNo,Satellite;"
    >> "SELECT * FROM StateLicenses Where (Exp < #" & Now() & "#) Order By
    >> BranchNo,Satellite;"
    >> "SELECT * FROM StateLicenses Where (Exp < " & FormatDateTime(Now(),2)
    >> & ") Order By BranchNo,Satellite;"
    >> "SELECT * FROM StateLicenses Where (Exp < #" & FormatDateTime(Now(),2)
    >> & "#) Order By BranchNo,Satellite;"
    >>
    >> and get errors such as:
    >> Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
    >>
    >> [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.
    >>
    >>
    >> The expected numbe is sometimes 1 and sometimes 3.
    >> Please tell me what I am doing wrong.

    >
    >
    >
    > "SELECT * FROM StateLicenses Where (Exp < '" & Now() & "') Order By
    > BranchNo,Satellite;"
    >

    That gave me
    [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
    operator) in query expression '(Exp < 7/26/2005 2:48:25 PM)'.

    Since that error was different I thought I might be on the right track
    so I surrounded Now() with single quotes and pound signs, both of which
    generated:
    [Microsoft][ODBC Microsoft Access Driver] Too few parameters.
    Expected 2.
    !TG, Jul 26, 2005
    #9
  10. !TG

    !TG Guest

    Aaron Bertrand [SQL Server MVP] wrote:
    >>"SELECT * FROM StateLicenses Where (Exp < '" & Now() & "') Order By
    >>BranchNo,Satellite;"

    >
    >
    > Why do this outside of the database? Access knows what NOW() is. Plus, I
    > don't think single quote delimiters will work well, assuming Exp is a Date
    > column...
    >
    >

    That gave me:
    [Microsoft][ODBC Microsoft Access Driver] Too few parameters.
    Expected 2.
    !TG, Jul 26, 2005
    #10
  11. !TG

    !TG Guest

    Aaron Bertrand [SQL Server MVP] wrote:
    > Is Exp a reserved word? Try the very first query but surround Exp with
    > [Exp]
    >
    > Also try
    >
    > set PRS = PDB.Execute(ThisTBL)
    >
    > I also suggest using more standard names, they cause far less confusion.
    > Typically connection objects are named conn and recordset objects are named
    > rs or objRS. Just makes the code easier for others to follow...
    >


    Same effect on all of the above.
    I have a query looking for an exact date that works just fine.
    !TG, Jul 26, 2005
    #11
  12. !TG

    Curt_C [MVP] Guest

    !TG wrote:
    > Thanks for the replies.
    > EXP is type Date/Time in access database.
    > I also specified a format of short date after the problems began in a
    > wild stab at the dark.
    >
    > I tried the below as recommended by Aaron
    > PSQL = "SELECT * FROM "
    > ThisTBL = PSQL & "StateLicenses Where (Exp < Date()) Order By
    > BranchNo,Satellite;"
    > PRS.Open ThisTBL, PDB



    Response.Write ThisTBL

    What is the exact code/text its generating?


    --
    Curt Christianson
    site: http://www.darkfalz.com
    blog: http://blog.darkfalz.com
    Curt_C [MVP], Jul 26, 2005
    #12
  13. > Same effect on all of the above.
    > I have a query looking for an exact date that works just fine.


    What version of Access? What version of MDAC is on your server? What does
    your connection string look like?

    Can you show the exact syntax for the query that works, and the code around
    it?

    A
    Aaron Bertrand [SQL Server MVP], Jul 26, 2005
    #13
  14. !TG

    !TG Guest

    Curt_C [MVP] wrote:
    > !TG wrote:
    >
    >> Thanks for the replies.
    >> EXP is type Date/Time in access database.
    >> I also specified a format of short date after the problems began in a
    >> wild stab at the dark.
    >>
    >> I tried the below as recommended by Aaron
    >> PSQL = "SELECT * FROM "
    >> ThisTBL = PSQL & "StateLicenses Where (Exp < Date()) Order By
    >> BranchNo,Satellite;"
    >> PRS.Open ThisTBL, PDB

    >
    >
    >
    > Response.Write ThisTBL
    >
    > What is the exact code/text its generating?
    >
    >

    I already have it setup and it generates what you'd expect
    like
    SELECT * FROM StateLicenses Where [Exp] < #7/26/2005# Order By
    BranchNo,Satellite;
    or
    SELECT * FROM StateLicenses Where Exp < #7/26/2005# Order By
    BranchNo,Satellite;
    !TG, Jul 26, 2005
    #14
  15. !TG

    !TG Guest

    Aaron Bertrand [SQL Server MVP] wrote:
    >>Same effect on all of the above.
    >>I have a query looking for an exact date that works just fine.

    >
    >
    > What version of Access? What version of MDAC is on your server? What does
    > your connection string look like?
    >
    > Can you show the exact syntax for the query that works, and the code around
    > it?
    >
    > A
    >
    >


    File is in 2000 format.
    Access driver version on the server says: 4.00.6200.00
    MDAC 2.8

    The Query from the one that works looks like:
    SELECT * FROM StateLicenses WHERE Exp=#2/27/1981#;

    And the code:
    Set PRS = Server.CreateObject("ADODB.Recordset")
    ThisTBL = PSQL & "StateLicenses WHERE
    Exp=#2/27/1981#;"
    Response.Write(ThisTBL)
    PRS.Open ThisTBL, PDB

    Everything else below and above is identical
    !TG, Jul 26, 2005
    #15
  16. !TG

    Curt_C [MVP] Guest

    !TG wrote:
    > Curt_C [MVP] wrote:
    >
    >> !TG wrote:
    >>
    >>> Thanks for the replies.
    >>> EXP is type Date/Time in access database.
    >>> I also specified a format of short date after the problems began in a
    >>> wild stab at the dark.
    >>>
    >>> I tried the below as recommended by Aaron
    >>> PSQL = "SELECT * FROM "
    >>> ThisTBL = PSQL & "StateLicenses Where (Exp < Date()) Order By
    >>> BranchNo,Satellite;"
    >>> PRS.Open ThisTBL, PDB

    >>
    >>
    >>
    >>
    >> Response.Write ThisTBL
    >>
    >> What is the exact code/text its generating?
    >>
    >>

    > I already have it setup and it generates what you'd expect
    > like
    > SELECT * FROM StateLicenses Where [Exp] < #7/26/2005# Order By
    > BranchNo,Satellite;
    > or
    > SELECT * FROM StateLicenses Where Exp < #7/26/2005# Order By
    > BranchNo,Satellite;


    Just for a test, take off the WHERE clause and ORDER BY clause, does it
    error out still? Just trying to determine if the error is the statement
    or not, it looks fine so I'm thinking there is something else.

    --
    Curt Christianson
    site: http://www.darkfalz.com
    blog: http://blog.darkfalz.com
    Curt_C [MVP], Jul 26, 2005
    #16
  17. !TG

    !TG Guest

    Curt_C [MVP] wrote:
    > !TG wrote:
    >
    >> Curt_C [MVP] wrote:
    >>
    >>> !TG wrote:
    >>>
    >>>> Thanks for the replies.
    >>>> EXP is type Date/Time in access database.
    >>>> I also specified a format of short date after the problems began in
    >>>> a wild stab at the dark.
    >>>>
    >>>> I tried the below as recommended by Aaron
    >>>> PSQL = "SELECT * FROM "
    >>>> ThisTBL = PSQL & "StateLicenses Where (Exp < Date()) Order By
    >>>> BranchNo,Satellite;"
    >>>> PRS.Open ThisTBL, PDB
    >>>
    >>>
    >>>
    >>>
    >>>
    >>> Response.Write ThisTBL
    >>>
    >>> What is the exact code/text its generating?
    >>>
    >>>

    >> I already have it setup and it generates what you'd expect
    >> like
    >> SELECT * FROM StateLicenses Where [Exp] < #7/26/2005# Order By
    >> BranchNo,Satellite;
    >> or
    >> SELECT * FROM StateLicenses Where Exp < #7/26/2005# Order By
    >> BranchNo,Satellite;

    >
    >
    > Just for a test, take off the WHERE clause and ORDER BY clause, does it
    > error out still? Just trying to determine if the error is the statement
    > or not, it looks fine so I'm thinking there is something else.
    >

    No errors in that instance.
    I went ahead and built a function that does what I need. It's quite
    slow, but at least it works. I'm boggled as to why the query won't work
    and thanks for the help, but you don't have to offer any more advice.

    I do appreciate the attempt.
    !TG, Jul 26, 2005
    #17
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. TB
    Replies:
    0
    Views:
    451
  2. Phillip Vong

    Set defualt date as Week Day before Today.

    Phillip Vong, Dec 11, 2006, in forum: ASP .Net
    Replies:
    2
    Views:
    544
    Rad [Visual C# MVP]
    Dec 12, 2006
  3. Replies:
    8
    Views:
    224
    peashoe
    Jan 24, 2005
  4. Vinnie Davidson

    ASP Date: get records with date = today (SQL Server)

    Vinnie Davidson, Aug 13, 2005, in forum: ASP General
    Replies:
    6
    Views:
    321
    Aaron Bertrand [SQL Server MVP]
    Aug 15, 2005
  5. joeyej
    Replies:
    7
    Views:
    142
    Randy Webb
    Apr 6, 2006
Loading...

Share This Page