Date Range for months with < 31 days not working

[CosmoKramer]

I have a query that returns records from a user entered date range. If I look at the records in the table that meet the criteria for 4/2001 there are 22. The following query only returns 21 if the user enters 04/01/2001 and 04/30/2001 as the beginning and ending dates:

SELECT PersonData.Location, PersonData.Reg_Initials, Format([Trans_Date],"mm/yyyy&quot AS Expr1
FROM PersonData
WHERE (((PersonData.Merged)=Yes) AND ((PersonData.Trans_Date) Between [Enter Beginning Date - mm/dd/yyyy] And [Enter Ending Date - mm/dd/yyyy]))
ORDER BY PersonData.Location, PersonData.Reg_Initials;

If they enter 04/01/2001 and 05/01/2001, they get the 22 records. What's strange is the query works fine with months that have 31 days.

Any ideas??

 

[Cagliostro]

You should use datediff.
where datediff(....) < 31 John Fill

ivfmd@mail.md

 

[MichaelRed]

The actual ANSWER to your question is that Date type data in Ms. Access (and MANY databases) are stored as 'floating point' variables. The integer protion is the number of days since the start of the calendar, and the decimal part ie the 'portion' of the day (e.g. 0.25 ~ 6 hours, .05 ~ 12 hours ...). when you 'compare' date fields to a datevalue, the date field often will have been create using the &quot;NOW function - which includes the time part. For any 'time' during the 'day', this will be > 0, while the date ented via the keyboard has only the date (integer) part with the fraction / decimal part = 0. So any record created on a particular day (e.g. 4/30.01) will be some ingeger AND some fraction. as in:

? Now
5/29/01 2:19:26 PM


which translates to :
? CDbl(Now)
37040.5972222222

So today is 37,040 days from the beginning and 0.597222 * 24

or:

? 0.597222 * 24
14.333328

hours after the start of the day.

or ~~~ 2:30 PM

Now, in conclusion ...

You need to re-think the whole &*^%$^%$#^%$# thing. If you want a month's worth of info, DO NOT LET THE USER ENTER BOTH DATES! ALSO, DO NOT USE THE &quot;DATEADD + 31&quot;!!

To retrieve the CALENDAR mopnth's worth of data, have the user enter SOME date within the month.

Derive the Start of the month:
DateSerial(Year(Now), Month(DateEntered), 1)

Derive the End of the month (actually the start of the following month):
DateSerial(Year(Now), Month(DateEntered) + 1, 1)


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over