I have a query in a MS Access 2010 database that I need to pass a parameter to from an Excel spreadsheet. The results are returned to the spreadsheet. (Not my preferred method, but it is something that is exists and I am making updates to.)
The query has a Date/Time combined field from two separate fields, i.e. StartDateTime: [StartDate]+[StartTime]
I am using the following SQL in a data connection in the spreadsheet:
SELECT selqryDowntimeforShiftReport.`Type of Exception`, selqryDowntimeforShiftReport.`Description of Exception`, selqryDowntimeforShiftReport.Duration, selqryDowntimeforShiftReport.ScheduledDT
FROM selqryDowntimeforShiftReport selqryDowntimeforShiftReport
WHERE (((selqryDowntimeforShiftReport.StartDateTime)>=? And (selqryDowntimeforShiftReport.StartDateTime)<?) AND ((selqryDowntimeforShiftReport.MajProcID)=7));
The user enters the report date a spreadsheet field. I need the query to return results from 7:00 AM on the date they enter to 7:00 AM of the next day.
If I format a couple cells in excel as text and manually enter the date/times, i.e. 1/20/2014 7:00:00 AM and 1/21/2014 7:00:000 AM then everything works fine.
Trying to calculate those dates based on the user entered date of 1/20/2014 in cell A24 by the method =TEXT(A24+7/24,”m/d/yyyy h:mm:ss AM/PM”) for the start date/time and =TEXT(A24+1+7/24,”m/d/yyyy h:mm:ss AM/PM”) for the end date/time yields no results. I have also used the same formulas and with the cells formatted in various date formats and still get no results.
I’m suspecting that there is a data type mismatch somewhere even though I get no error stating so. If it is, then I wonder why the original hard typed date and times formatted as text work.
Any ideas on how I can accomplish what I need?
The query has a Date/Time combined field from two separate fields, i.e. StartDateTime: [StartDate]+[StartTime]
I am using the following SQL in a data connection in the spreadsheet:
SELECT selqryDowntimeforShiftReport.`Type of Exception`, selqryDowntimeforShiftReport.`Description of Exception`, selqryDowntimeforShiftReport.Duration, selqryDowntimeforShiftReport.ScheduledDT
FROM selqryDowntimeforShiftReport selqryDowntimeforShiftReport
WHERE (((selqryDowntimeforShiftReport.StartDateTime)>=? And (selqryDowntimeforShiftReport.StartDateTime)<?) AND ((selqryDowntimeforShiftReport.MajProcID)=7));
The user enters the report date a spreadsheet field. I need the query to return results from 7:00 AM on the date they enter to 7:00 AM of the next day.
If I format a couple cells in excel as text and manually enter the date/times, i.e. 1/20/2014 7:00:00 AM and 1/21/2014 7:00:000 AM then everything works fine.
Trying to calculate those dates based on the user entered date of 1/20/2014 in cell A24 by the method =TEXT(A24+7/24,”m/d/yyyy h:mm:ss AM/PM”) for the start date/time and =TEXT(A24+1+7/24,”m/d/yyyy h:mm:ss AM/PM”) for the end date/time yields no results. I have also used the same formulas and with the cells formatted in various date formats and still get no results.
I’m suspecting that there is a data type mismatch somewhere even though I get no error stating so. If it is, then I wonder why the original hard typed date and times formatted as text work.
Any ideas on how I can accomplish what I need?
