Make a statement

[Retow]

quick help if at all possible.

ASPX / Access

I have a table that stores the date (dd/mm/yyyy) in one column and the name of the person assined to that row in another.

Its currently setup in excel but id like something avail to everyone and hosted off a spare workstation here.

I would like to count how many records are assigned in total for the month and then a total for the selected year.

Simlar to

NAME DATE
bob 22/06/2006

There are two criteria month and year these can be seperate queries as they will be shown at the same time.

If i select 06 as the month it will count 1 and return that value to a label called June.

I have two txt boxes so I can enter a month value and a year value, and when theyre blank then it just selects * from the table.

Any ideas as i have little experiance referencing dates or something similar on the net!

 

[JIMA]

I might be able to help with the SQL part for this.....

For the SQL in Access try using the datepart function to extract parts of the dates.

As an example, if I have a table with a column mydate I could use the following to retrieve records where the year is = 2006:

SELECT *
FROM datetable
WHERE datepart("yyyy",mydate)=2006;

Or the following to get dates where the month is May or later:

SELECT *
FROM datetable
WHERE datepart("M",mydate)>5;

You can combine the two Where clauses to restrict on month and year together e.g after May in the year 2006.

SELECT *
FROM datetable
WHERE datepart("M",mydate)>5
AND datepart("yyyy",mydate)=2006;

Hope that helps.

Jim

 

[Retow]

Thats grand thanks!