I have a problem with a db using SQL Server linked tables.
Bit of background: It was originally designed in 2007 and is on a corporate network using corporate PC's. Then when we upgraded to 2010, the db was upgraded also.
Anyway, I design on my work desktop PC, and all works well. It also installs on other company desktop PC's flawlessly with no problems.
However, the problems start when trying to install on the company laptops that some people have.
The first problem I am encountering is when running an SQL statement in VBA with dates. The error message is as follows:
"conversion failed when converting datetime from character string"
The query is this:
Any ideas why this would work fine on one computer and not the other? I'm in the UK so am thinking along time formats UK/US. The PC's and laptops are both set the same in regard to locale though and use UK.
As it is only the laptops that have the problem, I am thinking it must be a setting or driver somewhere that is causing the problem as the code works fine on the desktop PC's.
Any pointers, ideas etc would be MUCH appreciated. Thanks =)
Bit of background: It was originally designed in 2007 and is on a corporate network using corporate PC's. Then when we upgraded to 2010, the db was upgraded also.
Anyway, I design on my work desktop PC, and all works well. It also installs on other company desktop PC's flawlessly with no problems.
However, the problems start when trying to install on the company laptops that some people have.
The first problem I am encountering is when running an SQL statement in VBA with dates. The error message is as follows:
"conversion failed when converting datetime from character string"
The query is this:
Code:
Function KPICurrentMonth()
'This function populates the KPI Statistics as they stand this current month
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sql As String
Dim dtmFirstDay As Date
Dim dtmLastDay As Date
'OK, first get the first and last day of this month
dtmFirstDay = Format(DateSerial(Year(Date), Month(Date), 1), "mm/dd/yyyy")
dtmLastDay = Format(DateSerial(Year(Date), Month(Date) + 1, 0), "mm/dd/yyyy")
Set db = CurrentDb()
sql = "SELECT * FROM tblKPICurrent"
Set rs = db.OpenRecordset(sql, dbOpenDynaset)
With rs
.MoveFirst
.Edit
!twor = DCount("*", "dbo_Work_Order", "[Work_Order_Raised] BETWEEN #" & dtmFirstDay & "# AND #" & dtmLastDay & "#+1")
!twor_ma = DAvg("lngWorkOrdersRaisedThisMonth", "qry_STAT_Last12Values")
!twoc = DCount("*", "dbo_Work_Order", "[Work_Order_Actual_Completion] BETWEEN #" & dtmFirstDay & "# AND #" & dtmLastDay & "#")
!twoc_ma = 0
!twob = DCount("*", "dbo_Work_Order", "[Work_Order_Raised]<DateAdd('d',1,#" & dtmLastDay & "#) AND [Work_Order_Actual_Completion] Is Null AND [Work_Order_Not_Done_ReasonID] Is Null OR [Work_Order_Raised]<DateAdd('d',1,#" & dtmLastDay & "#) AND [Work_Order_Actual_Completion]> #" & dtmLastDay & "# And [Work_Order_Not_Done_ReasonID] Is Null")
!twob_ma = DAvg("lngTotalOutstandingWorkOrders", "qry_STAT_Last12Values")
!pr = DCount("*", "dbo_Work_Order", "[Work_Order_Raised] Between #" & dtmFirstDay & "# AND #" & dtmLastDay & "#+1 AND [Work_Order_OriginID]=2")
!pr_ma = DAvg("lngPMThisMonth", "qry_STAT_Last12Values")
!pco = DCount("*", "dbo_Work_Order", "[Work_Order_Actual_Completion] BETWEEN #" & dtmFirstDay & "# AND #" & dtmLastDay & "# AND [Work_Order_OriginID]=2")
!pco_ma = DAvg("lngCompletedPMThisMonth", "qry_STAT_Last12Values")
!po = DCount("*", "dbo_Work_Order", "[Work_Order_Raised]<DateAdd('d',1,#" & dtmLastDay & "#) AND [Work_Order_Actual_Completion] Is Null AND [Work_Order_OriginID]=2 AND [Work_Order_OriginID]=2 AND [Work_Order_Planned_Start]<DateAdd('d',-10,#" & dtmLastDay & "#) And [Work_Order_Not_Done_ReasonID] Is Null OR [Work_Order_Raised]<DateAdd('d',1,#" & dtmLastDay & "#) AND [Work_Order_Actual_Completion]>#" & dtmLastDay & "# AND [Work_Order_OriginID]=2 AND [Work_Order_Planned_Start]<DateAdd('d',-10,#" & dtmLastDay & "#) And [Work_Order_Not_Done_ReasonID] Is Null")
!po_ma = DAvg("lngOutstandingPMOverdue", "qry_STAT_Last12Values")
!pcu = DCount("*", "dbo_Work_Order", "[Work_Order_Raised]<DateAdd('d',1,#" & dtmLastDay & "#) AND [Work_Order_Actual_Completion] Is Null AND [Work_Order_OriginID]=2 AND [Work_Order_Planned_Start] Between DateAdd('d',-10,#" & dtmLastDay & "#) And DateAdd('d',10,#" & dtmLastDay & "#) OR [Work_Order_Raised]<DateAdd('d',1,#" & dtmLastDay & "#) AND [Work_Order_Actual_Completion]>#" & dtmLastDay & "# AND [Work_Order_OriginID]=2 AND [Work_Order_Planned_Start] Between DateAdd('d',-10,#" & dtmLastDay & "#) And DateAdd('d',10,#" & dtmLastDay & "#) And [Work_Order_Not_Done_ReasonID] Is Null")
!pcu_ma = DAvg("lngOutstandingPMCurrent", "qry_STAT_Last12Values")
!pf = DCount("*", "dbo_Work_Order", "[Work_Order_Raised]<DateAdd('d',1,#" & dtmLastDay & "#) AND [Work_Order_Actual_Completion] Is Null AND [Work_Order_OriginID]=2 AND [Work_Order_Planned_Start]>DateAdd('d',10,#" & dtmLastDay & "#) OR [Work_Order_Raised]<DateAdd('d',1,#" & dtmLastDay & "#) AND [Work_Order_Actual_Completion]>#" & dtmLastDay & "# AND [Work_Order_OriginID]=2 AND [Work_Order_Planned_Start]>DateAdd('d',10,#" & dtmLastDay & "#) And [Work_Order_Not_Done_ReasonID] Is Null")
!pf_ma = DAvg("lngOutstandingPMFuture", "qry_STAT_Last12Values")
!ur = DCount("*", "dbo_Work_Order", "[Work_Order_Raised] Between #" & dtmFirstDay & "# AND #" & dtmLastDay & "#+1 AND [Work_Order_OriginID]=1 OR [Work_Order_Raised] Between #" & dtmFirstDay & "# AND #" & dtmLastDay & "#+1 AND [Work_Order_OriginID]=3")
!ur_ma = DAvg("lngBreakdownThisMonth", "qry_STAT_Last12Values")
!uco = DCount("*", "dbo_Work_Order", "[Work_Order_Actual_Completion] BETWEEN #" & dtmFirstDay & "# AND #" & dtmLastDay & "# AND [Work_Order_OriginID]=1 OR [Work_Order_Actual_Completion] BETWEEN #" & dtmFirstDay & "# AND #" & dtmLastDay & "# AND [Work_Order_OriginID]=3")
!uco_ma = DAvg("lngCompletedBreakdownThisMonth", "qry_STAT_Last12Values")
!uo = DCount("*", "dbo_Work_Order", "[Work_Order_Raised]<DateAdd('d',1,#" & dtmLastDay & "#) AND [Work_Order_Actual_Completion] Is Null AND [Work_Order_OriginID]<>2 And [Work_Order_Not_Done_ReasonID] Is Null OR [Work_Order_Raised]<DateAdd('d',1,#" & dtmLastDay & "#) AND [Work_Order_Actual_Completion]> #" & dtmLastDay & "# AND [Work_Order_OriginID]<>2 And [Work_Order_Not_Done_ReasonID] Is Null")
!uo_ma = DAvg("lngOutstandingBDOverdue", "qry_STAT_Last12Values")
!ucu = DCount("*", "dbo_Work_Order", "[Work_Order_Raised]<DateAdd('d',1,#" & dtmLastDay & "#) AND [Work_Order_Actual_Completion] Is Null AND [Work_Order_OriginID]<>2 AND [Work_Order_Planned_Start] Between DateAdd('d',-10,#" & dtmLastDay & "#) And DateAdd('d',10,#" & dtmLastDay & "#) OR [Work_Order_Raised]<DateAdd('d',1,#" & dtmLastDay & "#) AND [Work_Order_Actual_Completion]>#" & dtmLastDay & "# AND [Work_Order_OriginID]<>2 AND [Work_Order_Planned_Start] Between DateAdd('d',-10,#" & dtmLastDay & "#) And DateAdd('d',10,#" & dtmLastDay & "#) And [Work_Order_Not_Done_ReasonID] Is Null")
!ucu_ma = DAvg("lngOutstandingBDCurrent", "qry_STAT_Last12Values")
!uf = DCount("*", "dbo_Work_Order", "[Work_Order_Raised]<DateAdd('d',1,#" & dtmLastDay & "#) AND [Work_Order_Actual_Completion] Is Null AND [Work_Order_OriginID]<>2 AND [Work_Order_Planned_Start]>DateAdd('d',10,#" & dtmLastDay & "#) OR [Work_Order_Raised]<DateAdd('d',1,#" & dtmLastDay & "#) AND [Work_Order_Actual_Completion]>#" & dtmLastDay & "# AND [Work_Order_OriginID]<>2 AND [Work_Order_Planned_Start]>DateAdd('d',10,#" & dtmLastDay & "#) And [Work_Order_Not_Done_ReasonID] Is Null")
!uf_ma = DAvg("lngOutstandingBDFuture", "qry_STAT_Last12Values")
!locr = DCount("*", "dbo_Work_Order", "[Work_Order_Raised] BETWEEN #" & dtmFirstDay & "# AND #" & dtmLastDay & "#+1 AND [Task_TypeID]=1")
!locr_ma = DAvg("lngBreakdownLOCThisMonth", "qry_STAT_Last12Values")
!locco = DCount("*", "dbo_Work_Order", "[Work_Order_Actual_Completion] BETWEEN #" & dtmFirstDay & "# AND #" & dtmLastDay & "# AND [Task_TypeID]=1")
!locco_ma = DAvg("lngBreakdownLOCCompletedThisMonth", "qry_STAT_Last12Values")
!loco = DCount("*", "dbo_Work_Order", "[Work_Order_Raised]<DateAdd('d',1,#" & dtmLastDay & "#) AND [Work_Order_Actual_Completion] Is Null AND [Task_TypeID]=1 AND [Work_Order_Not_Done_ReasonID] Is Null OR [Work_Order_Raised]<DateAdd('d',1,#" & dtmLastDay & "#) AND [Work_Order_Actual_Completion]> #" & dtmLastDay & "# AND [Task_TypeID]=1 AND [Work_Order_Not_Done_ReasonID] Is Null")
!loco_ma = DAvg("lngBreakdownLOCOutstandingThisMonth", "qry_STAT_Last12Values")
!frr = DCount("*", "dbo_Work_Order", "[Work_Order_Raised] BETWEEN #" & dtmFirstDay & "# AND #" & dtmLastDay & "#+1 AND [Task_TypeID]=4")
!frr_ma = DAvg("lngBreakdownFRThisMonth", "qry_STAT_Last12Values")
!frco = DCount("*", "dbo_Work_Order", "[Work_Order_Actual_Completion] BETWEEN #" & dtmFirstDay & "# AND #" & dtmLastDay & "# AND [Task_TypeID]=4")
!frco_ma = DAvg("lngBreakdownFRCompletedThisMonth", "qry_STAT_Last12Values")
!fro = DCount("*", "dbo_Work_Order", "[Work_Order_Raised]<DateAdd('d',1,#" & dtmLastDay & "#) AND [Work_Order_Actual_Completion] Is Null AND [Task_TypeID]=4 AND [Work_Order_Not_Done_ReasonID] Is Null OR [Work_Order_Raised]<DateAdd('d',1,#" & dtmLastDay & "#) AND [Work_Order_Actual_Completion]> #" & dtmLastDay & "# AND [Task_TypeID]=4 AND [Work_Order_Not_Done_ReasonID] Is Null")
!fro_ma = DAvg("lngBreakdownFROutstandingThisMonth", "qry_STAT_Last12Values")
.Update
.Close
End With
End FunctionAny ideas why this would work fine on one computer and not the other? I'm in the UK so am thinking along time formats UK/US. The PC's and laptops are both set the same in regard to locale though and use UK.
As it is only the laptops that have the problem, I am thinking it must be a setting or driver somewhere that is causing the problem as the code works fine on the desktop PC's.
Any pointers, ideas etc would be MUCH appreciated. Thanks =)
