Previous Date (2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

Try

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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;

Reply | Quote

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

Reply | Quote

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;

Reply | Quote

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 (")

Reply | Quote

as said i tried that and it didnt work

Reply | Quote

Wait a second……………

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Wait a second……………

Reply | Quote

as said i tried that and it didnt work

Reply | Quote

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 (")

Reply | Quote

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;

Reply | Quote

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

Reply | Quote

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;

Reply | Quote

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.

Reply | Quote

Try

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

Reply | Quote