Dates

[UT227]

I tried that. I got a blank field. Was hoping to get a list of dates.



SELECT tblCalendarDate.CalDate
FROM tblCalendarDate
WHERE (((tblCalendarDate.CalDate) Between [Forms]![frmClassScheduleEnterNames]![DOT] And [Forms]![frmClassScheduleEnterNames]![DOTEnd]));

[Micron]

You might find this (from the master himself) useful
https://bytes.com/topic/access/answe...-between-dates

Your date fields don't happen to include time, do they?

[CJ_London]

WHERE (((tblCalendarDate.CalDate) Between [Forms]![frmClassScheduleEnterNames]![DOT] And [Forms]![frmClassScheduleEnterNames]![DOTEnd]));

you need to understand what date fields are and how they are treated, After 200+ posts I would have thought you would have come across this before now.

Dates are numeric and are only presented (formatted) to look like a date as you know it. The number is a decimal number where the date is a number counting up from 1 for 31/12/1899, time is the decimal part expressed as the number of seconds/number of seconds in a day - so 12 noon is 0.5, a time of 9:45am is 0.406412037 etc. Today (7th March) is 43166, so the value for 9:45 on 7th March 2018 is 43166.406412037

Your form control is returning a text value of say 01/03/2018. So what you are trying to compare is something like

date (i.e.43166) between 01/03/2018 and 31/03/2018

i.e. comparing a number with text - which clearly won't work

To get round this SQL requires the use of the # character to surround the text which tells it that the text between the # is to be treated as a date - it will convert it. So you now have


date between #01/03/2018# and #31/03/2018#

Which brings us to the final point. SQL uses the US date format of mm/dd/yyyy. If you are using this format as your standard then should not be a problem, but for most users, they have a different format e.g. dd/mm/yyyy. This means that a UK date of 01/03/2018 will not be interpreted as 1st March, but as 3rd January. So you need to present the date in the right format and your code becomes


date between "#" & format(01/03/2018, "mm/dd/yyyy") & "# and #" & format(31/03/2018,"mm/dd/yyyy") & "#"

There are a few wrinkles so SQL will interprete an unambiguous date correctly - so 31/03/2018 will be correctly interpreted as 31st March because there are only 12 months, not 31. You can also use a format which is unambiguous such as 01 March 2018 rather than 01/03/2018 - but still need to surround with the # to tell SQL that what is between them is to be treated as a date.

[UT227]

Micron, Thanks. No. I only need the date, not the time.
Ajax, Yes I ask a lot of questions because I keep coming up with new problems. I like to think of this as learning from experience programers like yourself. I don't know many personally that I can go to ask questions. I have really enjoyed learning. I take the codes and try to learn how the code works instead of just sticking it into the db. No, this is the first time I've had this problem with dates. Hopefully, I will learn how it works. I'm going to look closely at your post and try to understand it.

[ssanfu]

In addition to Ajax's explanation, Allen Browne has a function to convert non USA date format to USA date format.

http://www.allenbrowne.com/ser-30.html

Look for the header "Dates in Strings"