Between Dates
Annette Massie
field. I am attempting to run a query selecting data from the entry-date
field.
The Criteria on the query for entry-date is as follows: Is Not Null And
Between [Forms]![frmDateSelection]![BeginningDate] and
[Forms]![frmDateSelection]![EndingDate].
The form, frmDateSelection prompts the user to enter a beginning and ending
date. I have two entries in the table with the date of 07/17/1998.
However, when I run the query with beginning date of 07/17/1998 and ending
date of 07/17/1998, I receive no entries. I have even tried running the
query using a critieria on the field entry-date of Between[Enter beginning
date:] and [Enter ending date:] and again with criteria of >=[Enter
beginning date:] and <=[Enter ending date:].
I thought 'between' meant inclusive. What am I missing?
John Viescas
Is it possible there's a time component in the field you're testing? If so,
Between won't work. You need to say:
Is Not Null AND >= [Forms]![frmDateSelection]![BeginningDate]
AND < [Forms]![frmDateSelection]![EndingDate] + 1
--
John Viescas
author, "Running Microsoft Access 97"
http://www.amazon.com/exec/obidos/ISBN=1572313234/
Annette Massie wrote in message
<#BiQbE9s...@uppssnewspub05.moswest.msn.net>...
Lilla
I am using Access97 and am using dates with time components. Thank you for
pointing out the problem with using BETWEEN. I was unaware of it until I
read your post. Anyway I am trying to switch from BETWEEN to the method you
suggest, but am having mixed results as I will explain.
Interestingly, in my code behind forms where I am building sql strings and
executing them in code as temp querydefs, I have no problem implementing
your suggestion. And those queries are working fine - least wise no error
message appears.
But, in my stored queries, when I try to enter the syntax I get an error
message saying "Expression is incorrectly typed or is too complex to
evaluate." It's the +1 that is causing the problem. It happens even if I use
only the part of the syntax with the +1 in it as the only criteria. I can
fix the problem by using the CDate() function before adding the +1 as shown
below.
In my case txtEndingDate comes in as a date field (no time component)
formatted on the form as
=Format$([cboReportPeriodDesc].[Column](2),"mm/dd/yyyy")
This doesn't work:
< [Forms]![frmReportCriteria]![txtEndingDate] + 1
< (([Forms]![frmReportCriteria]![txtEndingDate]) + 1)
This works:
< #1/1/98#+1
< CDate([Forms]![frmReportCriteria]![txtEndingDate]) + 1
Is there a way to avoid having to use CDate() in my criteria? Or, if I must
use a function, is this the best for this purpose?
Thanks in advance for any suggestions you might have for me.
Lilla
John Viescas wrote in message ...
John Viescas
As you have learned, you have to "force" the data type to date/time. The
data in your control is a String because of your use of the Format$
function. CDate works. Explicitly declaring the form control reference as
a date/time in Query/Parameters might also work. Wrapping "#" around it
should also do the trick.
Have fun!
--
John Viescas
author, "Running Microsoft Access 97"
http://www.amazon.com/exec/obidos/ISBN=1572313234/
Lilla wrote in message <#MgMBECt...@uppssnewspub05.moswest.msn.net>...
Lilla
If there is a time component in a date field then you will have problems if
you use a simple BETWEEN <begin date> And <end date>. I learned this today
by reading a post written by John Viescas. Thank you John. After reading
John's post I did some research and then wrote a little summary for myself
of methods that work. I decided to post my list here so it can be of help to
others.
' 1) Is Not Null AND >= Forms!frmDateSelection!BegDate AND
' < CDate(Forms!frmDateSelection!txtEndDate)+1
' Note: CDate() is required if value is coming from a form (like
above), but is not required
' if the value is a parameter defined as a date type
parameter as in next example.
' 2) Is Not Null AND >= [Enter Beg Date] AND < [Enter End Date]+1
' where the two date parameters are defined as date type
parameters
' This method was suggested by John Viescas, Access author, via
newsgroup.
' 3) Is Not Null AND Between Forms!frmDateSelection!txtBegDate And
'
Forms!frmDateSelection!txtEndDate+#11:59:59 PM#
' Note: it is not necessary to include #12:00:00 AM# on the
BegDate (my observation)
' This method was suggested by David Pfeiffer via this
newsgroup.
' 4) Is Not Null AND Between Forms!frmDateSelection!txtBegDate And
' Forms!frmDateSelection!txtEndDate
' where txtEndDate is formated as:
' = Format(dtmReportEndDate,"mm/dd/yyyy") & " 11:59:59 PM"
Hope this is helpful to someone,
li...@gte.net
Lilla
value that was coming from a form.
Belkiri
>I have a table with a field called entry-date. Entry date is a date/time
>field. I am attempting to run a query selecting data from the entry-date
>field.
>
>The Criteria on the query for entry-date is as follows: Is Not Null And
>Between [Forms]![frmDateSelection]![BeginningDate] and
>[Forms]![frmDateSelection]![EndingDate].
>
>The form, frmDateSelection prompts the user to enter a beginning and ending
>date. I have two entries in the table with the date of 07/17/1998.
>However, when I run the query with beginning date of 07/17/1998 and ending
>date of 07/17/1998, I receive no entries. I have even tried running the
>query using a critieria on the field entry-date of Between[Enter beginning
>date:] and [Enter ending date:] and again with criteria of >=[Enter
>beginning date:] and <=[Enter ending date:].
>
>I thought 'between' meant inclusive. What am I missing?
This is a common "gotcha". When you are working with a date/time field in MS
Access, if you don't specify a time, Access "assumes" you mean 12:00 midnight
for the date entered. So, in your example, what Access thinks you are asking
for is : Between 07/17/1998 12:00am and 07/17/1998 12:00 am. The likelihood of
having any entries that took place EXACTLY at 12:00am are remote. If you are
sure you will never have an entry at 12:00 am, you can use the following:
Between [BeginningDate] and DateAdd("d",1,[EndingDate])
If there is a possibility that a record will have a 12:00 am time, then to
avoid having the next days 12:00 am records from being included (because the
'Between' clause IS inclusive, as you surmized), you should use the following:
>=[BeginningDate] and <DateAdd("d",1,[EndingDate])
I hope this helps.
Jeremy Williams
Head Problem Solver
ProbSolve
John Viescas
Your answer is correct. Note that since dates are an integer count of the
days since December 30, 1899, you can avoid the function call overhead by
saying:
.. AND < [EndingDate]+1
--
John Viescas
author, "Running Microsoft Access 97"
http://www.amazon.com/exec/obidos/ISBN=1572313234/
Belkiri wrote in message
<199807211646...@ladder01.news.aol.com>...