Report between dates does not work 2

[Niebotel]

I have created a report based on the following query:
.....HAVING (((Totalen.Datum)>=[Forms]![Form_menu_finance].[DatumStart] And (Totalen.Datum)<=[Forms]![Form_menu_finance].[DatumEind]))

I enter the parameters on the form 29-11-2011 and 30-11-2011

If I run the query it only returns the first record from the two which are in the table:
Totalen.Datum
29-11-2011 10:45:22 € 0,00 € 4,00
30-11-2011 10:48:24 € 99,00 € 0,00

I would like to have them both but the 30-11 record is not included; How to solve????

 

[jrbarnett]

This occurs because the BETWEEN takes account of the time component of the datetime value. It assumes 0:00 time if you don't put one in, which is why you don't get the 30/11 value.

To get around this update your code to store only the date (with a 0:00 time component) - you can do this by using:
=Date
instead of =Now
in your default values or field designs.

and then use an update query to update the Datum field to Int (Datum) to set the time component to 0 - take a backup first though.

Additionally, you can get a slight (depending on the size of the database) performance boost by moving your condition from HAVING to WHERE clauses - as there are no calculations based on it, this will work.

John

 

[SkipVought]

Your "dates" from your form are simply TEXT.

Enter your dates so that you can capture the year, month number and day. Then do your between using the DateSerial function to generate a Date Value, which is really a NUMBER.

HAVING (Totalen.Datum)>=DateSerial(fYR,fMO,fDA)
   And (Totalen.Datum)<=DateSerial(tYR,tMO,tDA)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Niebotel]

Hi John, it is "complicated" to change the date in the database because these are excisting tables on the PC of several users.
So I do not like to do that (if possible)

I changed the query in
where (((Totalen.Datum)>=[Forms]![Form_menu_finance].[DatumStart] And (Totalen.Datum)<=[Forms]![Form_menu_finance].[DatumEind]))

But that does not help

Can I format the date in the query

 

[Niebotel]

I understand the problem, but not the solution (sorry)
what are the parts in de dateserial: fYR,fMO,fDA
My parameter field on the screen is called StartDatum (only 1 field in which I select a date which the dateChooser
and EindDatum (same definition)

 

[SkipVought]

hellooooooooo....

You have TEXT not DATES!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Niebotel]

Yes I understand; do not want to frustrate.
But I do not understand how to solve;

I Have a filed on a form in which the user selects the startdate for the report. It is unbound text fielsd wich a date selector. (a calendar on which he clicks)
This field is used in teh eparameter query for the report.
I understand now from your explanation that my parameter field is not a date OK?

How can I use now your suggested DateSerial? (I have only 1 field name and not seperated day, month and year)

Do you understand my problem?

 

[SkipVought]

You'll need to parse your field data.

BTW, a freeform field for dates is not a particularly good practice. You really need to VALIDATE each "date" string before submitting the query.

You can use the MID() function to parse.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

WHERE INT(Totalen.Datum) BETWEEN CDate([Forms]![Form_menu_finance].[DatumStart]) And CDate([Forms]![Form_menu_finance].[DatumEind])


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Niebotel]

Yes I think so!
To include the dates?
WHERE INT(Totalen.Datum) >= CDate([Forms]![Form_menu_finance].[DatumStart]) And INT(Totalen.Datum) <= CDate([Forms]![Form_menu_finance].[DatumEind])

 

[PHV]

Did you try my suggested WHERE clause ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Niebotel]

Not yet, but I will do it now and come back in some minutes

 

[Niebotel]

I tried: Terrific!! I search for it 5 hours!!

Why are the date sincluded (just for my understanding)

Very very thank you