Query data from two different date fields

[Gicu]

Have you tried my suggestion? Put the criteria (Like "*" & [Enter Year] & "*") in one of the dates (say [Date Reported]) and add a new field to the query: Year([Date Completed). In the criteria row add Year([Date Reported]) and uncheck the Show check box if you don't want to see it in the results.


Also try True instead of Yes for the BuildingIssues field.
Cheers,

[ones_zero]

Hello davegri
Here a screen shot from the query. I have slide over the other so you can see everything
When i use this configuration it ignores the Yes in the Building Issues filter and populates everything with a say fro example search of 2021 in the "date reported" or "Date completed"



If i add this search criteria in the Criteria filter or the Or filter the data populated isnt correct.
I need to search by AND to include both the 'Date reported" and "date completed" year

I'm close but im missing something
thanks

[davegri]

Can you post an image of the table in design view?

Edit: In post #17 you also need YES in both rows of the criteria.

[Gicu]

Why do you want to get prompted twice for the same year? My approach uses only one prompt and takes the year of the first filtered date and filters the year of the other.

Cheers,

[ones_zero]

Hello Gircu
I have tried your response, but i havent had any luck getting it to work

Have you tried my suggestion? Put the criteria (Like "*" & [Enter Year] & "*") in one of the dates (say [Date Reported]) and add a new field to the query: Year([Date Completed). In the criteria row add Year([Date Reported]) and uncheck the Show check box if you don't want to see it in the results.
Also try True instead of Yes for the BuildingIssues field.
Cheers,

I havent tried the "Building Issue" filter as True instead of Yes. I really like the one prompt if i can get it to work
Let me try again and ill post back

thanks

[ones_zero]

Hello Gicu,
I added what you have said but still doesn't populate any data for me when search by year 2020 or 2021. I know i have data in both "Date reported" and "Date Completed" by this year



Do you see anything i did wrong in the screen shot?
The screen shot shows the checkbox selected under "Expr1: Year([Date Completed])" but it didnt seem to make any difference

thanks again

[Gicu]

It looks good, can you check if both fields are defined as dates in the Work_Tracker table? Is there other table involved in this query?

Please have a look at the attached sample where I think the query works as expected (I had some old sample data, use 2004 or 2005 for year).

[ones_zero]

Thanks Gicu
I see one difference in my table is that there is times when the "Date Completed" will be Null and the "Date Reported" will have say 2-14-2021 date
If I want to query both "Date Reported" and "Date Completed" and the there is no date in the Date Completed column how would i make sure it captures both data.

Could this be my issue

[ones_zero]

I copied exactly what you had in your example but I'm still not populating data from both "Date Reported" or "Date Completed"
Here is the Design View of my table. Everything looks correct and matches your table setup

[Gicu]

Can you upload a small sample with your table, please remove any sensitive info, just need the three columns and the primary key? Do you have ANY records where both dates have the same year?

Cheers,

[Gicu]

Have a look at his one for how to deal with the Nulls in one date.

Cheers,
Vlad

[ones_zero]

Hello Gicu
Attached is a sample of the database
I have dates in both "Date Reported" and "Date Completed"
I have the query configured how i originally had it and left for you to add you magic to it.
When i query "Building Issues" with a year say for example 2021, i would like to populate Building Issue Data from both "Date Reported" and "Date Completed" for that year or if i query year 2020 Id like to populate data from both fields as well and so forth

I appreciate your help and time

[CarlettoFed]

This should lead to the desired result Work Tracker Sample.zip

[Gicu]

Here is an update with a combo for the year (instead on the query prompt).
Cheers,
Vlad

[ones_zero]

Thank you very much CarlettoFed & Gicu for helping!! Very much appreciate your time and help.
The query works like I need it to.

Thanks again