Brian Bermingham
|
| Posted: 02/22/2002, 1:39 AM |
|
I am using ASP with Templates, Access 2000, and CodeCharge 2.04
I have an interesting problem working with dates.
I have a page with a search form, which passes a date to a grid form. The grid form uses some SQL in the Open event to extract the data. The SQL is constructed using the extracted Day, Month, and Year from the passed date.
tmpSQL = "SELECT Absence.ID, Absence.HR_Number, Employee.First_Name, Employee.Last_Name, Team_Leader_Name_Query.Name AS Team_Leader, Manager_Name_Query.Name AS Manager, Groups.Group_Name, Absence.Year, Absence.Month_ID, Employee.Manager_ID, Employee.Team_Leader_ID, Employee.Call_Despatcher_ID, Employee.Group_ID,"
tmpSQL = tmpSQL & " Absence.[" & txtDay & "]"
tmpSQL = tmpSQL & " FROM Groups RIGHT JOIN (Manager_Name_Query RIGHT JOIN (Team_Leader_Name_Query RIGHT JOIN (Employee LEFT JOIN Absence ON Employee.HR_Number = Absence.HR_Number) ON Team_Leader_Name_Query.HR_Number = Employee.Team_Leader_ID) ON Manager_Name_Query.HR_Number = Employee.Manager_ID) ON Groups.Group_ID = Employee.Group_ID"
tmpSQL = tmpSQL & " WHERE (((Absence.Year)=" & txtYear & ")"
tmpSQL = tmpSQL & " AND ((Absence.Month_ID)=" & txtMonth & ")"
tmpSQL = tmpSQL & " AND ((Absence.[" & txtDay & "]) Is Not Null)) ORDER BY Employee.Last_Name;"
sSQL = tmpSQL
So far so good, in the Before Show event I then extract the contents of the field in the rs defined by the Day extracted from the date passed from the search form and assign the contents to a field on the grid form. i.e. The rs fields are called 1 through to 31 and I can select any one of them. Again this works.
However the problem is:
When I pass a Day lower than the 13th of the month the day and month are reversed i.e. 02/01/2002 which should be 2nd Jan 2002
Is treated as the US format 1st Feb 2002 but if I pass the date 24/02/2002 it is treated correctly as 24th Feb 2002
I hope somebody can suggest something as I can't understand where the problem lies.
Thanks
|
|
|
 |
Nicole
|
| Posted: 02/22/2002, 4:38 AM |
|
Brian,
CC accepts dates in both mm/dd/yyyy and dd/mm/yyyy formats.
The workaround is to force users to enter dates in proper format e.g. in dd/mm/yyyy and then parse the date string using code like:
d = mid(pdatetime_field, 1, 2)
m = mid(pdatetime_field, 4, 2)
y = mid(pdatetime_field, 7)
You may use JavaScript to detect entered date format.
|
|
|
|
Brian Bermingham
|
| Posted: 02/22/2002, 5:22 AM |
|
Thanks Nichole
That sounds as if it could be the problem, I'll give it a try.
|
|
|
|
|
