WhereCondition not working

[Perceptus]

Code to run my report

                Dim x$
                x$ = "([StartDate] >=#" & Text0 & "# and [EndDate]<=#" & Text0 & "#)"
                stDocName = "rptInventoryAging"
                DoCmd.OpenReport stDocName, acPreview, , x$, acWindowNormal, "Inventory Aging for " & Text0

When the report loads I get rows where the start and enddate dont match text0 input.(it actually looks like im getting the whole query without any constraints from the where condition.

The report doesn't have anything special to it, Filter is off, Sort is off datasource is a query
sample data in query

PartNumber
SerialNumber

Qty
StartDate
EndDate
LongDescription

80-10-08TS1WR080B


9
1/31/2007
6/11/2007
UNF 8 T WATER RING (8T)

80-10-08TS1WR080B


10
12/18/2007
5/15/2008
UNF 8 T WATER RING (8T)

80-10-08TS1WR080B


36
5/9/2007
9/27/2007
UNF 8 T WATER RING (8T)

80-10-08TS1WR080B


1
10/7/2011
10/22/2012
UNF WATER RING 8T

80-10-08TS1WR080B


1
10/7/2011
5/30/2012
UNF WATER RING 8T

80-10-08TS1WR080B


2
8/30/2010
7/7/2011
UNF WATER RING 8T

80-10-08TS1WR080B


5
10/30/2009
2/23/2011
UNF WATER RING 8T

80-10-08TS1WR100B


1
6/30/2011
12/28/2012
UNF WATER RING 10TS

80-10-08TS1WR100B


1
9/15/2011
12/28/2012
UNF WATER RING 10TS

80-10-08TS1WR100B


24
10/1/2012
12/28/2012
UNF WATER RING 10TS

80-10-08TS1WR100B


1
1/31/2007
1/28/2009
UNF WATER RING,10TS (10 TS WR)

80-10-08TS1WR100B


1
1/31/2007
10/17/2008
UNF WATER RING,10TS (10 TS WR)

80-10-08TS1WR100B


1
1/31/2007
10/19/2009
UNF WATER RING,10TS (10 TS WR)

80-10-08TS1WR100B


1
1/31/2007
11/3/2009
UNF WATER RING,10TS (10 TS WR)

80-10-08TS1WR100B


1
1/31/2007
3/31/2008
UNF WATER RING,10TS (10 TS WR

[alansidman]

What happens when you run the query from the QBE using the same criteria? I suspect that you need to identify the control text0 in the following syntax in the query itself in the WHERE clause

Forms!YourFormName.text0

Alan

[Perceptus]

When I enter this into the immediate window after x$= line

?x$

I get

([StartDate] >=#12/27/2012# and [EndDate]<=#12/27/2012#)

I think think my logic is off, but if the where condition did not work i would get no rows returned i think.

[RayMilhon]

Is there a reason you use the Where Clause in the docmd statement instead of in the actual query?

[Perceptus]

Yes, the query contains a master list of the data.

I have a form that asks the user for a date. The user enters a date. That date is text0.

Then the idea is you only see the dates of the items where the start is lessthan or equal to text0 and the end is greaterthan or equal to text0.

I have also tried between, text0 between [startdate] and [enddate]

Am I skinning the cat with the wrong spoon?


edit
also tried the using where in query instead

qryInventoryAging is a union query made from https://www.accessforums.net/queries...tml#post152247

SELECT qryInventoryAging.PartNumber, qryInventoryAging.SerialNumber, qryInventoryAging.Location, qryInventoryAging.Qty, qryInventoryAging.UnitTotalCost, qryInventoryAging.ExtendedTotalCost, qryInventoryAging.StartDate, qryInventoryAging.EndDate, qryInventoryAging.LongDescription
FROM qryInventoryAging where [qdate] between [qryInventoryAging.StartDate] and [qryInventoryAging.EndDate]

i entered 12/27/2012 when prompted by the query. Same results, I see enddates that are less than the qdate.

[Perceptus]

I changed the union to look like

SELECT * FROM qryInventoryCurrentAging where [qdate] between [startdate] and [enddate]
Union
SELECT * FROM qryInventoryHistoryAging where [qdate] between [startdate] and [enddate]
UNION
SELECT * FROM qryWIPAging where [qdate] between[startdate] and [enddate]
UNION SELECT * FROM qryWIPHistoryAging where [qdate] between [startdate] and [enddate];

and this works. If anyone has an idea as to wtf and the report I would love to be enlightened.

haven't tried with report yet. still reviewing data.

[ssanfu]

([StartDate] >=#12/27/2012# and [EndDate]<=#12/27/2012#)

If EndDate has to be greater than StartDate, the only time that the expression would be true is if StartDate and EndDate were both 12/27/2012.

Then the idea is you only see the dates of the items where the start is lessthan or equal to text0 and the end is greaterthan or equal to text0.

This is different than the expression above. This would be

([StartDate] <=#12/27/2012# and [EndDate]>=#12/27/2012#)


I would think this would work...

Dim x$

x$ = "([StartDate] <=#" & Text0 & "# and [EndDate]>=#" & Text0 & "#)"
stDocName = "rptInventoryAging"
DoCmd.OpenReport stDocName, acPreview, , x$, acWindowNormal, "Inventory Aging for " & Text0

but I prefer to use the "between " syntax...

x$ = Text0 & " between [StartDate] and [EndDate]"

[Perceptus]

Im not sure if my autism shows when i try to explain things xD, but thanks to everyone for help.

use of the BETWEEN in the Query gave me what i needed.

[ssanfu]

Excellent.