JohnJohnJohn
Posts: 10
|
| Posted: 03/13/2014, 11:08 PM |
|
I have a database with separate date and time fields. The following works just fine in Access as a query,
WHERE CDate(SubmittedDate + SubmittedTime) BETWEEN #3/2/2014 8:00:00 PM# AND #3/3/2014 11:00:00 PM#
but when I try to set it up in the CCS Visual Query Builder I cannot get it to work. I have tried this,
WHERE (CDate(SubmittedDate + SubmittedTime) BETWEEN #{s_Start_Date s_Start_Time}# AND #{s_End_Date s_End_Time}#)
and this,
WHERE (CDate(SubmittedDate + SubmittedTime) BETWEEN #{s_Start_Date} {s_Start_Time}# AND #{s_End_Date} {s_End_Time}#)
but neither works.
Any ideas how to get this working?
|
 |
 |
mor
Posts: 119
|
| Posted: 03/14/2014, 2:40 AM |
|
just define date formats and all will works. For example: for data format "3/2/2014 8:00:00"
WHERE
Format(SubmittedDate + ' ' + SubmittedTime, 'dd/mm/yyyy hh:mm:ss')
BETWEEN
Format({s_Start_Date} + ' ' + {s_Start_Time}, 'dd/mm/yyyy hh:mm:ss')
AND
Format({s_End_Date} + ' ' + {s_End_Time}, 'dd/mm/yyyy hh:mm:ss')
_________________
Mor ve Ötesi |
|
|
|
mor
Posts: 119
|
| Posted: 03/14/2014, 3:27 AM |
|
John,
maybe be better two simple where conditions? :)
WHERE
( CDate(SubmittedDate+SubmittedTime) > #{s_Start_Date s_Start_Time}# )
AND
( CDate(SubmittedDate+SubmittedTime) < #{s_End_Date s_End_Time}# )
_________________
Mor ve Ötesi |
|
|
|
JohnJohnJohn
Posts: 10
|
| Posted: 03/14/2014, 3:41 AM |
|
Thank you so much for helping!!
But I tried your code and just got errors...
Form: Grid PartTimeStaffTimeSheetHol1
Error: [Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression '(( CDate(SubmittedDate+SubmittedTime) >= #s_Start_Date s_Start_Time}# )) and (( CDate(SubmittedDate+SubmittedTime) < #s_End_Date s_End_Time}# ))'. (Microsoft OLE DB Provider for ODBC Drivers)
Error: [Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression '(( CDate(SubmittedDate+SubmittedTime) >= #s_Start_Date s_Start_Time}# )) and (( CDate(SubmittedDate+SubmittedTime) < #s_End_Date s_End_Time}# ))'. (Microsoft OLE DB Provider for ODBC Drivers)
|
|
|
|
JohnJohnJohn
Posts: 10
|
| Posted: 03/14/2014, 3:45 AM |
|
My current code is shown below and it works for dates but not for times! I want to search a range from start date/time to end date/time. But my code grabs events from each date that are also within the time constraints. It should get continuous event5s from start date/time to end date/time.
SELECT PartTimeStaffTimeSheetHolder.ID AS PartTimeStaffTimeSheetHolder_ID, SubmittedDate, SubmittedTime, FirstName, LastName
FROM Staff INNER JOIN PartTimeStaffTimeSheetHolder ON
Staff.ID = PartTimeStaffTimeSheetHolder.StaffID
WHERE PartTimeStaffTimeSheetHolder.SubmittedDate >= #{s_Start_Date}#
AND PartTimeStaffTimeSheetHolder.SubmittedDate <= #{s_End_Date}#
AND PartTimeStaffTimeSheetHolder.SubmittedTime >= #{s_Start_Time}#
AND PartTimeStaffTimeSheetHolder.SubmittedTime <= #{s_End_Time}#
AND Staff.FirstName LIKE '%{s_FirstName}%'
AND Staff.LastName LIKE '%{s_LastName}%'
AND PartTimeStaffTimeSheetHolder.ID LIKE '%{s_ID}%'
ORDER BY SubmittedDate desc
|
|
|
|
JohnJohnJohn
Posts: 10
|
| Posted: 03/14/2014, 3:19 PM |
|
any other ideas?
|
|
|
|
eratech
Posts: 513
|
| Posted: 03/16/2014, 10:42 PM |
|
Are the date/time parameters set as 'Date' types in the parameter setup? I've had dates sometimes and sometimes not want the delimiters (# or single quote ') around them.
I've done dates without any quotes, and CCS adds them, and some other times, it wants the quotes (or perhaps # in Access case) added. I've done MySQL and MS SQL Server so I forget which wanted them more, but it's something to try.
Eric
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia |
|
|
|
|
