Most Recent Date

[Michael Noblet]

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?

 

[hcj]

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

 

[Gerald Stanley]

Use a WHERE clause along the lines of

WHERE fldDate IN (SELECT Max(fldDate) FROM YourTable)

Hope This Helps
Gerald Stanley MCSD

 

[Cheryl Fischer]

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,

 

[Guest]

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

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?

.

 

[Cheryl Fischer]

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

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

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?

.