Date query working incorrectly (Reports - Charts)

[davie1982]

Hiya.

I have this code in a chart on one of my reports in the SQL query as a criteria.

>=[forms]![frmdatestuff]![txtstartdate] And <=[forms]![frmdatestuff]![txtenddate]

For some reason, if i want the chart to display for example 14.12.11 > 16.12.11 I place 14/12/11 in the [txtstartdate] input box and 16/12/11 in the [txtenddate] box. The problem is that the chart starts from 15/12/11. This isn't what I want. I'm having to put 13/12/11 in [txtstartdate]. It's like it's not registering the "=" in ">=". However, the end date works fine. Can anyone tell me where i've gone wrong?

 

[Norie]

Try this.

BETWEEN "#" & [Forms]![frmdatestuff]![txtstartdate] & "#" And "#" &[Forms]![frmdatestuff]![txtenddate] & "#"

 

[davie1982]

Hi i tried that, but the chart is blank It still does the same thing.

 

[Norie]

Does the query for the Row Source of the chart return the expected records?

 

[davie1982]

No. I want to bring up 6,7 and 8 november but doing 06/12/11 in start and 08/12/11 in end only brings up 7 and 8. Start and end are both unbound textboxes. I press a button that opens up a report with a chart and the query is the chart's query. The chart is empty when there is no information.

 

[Norie]

Isn't 06/12/11 December rather than November?

Does the query work if you enter the dates manually?

Between #6 Nov 2011# And #8 Nov 2011#

 

[davie1982]

Ah there will be the problem... because of the nature of the front end system. To enter it manually like that will defeat the whole purpose as the dates for the report will often vary and many other users are using it. At the moment a nice little message is next to the start field saying to input the date the day before they want it to start.. and people just don't read. So I'm trying to fix that issue. To have it entered manually means to open it with shift, click on reports, go in design view, bring up the properties, click on the chart, and change it in the sql query.

The query does work.

 

[davie1982]

If it helps.. here's the sql code

SELECT (Format([fldDate],"Short Date")) 
AS Expr1, Sum(qrySiloTotals.Fastpick) 
AS [Fast pick], Sum(qrySiloTotals.[Silo 1]) 
AS [Silo 1], Sum(qrySiloTotals.[Silo 2]) 
AS [Silo 2], Sum(qrySiloTotals.[Silo 3]) 
AS [Silo 3], Sum(qrySiloTotals.[Silo 4]) 
AS [Silo 4] 
FROM qrySiloTotals 
GROUP BY (Format([fldDate],"Short Date")), (Int([fldDate])) 
HAVING ((((Int([fldDate])))>=[forms]![frmdatestuff]![txtstartdate] And ((Int([fldDate])))<=[forms]![frmdatestuff]![txtenddate]));

 

[Norie]

I wasn't suggesting the user enter the dates manually.

I was suggesting you try doing it to test.

You need to enclose the (text) values from the textboxes in #s.

 

[guerillaunit]

konichiwa!

Replace your code with this:

Between [forms]![frmdatestuff]![txtstartdate] And [forms]![frmdatestuff]![txtenddate]