Search by year

[UT227]

I'm trying to make a search by using only a year. I have several records with many different dates. I want to only see those in a certain year. I want every record in that year. I want to only use the four digit year instead of inputting a beginning and ending date.

[andy49]

Are the dates stored in time/date field?

[UT227]

Yes. Dates are stored in the table as Date/Time

[orange]

What is the Date field you intend to query??

...where Year (yourDatefield) = 9999

[UT227]

Sorry Orange, I didn't quite understand. I'm trying to query the date that is on the record. It's a regular short date field. There are many records with different dates and years. I want to be able to look at all the records with the year I need to search. Example, if I want to see everything from 1/1/2016 - 12/31/2016, I want to be able to search for 2016 and get everything.

[orange]

A query

Select * from YourTableNameGoesHere
where year(yourFieldNameGoesHere)= 2016

[UT227]

Hate to say this, but it looks like SQL. I'm not very good with SQL. Can I write it into the query criteria?

[ssanfu]

Or....

You didn't state the field name. So in the following change "yourDatefield" to your actual field name.

In the query add a new column-> OnlyYear:Year (yourDatefield)
In the Criteria row, enter-> 2016
Run the query.....

[orange]

Agree.
Thnx Steve.

[UT227]

Oops ssanfu. The field name is OpenDate

[UT227]

That worked great. Thanks. Having one more issue. I have a form I use to enter what I'm looking for. When I enter this into the criteria, it stops working:
[Forms]![frmSearchIAComplaintClass]![Year]. I'm trying to have it look for whatever year I enter into the text field YEAR.

[ssanfu]

Shouldn't use "Year" as an object name. "Year" is a reserved word and a build in function.

When I enter this into the criteria, it stops working:

Explain "stops working". Is there an error message?


Can you attach the dB? With maybe 10 - 20 records for testing purposes?
Do a "Compact & Repair", then Zip it.

[UT227]

Thanks again. I had forgotten about the reserved words. No error messages. I changed the name to txtYrSearch. Still the same results though. The query just comes up blank. I'll post some pictures.

[UT227]

There was something wrong with my search form. I redid it from scratch. It works like a charm. I appreciate the help. Thanks.

[ssanfu]

Glad you got it sorted out......