Results 1 to 13 of 13
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Previous Date (2000)

    Hi I have a table with the following fields feed_FTMEX, Feed_XD_Date with data like the below

    feed_FTMEX feed_xd_date
    PLPFIA 01-Aug-88
    PLPFIA 01-Nov-88
    PLPFIA 16-Jan-89
    PLPFIA 16-Apr-89
    PLPFIA 16-Jul-89
    PLPFIA 15-Oct-89
    PLPFIA 15-Jan-90
    PLPFIA 15-Apr-90
    PLPFIA 15-Jul-90
    PLPFIA 15-Oct-90
    PLPFIA 15-Jan-91
    PLPFIA 16-Apr-91
    PLPFIA 16-Jul-91
    PLPFIA 16-Oct-91
    PLPFIA 16-Jan-92
    PLPFIA 16-Apr-92
    PLPFIA 16-Jul-92
    PLPFIA 16-Oct-92
    PLPFIA 16-Jan-93
    PLPFIA 16-Apr-93

    what i want to do is create a query that returns the previous date for each record so my query is like this

    SELECT tbl_feed.feed_FTMEX, tbl_feed.feed_xd_date, DMax("[feed_xd_date]","[tbl_feed]"," [feed_xd_date] <# " & [feed_xd_date] & "# and [feed_FTMEX] ='" & [feed_FTMEX] & "'") AS Expr1
    FROM tbl_feed;

    The problem is im getting plenty of blank records, I cant see what i am doing wrong

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Previous Date (2000)

    Try the following:

    SELECT tbl_feed.feed_FTMEX, tbl_feed.feed_xd_date, DMax("[feed_xd_date]","[tbl_feed]"," [feed_xd_date] < # " & Format(tbl_feed.[feed_xd_date],'mm/dd/yy') & "# and [feed_FTMEX] = '" & tbl_feed.[feed_FTMEX] & "'") AS Expr1
    FROM tbl_feed;

    Note the change in bold.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Previous Date (2000)

    Try

    ... < #" & Format([feed_xd_date], "mm/dd/yyyy") & "# ...

  4. #4
    Star Lounger
    Join Date
    Apr 2002
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Previous Date (2000)

    nope that didnt work, however i changed [feed_xd_date] to [feed_xd_date] - 1 and that seem to kill most of the blanks , i then changed it to -2 and all the blanks went.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Previous Date (2000)

    I find that hard to believe as I created the table with all your data then ran the query I suggested above and the only blank was the 1st date record.

    Post your query here and we can see what you have done.

  6. #6
    Star Lounger
    Join Date
    Apr 2002
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Previous Date (2000)

    SELECT [tbl_feed].[feed_FTMEX], [tbl_feed].[feed_xd_date], DMax("[feed_xd_date]","[tbl_feed]"," [feed_xd_date] <# " & [feed_xd_date]-2 & "# and [feed_FTMEX] ='" & [feed_FTMEX] & "'") AS feed_xd_previous, [tbl_feed].[feed_lipper], [tbl_feed].[feed_Sedol], [tbl_feed].[feed_default_tax], [tbl_feed].[feed_currency], [tbl_feed].[feed_special_payment], [tbl_feed].[feed_dividends_per_year], [tbl_feed].[feed_dividends_months], [tbl_feed].[feed_distribution], [tbl_feed].[feed_legal_structure], [tbl_feed].[feed_dividend_date], [tbl_feed].[feed_Payment_date], [tbl_feed].[feed_payment], [tbl_feed].[feed_tax_code]
    FROM tbl_feed;

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Previous Date (2000)

    I meant the query that you tried with the format command.

  8. #8
    Star Lounger
    Join Date
    Apr 2002
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Previous Date (2000)

    SELECT tbl_feed.feed_FTMEX, tbl_feed.feed_xd_date, DMax("[feed_xd_date]","[tbl_feed]"," [feed_xd_date] <# " & [feed_xd_date]-2 & "# and [feed_FTMEX] ='" & [feed_FTMEX] & "'") AS feed_xd_previous
    FROM tbl_feed;

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Previous Date (2000)

    Excuse my request, but there is no format command in your query.

    As Hans noted earlier his solution included the format command:

    ... < #" & Format([feed_xd_date], "mm/dd/yyyy") & "# ...

    Mine differed in that I used the single quote (') as opposed the double quotes (")

  10. #10
    Star Lounger
    Join Date
    Apr 2002
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Previous Date (2000)

    as said i tried that and it didnt work

  11. #11
    Star Lounger
    Join Date
    Apr 2002
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Previous Date (2000)

    Wait a second...............

  12. #12
    Star Lounger
    Join Date
    Apr 2002
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Previous Date (2000)

    apologies in order your solution does work , for some reasons hans doesnt

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Previous Date (2000)

    I think you will find Hans solution does work, I got it to work.

Posting Permissions

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