qry Equation in Access 2003

[Jacob]

I have a report that runs off of a query. I am pulling data that is listed
in the report based on the accounting numbers. What accounting needs, is a
way to pull the last 6 moths activity included items that have been removed
from stock. when I put my between funcion on the removed date, it will ONLY
pull the items that have been removed. Is there a way to pull both the ones
that have been removed as well as the ones that are still present, in other
words, that have a null value in that date field? Or maybe I am going about
this all wrong?


here are my fields I am working with...


Date Purchased-Make-Model-Type-Cost-PO-ID-DepVal-DateSold

Any help is greatly appreciated.

 

[Dale Fye]

what does your query look like now?

I don't see a [RemovedDate] in the list of fields you are using?

 

[Tom van Stiphout]

On Mon, 10 Aug 2009 08:53:00 -0400, "Jacob" <[email protected]>
wrote:

You can use:
IsNull(myDate) or myDate >= dateadd("m", -6, myDate)
(of course you replace myObjectNames with yours)

-Tom.
Microsoft Access MVP

 

[Jacob]

Tom, I am getting a type mismatch error when I try to run this. I am trying
to use it in the DateSold field. So my expression looked like this....

IsNull("Date Sold") Or "Date Sold">=DateAdd("m",-6,"myDate")

could it be the spaces?

 

[Jacob]

Correction, I updated my criteria to be...

IsNull(Date Sold) or Date Sold>= dateadd("m", -6, Date Sold)

Sorry about that. I still get a DataTypeMismatch

 

[Jacob]

My actual removedate is "DateSold" My appologies.

what does your query look like now?

I don't see a [RemovedDate] in the list of fields you are using?

----
Dale

I have a report that runs off of a query. I am pulling data that is
listed
in the report based on the accounting numbers. What accounting needs, is
a
way to pull the last 6 moths activity included items that have been
removed
from stock. when I put my between funcion on the removed date, it will
ONLY
pull the items that have been removed. Is there a way to pull both the
ones
that have been removed as well as the ones that are still present, in
other
words, that have a null value in that date field? Or maybe I am going
about
this all wrong?


here are my fields I am working with...


Date Purchased-Make-Model-Type-Cost-PO-ID-DepVal-DateSold

Any help is greatly appreciated.

 

[Jacob]

When I put this in the FIELD list

Date Sold: IsNull("DateSold") Or ("DateSold")>=DateAdd("m",-6,"DateSold")

I get Data Type Mistmatch in Critiria.

If I put this in the field...

Date Sold: IsNull("DateSold") And ("DateSold")>=DateAdd("m",-6,"DateSold")

It runs without adding the fields that have been removed...it still only
brings back the null values.

I am against the wall on trying to figure this out....I keep trying
different things to make it work...I appreciate any and all help you guys
can offer me.

Jacob

 

[Douglas J. Steele]

Remove the quotes from around the field name:

IsNull([DateSold]) Or ([DateSold]>=DateAdd("m",-6,[DateSold]))

Note that I also corrected your parentheses.

 

[Jacob]

Thank you Doug...that did the trick. I appreciate all you guys help and
support for my periodic questions.

Remove the quotes from around the field name:

IsNull([DateSold]) Or ([DateSold]>=DateAdd("m",-6,[DateSold]))

Note that I also corrected your parentheses.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

When I put this in the FIELD list

Date Sold: IsNull("DateSold") Or ("DateSold")>=DateAdd("m",-6,"DateSold")

I get Data Type Mistmatch in Critiria.

If I put this in the field...

Date Sold: IsNull("DateSold") And
("DateSold")>=DateAdd("m",-6,"DateSold")

It runs without adding the fields that have been removed...it still only
brings back the null values.

I am against the wall on trying to figure this out....I keep trying
different things to make it work...I appreciate any and all help you guys
can offer me.

Jacob