choose by MONTH and YEAR

[octatyan]

Hi, I'm not very good with Access so here's another (probably easy) question:

I want to create a report that asks you to choose a MONTH and YEAR. Here's my SQL in the query:

WHERE (EnteredDate = @Enter_month_year)

When I open up the report, it asks "Enter_month_year". So if I type in 1/2, I'm asking the report to pick all records with dates in January 2002 (the dates in the DATE column are formatted as dd/mm/yyyy if that matters). But for some reason it only selects dates of January 1, 2002. (Why only jan 1?).

I added to the query another constraint. You still choose the MONTH and YEAR but the report will also only select records in that choosen month that also contain 1a01, 1a02, or 1a03 in the COMMENTCODE column:

WHERE (EnteredDate = @Enter_month_year) AND (CommentCode = N'1a01' OR CommentCode = N'1a02' OR CommentCode = N'1a03')

For some reason, adding this new constraint messes up the ENTER_MONTH_YEAR thing. Now when I type in 1/2 as the MONTH_YEAR... it gives me the records for Jan 2, 2004. (but at least the commentcode constraint works).

Any help? Thanks

 

[raskew]

Hi-

Sounds like the DateValue() function should do the trick.

When you pass it a partial date, it will attempt to return

a complete date, normally assigning the day as the

first day of the month.


Problems arise when the date fragment is ambiguous, e.g.

? datevalue("4/4")---the question is 'Does this represent

the 4th day of the 4th month, or the 4th month of the

4th year?' In this situation, the function will substitute

the current year and return 4/4/04. You can get around

this by specifying mm/yyyy, "04/2004", or even "4/2004".


Having said that here's an example, based on Northwind's

Orders table, that will return all records from a given month.

SELECT
    Orders.*
  , Orders.OrderDate
FROM
   Orders
WHERE
   (((Orders.OrderDate) Between DateValue([enter mm/yyyy]) 
AND
   DateAdd("m",1,DateValue([enter mm/yyyy]))-1));

You might give it a try, copying/pasting this code to a new

Northwind query. When prompted, enter a month/year between

8/1994 and 6/1996 (the range of the Orders table).

HTH - Bob