I would like to run reports from data going back several years. I have found this expression from the help pages:
Like"*01"
If I use this in the date field criteria cell
it returns all records with a date in 2001
Is it possible to change the expression so that when the query is run it promps for user imput ie to be able to change the year?
btw may date field is formatted to short date
dd/mm/yy
thanks

[This message has been edited by jon92 (edited 09-15-2001).]

[This message has been edited by jon92 (edited 09-15-2001).]

in the criteria cell put an expression like the following:

Like "*" & [enter ending year]

this will bring up a message box with a prompt of whatever you put between the [ ].

if your date format is dd/mm/yy (typo?) or mm/dd/yy this should work ok.

hth,
al

Dates are stored internally as serial numbers with the integer portion representing the date and the decimal portion representing the time. Negative values are used for dates prior to Dec 31, 1899. The only thing the date format affects is how a date is displayed. It has nothing to do with how the date is stored.

Becauese you have used the Like operator, Access has assumed you want the criteria applied to a text string and has formatted the date by using the default date format which normally has the year as the final component. However, there are other date formats and if any of your users has chosen one, this query will not select the proper records for them.

The correct technique is to use a function that extracts the year portion of the date field. Use either Year(YourDate) or DatePart("yyyy", YourDate). So your query would look like:

SELECT datefld
FROM Table1
WHERE Year(datefld)=2001;

thanks pcs & pat - just what I need
btw dd/mm/yy not a typo - I'm in the UK !