If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Most Recent Date
I have a linked table that has 3 fields.
fldName fldHours fldDate This table is poulated by and external program designed to give hours of usage by month and is run to show the most recent month and the previous month's data. Therefore the Date field only has 2 values: 03/01/04 and 04/01/04. this would be for april and march's figures. Next month the table will be repopulated with April and may (04/01/04 and 05/01/04). without specifying a date as criteri in the query how can I get only the current month's data? |
#2
|
|||
|
|||
Most Recent Date
I tried this and it seems to work on a small data set:
build a second Totals query that simply finds the Max date in your basic data set. Join this query into your main query via the date field (join for exact matches only). The main query will only pull records whose dates match the max date. HTH -----Original Message----- I have a linked table that has 3 fields. fldName fldHours fldDate This table is poulated by and external program designed to give hours of usage by month and is run to show the most recent month and the previous month's data. Therefore the Date field only has 2 values: 03/01/04 and 04/01/04. this would be for april and march's figures. Next month the table will be repopulated with April and may (04/01/04 and 05/01/04). without specifying a date as criteri in the query how can I get only the current month's data? . |
#3
|
|||
|
|||
Most Recent Date
Use a WHERE clause along the lines of
WHERE fldDate IN (SELECT Max(fldDate) FROM YourTable) Hope This Helps Gerald Stanley MCSD -----Original Message----- I have a linked table that has 3 fields. fldName fldHours fldDate This table is poulated by and external program designed to give hours of usage by month and is run to show the most recent month and the previous month's data. Therefore the Date field only has 2 values: 03/01/04 and 04/01/04. this would be for april and march's figures. Next month the table will be repopulated with April and may (04/01/04 and 05/01/04). without specifying a date as criteri in the query how can I get only the current month's data? . |
#4
|
|||
|
|||
Most Recent Date
Try this:
1. Add the following to the Field: row in a new column in your query's design: Month([fldDate]) 2. In the Criteria: row for that column, add the following: Month(Date()) hth, -- Cheryl Fischer, MVP Microsoft Access "Michael Noblet" wrote in message ... I have a linked table that has 3 fields. fldName fldHours fldDate This table is poulated by and external program designed to give hours of usage by month and is run to show the most recent month and the previous month's data. Therefore the Date field only has 2 values: 03/01/04 and 04/01/04. this would be for april and march's figures. Next month the table will be repopulated with April and may (04/01/04 and 05/01/04). without specifying a date as criteri in the query how can I get only the current month's data? |
#5
|
|||
|
|||
Most Recent Date
Cheryl,
Wouldn't this come up empty if the query runs in a month later than the latest data month (and even then, assuming that the computer clock is correct)? E.g.: running the March-April data in May, or running the April- May data in June. Would appreciate your take on my original reply; I tested on a a very small sample, but may have overlooked something. Respecfully, hcj -----Original Message----- Try this: 1. Add the following to the Field: row in a new column in your query's design: Month([fldDate]) 2. In the Criteria: row for that column, add the following: Month(Date()) hth, -- Cheryl Fischer, MVP Microsoft Access "Michael Noblet" wrote in message ... I have a linked table that has 3 fields. fldName fldHours fldDate This table is poulated by and external program designed to give hours of usage by month and is run to show the most recent month and the previous month's data. Therefore the Date field only has 2 values: 03/01/04 and 04/01/04. this would be for april and march's figures. Next month the table will be repopulated with April and may (04/01/04 and 05/01/04). without specifying a date as criteri in the query how can I get only the current month's data? . |
#6
|
|||
|
|||
Most Recent Date
Yes, it would. If you want to run the query in some month later than the
two months contained in your table, use a parameter in the Criteria: row [Enter month number to find:] This will cause a prompt which will allow you or your users to enter the desired month. -- Cheryl Fischer, MVP Microsoft Access wrote in message ... Cheryl, Wouldn't this come up empty if the query runs in a month later than the latest data month (and even then, assuming that the computer clock is correct)? E.g.: running the March-April data in May, or running the April- May data in June. Would appreciate your take on my original reply; I tested on a a very small sample, but may have overlooked something. Respecfully, hcj -----Original Message----- Try this: 1. Add the following to the Field: row in a new column in your query's design: Month([fldDate]) 2. In the Criteria: row for that column, add the following: Month(Date()) hth, -- Cheryl Fischer, MVP Microsoft Access "Michael Noblet" wrote in message ... I have a linked table that has 3 fields. fldName fldHours fldDate This table is poulated by and external program designed to give hours of usage by month and is run to show the most recent month and the previous month's data. Therefore the Date field only has 2 values: 03/01/04 and 04/01/04. this would be for april and march's figures. Next month the table will be repopulated with April and may (04/01/04 and 05/01/04). without specifying a date as criteri in the query how can I get only the current month's data? . |
Thread Tools | |
Display Modes | |
|
|