Coming due date within 45 days

[AircrewX]

I cannot provide an example, due to proprietary info, but the following code gives me all of my dates coming due but excludes the current month.



<Date()+30 And >Date()

so it checks 30 days before todays date and 3 days after todays date, but the current mount is missing.

[AircrewX]

i can try, so i run a calibration due within 30 days and the results show:

Octobers due items.




But i don't see the items due from the now until the end of September.

[Welshgasman]

Well you only have month and year?
I would have also thought you would need <= and >= else you will miss days?

[CJ_London]

You do know you can make up an example? e.g.

today is 25th September and based on your formula

<Date()+30 And >Date()

that would be <25th October and >25th September

i.e. all dates between 26th September and 24th October

I don't see any proprietary data in that example.

how do i make sure the query includes the current month?

- so modify my example

today is 25th September - suggest any date

that would be <25th October and >25th September - what dates do you actually want?

and do mention anything that also needs to be taken into account - such as allowing for weekends, bank holidays, staff absence etc

And the example you provided show only a month/year anyway - is that due to formatting, or is it not a date field? What is the day? is there a time element in there you have hidden away? We need to see the actual data - or an example of the actual data

[AircrewX]

Apologies for making this overly complicated.

Here is what i am trying to get my query to accomplish.

we have calibration due dates and we want to query the database to look at the due date column and scan back 30 days and forward 30 days and show any records that fall within that range.

I currently have this as my criteria: <Date()+30 And >Date() , but it is not including the current month in the results.

maybe you guys can help me figure out how to unsure it includes the current month in the results.

Thank you in advance.

[Welshgasman]

You are going to have to load a DB with the table with the dates and your code.
That is all. We do not need anything else but the dates in the table and your code.

Reason: What you say, is not possible.

Here is your criteria on a table of mine

{code]
SELECT tblDates.*, tblDates.StepsDate
FROM tblDates
WHERE (((tblDates.StepsDate)<Date()+30 And (tblDates.StepsDate)>Date()));


[/code]

[CJ_London]

since you cannot even provide a made up example or clarify what your data actually is, I'm going to drop off this thread as simply repeating what you have already said, albiet the parameters seem to have changed from +45 to +30 and -30, is a waste of everyone's time

[pbaldy]

Might this be what you're after?

<Date()+30 And > DateSerial(Year(Date()), Month(Date()), 1)

[AircrewX]

Welshgasman - You are right i didn't think of creating a fake db for help, i have attached one.


Here is what im trying to do.

I have two due date queries 30 day and 45.

30 Day report i want to scan the database get the results to show anything past due date and coming due within 30 days of current date.
45 Day report i want to scan the database get the results to show anything past due date and coming due within 45 days of current date.

Both appear to be functioning somewhat, but the reports do not show the overdue (past due date) or the current months coming due.

I hope the attached file will help you guys assist me.

Thank you in advance.

[Welshgasman]

TBH, I am not sure of your requirement.
Anything past due date, to me, would be anything with a date less than today?
Anything due withing a number of days would be date + that number of days?

So if I wanted to see anything that was due 30 days in the past or due in 30 days I would use
WHERE (((Equipment.[C,Due Date])<Date()+30 And (Equipment.[C,Due Date])>Date()-30));

which would give

[AircrewX]

This seems to be exactly what i needed, Thank you!
I will report back if any issues.