Entering a Year in a parameter query

[Guest]

Ignorant Question:
I am tracking a date of the format m/d/yyyy and I need to write a paramter
query that enables the user to enter only the year. I tried entering the
following in the criteria row (for a date/time field named dtmOrderDate).
Criteria I tried:: like [Enter the year] & "*"

This only worked for being able to enter the month (which is what I
expected) but I can't figure out how to structure the criteria to make it so
I only need to enter the year. I'm sure a bigger mind than my own has a
solution. Please help

 

[Guest]

Like"*"&[What year fo you want]&"*"

HTH

 

[Guest]

Try something like

Select TableName.* From TableName
Where Year([DateFieldName]) = [Enter the year]

So the criteria is [Enter the year] under the field Year([DateFieldName])
that return the year from the date

 

[Guest]

Try parsing out the year for the parameter. e.g.

SELECT Table1.Item, Table1.dtmOrderDate
FROM Table1
WHERE (((DatePart("yyyy",[dtmOrderDate]))=[Enter the Year]));

Of course, change the SELECT part to match your desired fields and the FROM
to match your table name.

HTH,

JoeD

 

[Mark Layne]

Use the year function on your date and put it in a separate column in your
query. Then put your criteria in that column.

select date, year(date) as yr from tablename where (((Year([date]))=[enter
year]));

 

[Guest]

Hi Wayne,
Thank you! This worked. Maybe I am being obtuse but it looks like
positionally we are entering the day and not the year. Would you please
interpret this for me.
High appreciated
Bill

Like"*"&[What year fo you want]&"*"

HTH


--
Wayne
Manchester, England.

Ignorant Question:
I am tracking a date of the format m/d/yyyy and I need to write a paramter
query that enables the user to enter only the year. I tried entering the
following in the criteria row (for a date/time field named dtmOrderDate).
Criteria I tried:: like [Enter the year] & "*"

This only worked for being able to enter the month (which is what I
expected) but I can't figure out how to structure the criteria to make it so
I only need to enter the year. I'm sure a bigger mind than my own has a
solution. Please help

 

[Guest]

It will find 1999 or 2007 or 1892 etc etc if it is in the field (somewhere).

As you may note - I tend to go for the simplest answer - less to mess up.

Good luck


--
Wayne
Manchester, England.

Hi Wayne,
Thank you! This worked. Maybe I am being obtuse but it looks like
positionally we are entering the day and not the year. Would you please
interpret this for me.
High appreciated
Bill

Like"*"&[What year fo you want]&"*"

HTH


--
Wayne
Manchester, England.

Ignorant Question:
I am tracking a date of the format m/d/yyyy and I need to write a paramter
query that enables the user to enter only the year. I tried entering the
following in the criteria row (for a date/time field named dtmOrderDate).
Criteria I tried:: like [Enter the year] & "*"

This only worked for being able to enter the month (which is what I
expected) but I can't figure out how to structure the criteria to make it so
I only need to enter the year. I'm sure a bigger mind than my own has a
solution. Please help

 

[Jeff Boyce]

Are you saying that your field is defined as a text type, and holds,
literally, "11/1/2007" (example)? Or are you saying that the field is
defined as a date/time field, and is formatted to display "11/1/2007"?

There's a difference between these two, and a difference in how you'd
accomplish what you want to...

Regards

Jeff Boyce
Microsoft Office/Access MVP