How to find a Date from within a Range using Access?
-
Hi, I have a bit of a problem with a Forms App using Access for the DB. I have a Calendar Control that has date ranges bolded from records in a table in the DB. I have the Calendar Control populated and now the user can select a date and find the event associated to the date. The date may be the "StartDate", "EndDate", or a date within the range of the "StartDate" & "EndDate" in a row. What I need help with is wrighting the SQL to bring back the record ID where the selected date falls between the StartDate & End Date and return the Record ID value. SELECT E_ID FROM Events WHERE "SelectedDate" is Between StartDate AND EndDate OR something like that???
-
Hi, I have a bit of a problem with a Forms App using Access for the DB. I have a Calendar Control that has date ranges bolded from records in a table in the DB. I have the Calendar Control populated and now the user can select a date and find the event associated to the date. The date may be the "StartDate", "EndDate", or a date within the range of the "StartDate" & "EndDate" in a row. What I need help with is wrighting the SQL to bring back the record ID where the selected date falls between the StartDate & End Date and return the Record ID value. SELECT E_ID FROM Events WHERE "SelectedDate" is Between StartDate AND EndDate OR something like that???
Try SELECT E_ID FROM Events WHERE (SelectedDate >= StartDate) AND (SelectedDate <= EndDate) Make sure you replace all the date variables with their string representations for the SQL, of course.
CQ de W5ALT
Walt Fair, Jr., P. E. Comport Computing Specializing in Technical Engineering Software
-
Try SELECT E_ID FROM Events WHERE (SelectedDate >= StartDate) AND (SelectedDate <= EndDate) Make sure you replace all the date variables with their string representations for the SQL, of course.
CQ de W5ALT
Walt Fair, Jr., P. E. Comport Computing Specializing in Technical Engineering Software
Hi Walt, Thanks for the sample code... It works great. But I have another issue that maybe you can help me with. The Value I am passing is a string with the DateTimeValue in it. I have put single quotes around the value and it works when I run the SQL manually... but when I run it in the app it rejectes it because the "Time" is still attached. My issue is how can I convert the string value to a Date ONLY value? I have tried Convert.ToDateTime(Value)... but that still has a Time in it. I tried DateTime.ParseExact(Value,... ,...) but I couldn't find a Date only filter. I am using this in a Forms App in C# on an Access DB, I build my SQL in the App and pass it over. Got any suggestions on how to convert the string value of "DateTime" to a "Date" only value? Thanks
-
Hi Walt, Thanks for the sample code... It works great. But I have another issue that maybe you can help me with. The Value I am passing is a string with the DateTimeValue in it. I have put single quotes around the value and it works when I run the SQL manually... but when I run it in the app it rejectes it because the "Time" is still attached. My issue is how can I convert the string value to a Date ONLY value? I have tried Convert.ToDateTime(Value)... but that still has a Time in it. I tried DateTime.ParseExact(Value,... ,...) but I couldn't find a Date only filter. I am using this in a Forms App in C# on an Access DB, I build my SQL in the App and pass it over. Got any suggestions on how to convert the string value of "DateTime" to a "Date" only value? Thanks
Have your tried DateTime.ToShortDateString? A good summary of the assorted DateTime stuff is at [^]. I keep that page bookmarked.
CQ de W5ALT
Walt Fair, Jr., P. E. Comport Computing Specializing in Technical Engineering Software
-
Have your tried DateTime.ToShortDateString? A good summary of the assorted DateTime stuff is at [^]. I keep that page bookmarked.
CQ de W5ALT
Walt Fair, Jr., P. E. Comport Computing Specializing in Technical Engineering Software
Walt, Thanks for the pointer the page dose have a lot of great formats etc on it. I have added it to my favorites. I had to change the Single Quotes to # around each of the parameter dates and it worked great. Thanks for all the help and pointers. SquireDude
-
Have your tried DateTime.ToShortDateString? A good summary of the assorted DateTime stuff is at [^]. I keep that page bookmarked.
CQ de W5ALT
Walt Fair, Jr., P. E. Comport Computing Specializing in Technical Engineering Software
Walt Fair, Jr. wrote:
DateTime.ToShortDateString
Not ideal, as it depends on regional settings (and could contain all kinds of nonsense), whereas the database content does not. An explicit format would be the preferred one here. Here is an alternative summary[^]. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
Prolific encyclopedia fixture proof-reader browser patron addict?
We all depend on the beast below.
-
Walt Fair, Jr. wrote:
DateTime.ToShortDateString
Not ideal, as it depends on regional settings (and could contain all kinds of nonsense), whereas the database content does not. An explicit format would be the preferred one here. Here is an alternative summary[^]. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
Prolific encyclopedia fixture proof-reader browser patron addict?
We all depend on the beast below.
True! Thanks for pointing that out, Luc.
CQ de W5ALT
Walt Fair, Jr., P. E. Comport Computing Specializing in Technical Engineering Software
-
True! Thanks for pointing that out, Luc.
CQ de W5ALT
Walt Fair, Jr., P. E. Comport Computing Specializing in Technical Engineering Software
No problem. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
Prolific encyclopedia fixture proof-reader browser patron addict?
We all depend on the beast below.