qry Equation in Access 2003

  • Thread starter Thread starter Jacob
  • Start date Start date
J

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.
 
what does your query look like now?

I don't see a [RemovedDate] in the list of fields you are using?
 
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
 
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?
 
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
 
My actual removedate is "DateSold" My appologies.








Dale Fye said:
what does your query look like now?

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

----
Dale



Jacob said:
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.
Click to expand...
Click to expand...
 
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
 
Remove the quotes from around the field name:

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

Note that I also corrected your parentheses.
 
Thank you Doug...that did the trick. I appreciate all you guys help and
support for my periodic questions.




Douglas J. Steele said:
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!)


Jacob said:
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
Click to expand...
Click to expand...
 
Back
Top