Results 1 to 9 of 9

Thread: report by date

  1. #1
    Join Date
    Dec 2007
    Posts
    49

    Unanswered: report by date

    ->Hi,
    ->My project is in MS Access 2002.
    ->In that I have one form which I am using to generate report by date.
    ->In that form I have two three fields StartDate,EndDate,ItemNo all are of type Text.
    ->Now when I select StartDate and EndDate both of same Year then its working properly.
    ->But if I select StartDate from Previous Year and Today's Date as EndDate then it will simply show results of current year and only of StartDate.
    ->Means if i enter StartDate=1/1/2007 and EndDate=02/19/2008 then it will show all results of current year(1/1/2008-02/19/2008) and results ,match date only 1/1/2007 but its not showing results between 1/1/2007 to 1/1/2008.

    ->I am using this query

    ->
    Code:
    SELECT *
    FROM PRODUCTION
    WHERE ((PRODUCTION.ItemNo=Forms!Form1!ItemNo) And ((PRODUCTION.ODate) Between Forms!Form1!StartDate And Forms!Form1!EndDate));
    ->I think my all fields are of type Text that's why this problem occur.
    ->So I don't know what to do?
    ->Thanks for Help.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a look at http://www.databasedev.co.uk/sql-between.html

    SELECT *
    FROM product
    WHERE order_date between to_date ('2006/01/01', 'yyyy/mm/dd')
    AND to_date ('2006/12/31', 'yyyy/mm/dd');

  3. #3
    Join Date
    Dec 2007
    Posts
    49
    when i used ur query it will look like this
    SELECT *
    FROM
    PRODUCTION
    WHERE ((PRODUCTION.ItemNo=Forms!Form1!ItemNo) and (PRODUCTION.ODate BETWEEN(Forms!Form1!StartDate('01/01/06','mm/dd/yy') and Forms!Form1!EndDate('02/19/08','mm/dd/yy'))));


    But when i run it it will show error that undefine StartDate.
    so what to do?

  4. #4
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    what about this:
    Code:
    SELECT *
    FROM PRODUCTION
    WHERE ((PRODUCTION.ItemNo=Forms!Form1!ItemNo) And 
    ((PRODUCTION.ODate) Between Format([Forms]![Form1]![StartDate],"Short Date") And Format([Forms]![Form1]![EndDate],"Short Date"))

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by billy_pit
    when i used ur query it will look like this
    SELECT *
    FROM
    PRODUCTION
    WHERE ((PRODUCTION.ItemNo=Forms!Form1!ItemNo) and (PRODUCTION.ODate BETWEEN(Forms!Form1!StartDate('01/01/06','mm/dd/yy') and Forms!Form1!EndDate('02/19/08','mm/dd/yy'))));


    But when i run it it will show error that undefine StartDate.
    so what to do?
    I'm not surprised...
    the where clause should be
    WHERE order_date between to_date (Forms!Form1!StartDate, 'yyyy/mm/dd')
    AND to_date (Forms!Form1!EndDate, 'yyyy/mm/dd');

    the to_dat function converts a date into a string representation of the date in the specified format mask 'yyyy/mm/dd''

  6. #6
    Join Date
    Dec 2007
    Posts
    49
    It will show an error that Undefine function 'to_date' in expression.Can u give me to_date function code.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    oops my mistake....looks like to_date is an Oracle function


    so I'd suggest instead
    dim strWhereClause as string
    strWhereClause = "WHERE order_date between #" & format (cdate(Forms!Form1!StartDate), 'mm/dd/yyyy') & "# AND #" &_
    format (cdate(Forms!Form1!EndDate), 'mm/dd/yyyy') & "#;"
    add the where clause to your sql.

    you mayneed to check that the between does what you want. its possible that you may not get records that are on the end date (as access uses a ste time value, if you ised the now() function to store a date then you will also have styroed a time, so its possible that the date will be 01 Jan 2008, but may be rules out as the time element say 14:32 makes that redcord be excluded from the where clause

  8. #8
    Join Date
    Dec 2007
    Posts
    49
    I used this one it showing expresion syntax error.

    SELECT *
    FROM PRODUCTION
    WHERE ((PRODUCTION.ItemNo=Forms!Form1!ItemNo) And ((PRODUCTION.ODate) BETWEEN (#" & Format (CDate(Forms!Form1!StartDate), 'mm/dd/yy') & "#) AND (#" &
    Format (CDate(Forms!Form1!EndDate), 'mm/dd/yy') & "#)))
    ORDER BY PRODUCTION.PalletNo;


    I tried this one also but still the same error.
    SELECT *
    FROM PRODUCTION
    WHERE ((PRODUCTION.ItemNo=Forms!Form1!ItemNo) And ((PRODUCTION.ODate) BETWEEN (#" & Format (CDate(Forms!Form1!StartDate), 'mm/dd/yyyy') & "#) AND (#" &
    Format (CDate(Forms!Form1!EndDate), 'mm/dd/yyyy') & "#)))
    ORDER BY PRODUCTION.PalletNo;

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    a good way of degugging SQL is to assign the SQL to a variable and display the variable in a msgbox.

    I'd consider breaking dont he sql into individual chunks to identoify where the syntax error may lay
    I'd want to make sure that the values in each parameter is appropriate to the column definition in the table

    id want to check that that StartDate and EndDate are date values

    I'd want to check that the SQL is valid, by pasting it into a query in design mode / SQL view.

Posting Permissions

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