Including End dates within forms

[Lynn73]

Hi
I have set up a form to prompt for start and end dates so that users can input their own dates which will return a report that is based on a query. However if they enter 01/04/07 to 30/04/07 it doesn't include the last date.
I have tried putting the following into the query
Between [forms]![formname]![cbostartdate] and CDate[forms]![formname]![cboenddate]+#23:59:59# but I keep getting the following message when trying to access the form.

The expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

Can anyone help?
Thanks

 

[ajetrumpet]

I have tried putting the following into the query
Between [forms]![formname]![cbostartdate] and CDate[forms]![formname]![cboenddate]+#23:59:59#

Maybe the "Between" Keyword is literal, meaning that it performs exactly like it sounds...??

Maybe try using the appropriate mathematical operator(s)...

>= [forms]![formname]![cbostartdate] AND 
<= [forms]![formname]![cboenddate]

 

[Lynn73]

Thanks - just tried that but it doesn't seem to include the last date - seems to be taking everything before it

 

[ajetrumpet]

SQL statement??

 

[Kinger43]

the between...and statement should be inclusive (including the start date and end date).
I use this in my databases and it includes the end date in the range. I'm guessing that you initially tried

between forms![formname]![cbostartdate] and forms![formname]![cboenddate]

This is what I use for that application

 

[EMP]

I have tried putting the following into the query
Between [forms]![formname]![cbostartdate] and CDate[forms]![formname]![cboenddate]+#23:59:59#

CDate() is a function and needs the brackets. Try this:

Between [forms]![formname]![cbostartdate] And CDate([forms]![formname]![cboenddate])+#23:59:59#

^

 

[Steve R.]

I personally have not had to solve this problem. However, this seems to be a re-occurring problem generating many posts.

First BETWEEN is equivalent to > SomeVlaue AND < AnotherValue
From the post it appears that you want the logic to be >=StartDate AND <=EndDate.

To assure that you are getting the latest possible date you have the code: +#23:59:59#. I don't know if this is correct or not.

I think to get a usable date value that you would need to use the FORMAT property to get the date value in a form that only uses the DAY, MONTH, and YEAR components of the full date value, or to put it another way, the TIME component of the date value is stripped off. I also have been contemplating the use of CDEC to convert the date value to a decimal number. (Converting to a decimal value at least gets it into a numeric form where you can see what is going on.)

Modifying ajetrumpet code may work in this form:

>= [forms]![formname]!CDEC([cbostartdate]) AND 
<= [forms]![formname]!CDEC([cboenddate])

You may need to look at the decimal string to figure out how to round-down for the Start Date and round-up to midnight for your End Date. If the time component is stripped off the dates, this may not be needed.

I hope you will post back with the solution of this issue so that we may all learn.

 

[boblarson]

Actually, Between will return both dates INCLUSIVE (sorry Ortais, but that is true) meaning that both the beginning AND ending dates will be shown. What is likely happening is that the date is stored in General Date format if this doesn't work:

Between [Forms]![YourFormName]![YourBeginningDate] And [Forms]![YourFormName]![YourBeginningDate]


Then, you would need to include the time in the text box input or append it on. But, don't use the (+) sign as a concatenation device here. Use an ampersand (&).

 

[EMP]

Oringially posted by boblarson
Then, you would need to include the time in the text box input or append it on. But, don't use the (+) sign as a concatenation device here. Use an ampersand (&).

I believe the poster's field does contain time too. That's why the query doesn't include the last date.

Date/time field is internally a number so the + sign is needed. # is the delimiter for delimiting date/time value.

On the other hand, ampersand (&) is for text concatenation. Using & instead of + will change
[forms]![formname]![cboenddate] & #23:59:59#
into a text string. To use & properly, you have to put it like this:-

Between [forms]![formname]![cbostartdate] And
CDate([forms]![formname]![cboenddate] & ' 23:59:59')


Note the need of change of delimiter and of the space before 23:59:59. For this reason, I would prefer the numeric way of using the + sign:-

Between [forms]![formname]![cbostartdate] And CDate([forms]![formname]![cboenddate])+#23:59:59#

which is the poster's original expression but without the brackets.

^

 

[Lynn73]

Finally got it to work using
>=[forms]![formname]![cbostartdate] and <=[forms]![formname]![cboenddate]&" "&#23:59:59#

Thanks everybody for all the help and suggestions

 

[Newbie100]

All roads lead to Rome, but which is the shortest?

Between [forms]![formname]![cbostartdate] And
CDate([forms]![formname]![cboenddate] & ' 23:59:59')

>= [forms]![formname]![cbostartdate] And
<= CDate([forms]![formname]![cboenddate] & ' 23:59:59')

Between [forms]![formname]![cbostartdate] And
CDate([forms]![formname]![cboenddate])+#23:59:59#

>= [forms]![formname]![cbostartdate] And
<= CDate([forms]![formname]![cboenddate])+#23:59:59#

>=[forms]![formname]![cbostartdate] And
<=[forms]![formname]![cboenddate] & " " & #23:59:59#

Between [forms]![formname]![cbostartdate] And
[forms]![formname]![cboenddate] & " " & #23:59:59#

 

[Jon K]

I'd say the one using Between And and without the need of the CDate() function. But given today's computing power, you can hardly notice their differences.
.