This is killing me!

[Liam.M]

Hey there, I have a problem that I would very much appreciate some
assitance with....currently I am trying to Query my database based on a
field "Due Date",
I wish to check the field "Due Date" to see if this Date is within the
next two months,
Currently this Due Date value is passed from a calculation that I have
created in my field "Date of Issue". This calculation, works out form
the value entered in the "Date of Issue", when the "Due Date" is going
to be (DateAdd("yyyy",1,ShipsInformation.[Date of Issue]) AS [Due Date
) this is done within the Query.

WHat I am trying to do...is now Query the Database to see if this "Due
Date" value is within the next two months, so the database will only
show the records that fall within this criteria and exclude the
rest....currently I am trying to use this calculation:

WHERE ShipsInformation.[Due Date]) Between DateAdd("m",+2,Date()) And
Date()));

Any suggestions....your help will be much appreciated!

Kind Regards,

Liam.

 

[John Vinson]

Hey there, I have a problem that I would very much appreciate some
assitance with....currently I am trying to Query my database based on a
field "Due Date",
I wish to check the field "Due Date" to see if this Date is within the
next two months,
Currently this Due Date value is passed from a calculation that I have
created in my field "Date of Issue". This calculation, works out form
the value entered in the "Date of Issue", when the "Due Date" is going
to be (DateAdd("yyyy",1,ShipsInformation.[Date of Issue]) AS [Due Date
) this is done within the Query.

As I said previously...

YOU CANNOT DO IT THIS WAY.

You *cannot* reuse the calculated Due Date field in a further
calculation. Access simply does not support doing so.

WHat I am trying to do...is now Query the Database to see if this "Due
Date" value is within the next two months, so the database will only
show the records that fall within this criteria and exclude the
rest....currently I am trying to use this calculation:

WHERE ShipsInformation.[Due Date]) Between DateAdd("m",+2,Date()) And
Date()));

If the Due Date is one year after the Date of Issue, then you can base
the criterion directly on the date of issue:

Try a criterion

WHERE [ShipsInformation].[Date of Issue]
BETWEEN DateAdd("m", -12, Date()) AND DateAdd("m", -10, Date())


John W. Vinson[MVP]

 

[Liam.M]

John, your a life saver! THANKYOU SOOOOOOO MUCH....this has resolved
the issue....very much appreciated....you have EVERY single time,
helped me with my problem(s) in a very professional and timely manner,
RESPECT!

Kind Regards,

Liam Murphy.

Hey there, I have a problem that I would very much appreciate some
assitance with....currently I am trying to Query my database based on a
field "Due Date",
I wish to check the field "Due Date" to see if this Date is within the
next two months,
Currently this Due Date value is passed from a calculation that I have
created in my field "Date of Issue". This calculation, works out form
the value entered in the "Date of Issue", when the "Due Date" is going
to be (DateAdd("yyyy",1,ShipsInformation.[Date of Issue]) AS [Due Date
) this is done within the Query.

As I said previously...

YOU CANNOT DO IT THIS WAY.

You *cannot* reuse the calculated Due Date field in a further
calculation. Access simply does not support doing so.

WHat I am trying to do...is now Query the Database to see if this "Due
Date" value is within the next two months, so the database will only
show the records that fall within this criteria and exclude the
rest....currently I am trying to use this calculation:

WHERE ShipsInformation.[Due Date]) Between DateAdd("m",+2,Date()) And
Date()));

If the Due Date is one year after the Date of Issue, then you can base
the criterion directly on the date of issue:

Try a criterion

WHERE [ShipsInformation].[Date of Issue]
BETWEEN DateAdd("m", -12, Date()) AND DateAdd("m", -10, Date())


John W. Vinson[MVP]