Coming due date within 45 days

[AircrewX]

ok so i have been searching for about 8 days and cant quite get this to work for me.

I am trying to run a query that shows items in my database that are coming due within 45 days of a due date.

I tried using this criteria: <Date()+"45" And >Date()




I thought i had it working, but then wound that it wasn't can anyone help please?

[pbaldy]

Try

Between Date() And Date() + 45

[AircrewX]

No joy, just zero results, but there are plenty due within 45 days.

Thank you

[pbaldy]

Your image didn't come through, they have to be attached, not pasted into the reply area. What is the data type of the field? Can you attach the db here?

[Welshgasman]

You do not put 45 within quotes.
It is numeric, not a string.

[ano]

are u sure the field is a date? and not a formatted date

[AircrewX]

Picture attached

Attachment 52154

[AircrewX]

Sorry i cannot post table as the data is proprietary

[AircrewX]

are u sure the field is a date? and not a formatted date

Its a Short Text Field

[Welshgasman]

Its a Short Text Field

Firstly DO NOT STORE DATES as text.
Now you can try CDate() and see if that will work for you.

Best to fix the issue at source though.

[ano]

hope ur date in text field is mm/dd/yyyy or mmm/dd/yyyy else u have to change it to that unless it is yyyy/mm/dd

[AircrewX]

Firstly DO NOT STORE DATES as text.
Now you can try CDate() and see if that will work for you.

Best to fix the issue at source though.

This appears to have fixed my mistake thank you!

Changed field from text to Date and

<Date()+"45" And >Date()

works perfect now.

Thank you guys sorry for the silly question.

[Welshgasman]

Well it shouldn't as you are still adding a string.

OK it does, but do not rely on Access being so forgiving, so get used to using strings where strings should go and the same for numerics, else you will come to grief.

[AircrewX]

I do have a question in addition to this guys, how do i make sure the query includes the current month?

<Date()+30 And >Date()

Thank you

[CJ_London]

can you clarify what you mean -provide an example