Query data from two different date fields

[ones_zero]

Hello


I have a table that has two different date fields
- Date Reported
- Date Completed

Currently i'm querying the data from the "Date Completed" field by using the criteria in the Date Completed query column Like "*" & [Enter Year] & "*"
I'm only querying the year from the date field "Date Reported" The date format is for example 2/13/2021

How can i add criteria to the "Date Reported" column to query this data by the year in the "Date Reported" column.

When I query this data "Building Issues" i'd like to query this data by the year from the columns "Date Reported" and 'Date Completed"



​Thanks,

[ranman256]

In the query,Add another field to filter on:
Year([date completed])

Put your parameter box there as criteria:
[Enter year]

[Bob Fitz]

ones_zero

Not sure of your exact requirements. The attached example may help

[ssanfu]

Is the data type of those two fields Date/Time or TEXT?

Do you want Building_Issues AND Date_Reported = 2020 AND Date_Completed = 2020?

SELECT tblWork_Tracker.Date_Completed, tblWork_Tracker.Date_Reported, tblWork_Tracker.Employee, tblWork_Tracker.Building_Issues
FROM tblWork_Tracker
WHERE (((tblWork_Tracker.Building_Issues)=Yes) AND ((Year([Date_Completed]))=[enter year]) AND ((Year([Date_Reported]))=[enter year]));

Or

Do you want Building_Issues AND (Date_Reported = 2020 OR Date_Completed = 2020)?

SELECT tblWork_Tracker.Date_Completed, tblWork_Tracker.Date_Reported, tblWork_Tracker.Employee, tblWork_Tracker.Building_Issues
FROM tblWork_Tracker
WHERE (((tblWork_Tracker.Building_Issues)=Yes) AND ((Year([Date_Completed]))=[enter year])) OR (((tblWork_Tracker.Building_Issues)=Yes) AND ((Year([Date_Reported]))=[enter year]));

[CJ_London]

to add an explanation, dates are stored as decimal numbers, what you see is a formatted view of that number.

The bit before the decimal point is the number of days since 31/12/1899. The bit after is the time in number of seconds to now divided by 86400, the number of seconds in 24 hours

so now is
?cdbl(now()) 44241.3880671296

and you can covert a number to a date
?cdate(34567)
21/08/1994
?cdate(34567.12345)
21/08/1994 02:57:46

- you can see your Like criteria won't work

so you use the year function to get the year

?year(34567.12345)
1994

[ones_zero]

Ok let me make sure I understand.
Since my goal is to query data from the “Building Issues” table by the Year only from the “Dates Reported” & “Dates Completed” columns in the table.
- I would and this criteria under “Date Reported”? Year([date completed])

- I currently have this criteria under “Date Completed” & it works. Like "*" & [Enter Year] & "*"
I just need to also filter the Building Issues data by “Date Reported” by year only like I’m doing under Date Completed.

That’s for your patience, I’m no Access expert but I’m learning

Thanks again for helping

[CJ_London]

OK - I stand corrected re like - not a way I would go but it's your app

with regards the rest, you need to answer the question Steve asked in post #4. Would also be courteous to try his suggestions and provide feedback

[ones_zero]

Ok thanks..I will give it a try.
Question, would I insert this in the criteria section of the query?

SELECT tblWork_Tracker.Date_Completed, tblWork_Tracker.Date_Reported, tblWork_Tracker.Employee, tblWork_Tracker.Building_Issues
FROM tblWork_Tracker
WHERE (((tblWork_Tracker.Building_Issues)=Yes) AND ((Year([Date_Completed]))=[enter year]) AND ((Year([Date_Reported]))=[enter year]));

[ones_zero]

I thought this would be simple as adding another Criteria filter under the "Date Reported" but I cant seem to get it working
It needs to be AND to include both the "Date Reported" and "Date Completed" Year
Maybe the Like "*" & [Enter Year] & "*" is not the best way to filter in the criteria, but im not sure how to pull the year from the dates from both
thanks again for helping

[CJ_London]

cant seem to get it working

what does that actually mean?

im not sure how to pull the year from the dates from both

you've been shown a couple of times - use the year function

[Gicu]

Here is one simple way, add a new field like shown here:

[ones_zero]

Gicu....I have been using you guys guidance.
When I say I can’t get it to work that’s what I mean, I can’t get it working I’m doing something wrong...also when I say I’m no Access expert, that also means that you may have to be be more specific with your responses.

I have asked how to insert the code from one of the above responses, but I’ve not been giving any guidance for this.

All I know is how to add it to the criteria section of the query. Again I’m no Visual Basic or Access expert like you guys but I’m trying

Sorry to bother you, but I appreciate your help and time

[davegri]

See image below:


Edit: Looking closer at the image, which was copied from your post #9, indicates that it is a mashup, not an actual query image. Have you tried the actual query that the image represents? That works OK in my tests.

[davegri]

Added important edit comment in post #13.

[ones_zero]

Hello davegri
If i remove the "Yes" which is a Yes/No Data type. I do receive data, but only by the year from the "Date Completed" column
If I put Like "*" & [Enter Year] & "*" as the criteria under "Date Reported" and Like "*" & [Enter Year] & "*" in the ""Date Completed" criteria filter and add Yes back to the criteria under "Building Issues" so it would filter the year out of Date Reported and Date Completed for the query Building Issues.
I dont receive any data results from this query.

I have issues i guess understanding how to setup my query to filter the Year only by both the "Date Reported" and "Date Completed" columns in the Work_Tracker Table
It will work if i try to only filter by one or the other but not by both

Note: I've tried the query from the post#13 image and i receive No data back from the query and I know i should have data from year 2021 from the 'Date Reported" and the "Date Completed" columns
I leave the Yes in the criteria for Building Issues to filter this data. It is a Yes/No data type. If i take Yes out, I still only receive data from the "Date Completed" column



thanks,