retrieve access records that fall between user specified dates

sql ms-access

39 просмотра

2 ответа

1 Репутация автора

The table has a date field that is text. The SQL statement is:

"SELECT datefield, anotherfield FROM tablename WHERE CDate(datefield) BETWEEN #" & dateStart & "# AND #" & dateEnd & "#"

dateStart and dateEnd are strings, like "10/02/2017" and "10/4/2017". I used CDate to convert the string datefield to a date, and the bracketing # around the start and end date strings so that they will be treated as date. I have tried, literally, dozens of different variants of the WHERE clause with no luck. Any suggestions are appreciated.

Автор: user3713008 Источник Размещён: 08.11.2017 11:43

Ответы (2)

0 плюса

824 Репутация автора

I certainly agree that dates should not be stored as text. However, if you are stuck with the table design then you will need to use CDate for all three of your "date" fields:

SELECT CDate([datefield]) AS myDate, anotherfield
FROM Table2
WHERE (((CDate([datefield])) Between CDate([dateStart]) And CDate([dateEnd])));
Автор: tlemaster Размещён: 09.11.2017 12:04

-1 плюса

1 Репутация автора

I've also used your ways in storing and retrieving date in mySQL. However, I only used one field instead of your perspective dateStart and dateEnd. I would suggest you only create one field for storing date. Here's how I managed to catch the values between those dates using VB.NET.

SELECT datefield, anotherfield FROM tablename WHERE datestoredfield BETWEEN '" & selectedDateFrom.toString("MM/dd/yyyy") & "' AND '" & selectedDateTo.toString("MM/dd/yyyy") & "';

I've indicated .toString("MM/dd/yyyy") at the end of the selected dates its because your current stored date format in your date field is MM/dd/yyyy.

Автор: Kirk Lavapiez Размещён: 09.11.2017 12:37
Вопросы из категории :