Run-time Error 3085

[villy]

I have this error when I am trying to run the Access SQL query from Excel.
The error is Run-time Error 3085: Undefined Function "propervalue" in Expression.
Can anyone explain to me this?How to solve it.
Thanks

 

[bensonsearch]

Hi,
do you have sampe code?

 

[villy]

Here it is..
Code was modified from one post credit to the owner.

Sub RawLotInput()
Dim dbs As Database
Dim rs As Recordset
Dim Ws As Worksheet
Dim wb As Workbook
Dim Path As String
Dim strSQL As String
On Error GoTo ErrorHandler
ThisWorkbook.Activate
'' - - - - - - - - - - - - - - - - - - - - - - - - - - - -
''Open the database
Path = "P:\03_Construction\4.0 CONSTRUCTION AUTOMATION\4.1 Applications\QMD - PORT\QMD_prog_1.9.12.mdb"
Set dbs = workspaces(0).OpenDatabase(Path)

' SQL statement- Change Query parameters here
strSQL = "SELECT forRepStep2.ID AS [System Number], forRepStep2.CP AS Contract,forRepStep2.GivenID AS [QAN Number]," & _
        "forRepStep2.MStatusID, forRepStep2.MonitoringType,IIf(forRepStep2.MonitoringType=""Surveillance"",""Not applicable"",forRepStep2.IRFNumBound)" & _
        "AS [IRF Number], forRepStep2.DateTimeAct AS [Date Raised], forRepStep2.DateTimePlan AS" & _
        "[Planned Closure Date], forRepStep2.ClosedDateTime, [Sched_ITP.ShortNum] & "" : """ & _
        "& [Sched_ITP.Object] AS [ITP Number], forRepStep2.Desc AS Description, forRepStep2.StatusDesc" & _
        " AS [Non Conformance Details], IRF.Loc AS Location, IRF.ClosingComment AS [Actione Taken]," & _
        "forRepStep2.IsClosed AS Closed, ContractInfo.ContractDescription, ContractInfo.Contractor," & _
        "ContractInfo.ResidentEngr, forRepStep2.StatusID2, DateDiff(""d"",forRepStep2.DateTimeAct,Date())" & _
        "AS DaysOpen, forRepStep2.StatusID1, forRepStep2.StatusID3, IRF.EnteredBy, IRF.ByWhomID, IRF.ClosedBy " & _
        "FROM ((forRepStep2 INNER JOIN IRF ON forRepStep2.ID = IRF.ID) INNER JOIN ContractInfo ON " & _
        "forRepStep2.CP = ContractInfo.ContractPackage) INNER JOIN Sched_ITP ON forRepStep2.ITPNumShort" & _
        "= Sched_ITP.ITPNum GROUP BY forRepStep2.ID, forRepStep2.CP, forRepStep2.GivenID," & _
        "forRepStep2.MStatusID, forRepStep2.MonitoringType, IIf(forRepStep2.MonitoringType=""Surveillance""," & _
        """Not applicable"",forRepStep2.IRFNumBound), forRepStep2.DateTimeAct, forRepStep2.DateTimePlan," & _
        "forRepStep2.ClosedDateTime, [Sched_ITP.ShortNum] & "" : "" & [Sched_ITP.Object], forRepStep2.Desc," & _
        "forRepStep2.StatusDesc, IRF.Loc, IRF.ClosingComment, forRepStep2.IsClosed, ContractInfo.ContractDescription," & _
        "ContractInfo.Contractor, ContractInfo.ResidentEngr, forRepStep2.StatusID2," & _
        "DateDiff(""d"",forRepStep2.DateTimeAct,Date()), forRepStep2.StatusID1, forRepStep2.StatusID3," & _
        "IRF.EnteredBy, IRF.ByWhomID, IRF.ClosedBy HAVING (((forRepStep2.StatusID1) = 2)) ORDER BY forRepStep2.CP," & _
        "forRepStep2.MonitoringType;"
On Error Resume Next
Set rs = dbs.OpenRecordset(strSQL)
Set wb = ThisWorkbook
wb.Worksheets("Raw Data").Delete
'dont use set ws = activesheet as when you have a few
'copies of excel open at the same time weird things can happen
Set Ws = wb.Worksheets.Add
Ws.Name = "Raw Data"
'Clear cells first
'Cells.Select
'Selection.ClearContents
'you dont need this if you delete the sheet
'I would recommend deleting the sheet rather than clearing cells
'removed copyfromrecordset from your loop
For i = 0 To rs.Fields.Count - 1
Ws.Cells(1, i + 1) = rs.Fields(i).Name
Next
'Export data from the recordset to a worksheet (Sheet1).
Ws.Range("A2").CopyFromRecordset rs

'Auto-fit columns
'Select statements are usually unnecessary and where
'you have select followed selection you can combine this
Ws.Range("A1").CurrentRegion.Columns.AutoFit

'' - - - - - - - - - - - - - - - - - - - - - - - - - - - -
lbTidy:
dbs.Close
Set dbs = Nothing
Set rs = Nothing
 Exit Sub
'' - - - - - - - - - - - - - - - - - - - - - - - - - - - -
ErrorHandler:
vtMessage = "Table and data creation error"
vtMessage = vtMessage & _
Chr(10) & _
Chr(10) & "Error Number: " & Err & _
Chr(10) & "Error Description: " & Error()
MsgBox vtMessage ', strSQL, vbInformation ', ctByg
Resume lbTidy
End Sub

 

[villy]

Anyone with idea about MS Access into Excel?
I am calling the attention of all gurus here.
Thanks in advance

 

[RoryA]

Are forRepStep2, IRF, ContractInfo and Sched_ITP all tables, or are any of them queries?

 

[villy]

Thanks for responding rory

all tables except for one query forRepStep2

 

[RoryA]

Does forRepStep2 involve a function called propervalue? If so, then your code won't work.

 

[villy]

No idea how will I know?

 

[RoryA]

You'll have to look at the SQL for that query in your database.

 

[villy]

here is the SQL of forRepStep2:

SELECT 1 AS Dummy, forRepStep1.ID, forRepStep1.IRFNumP, forRepStep1.IRFNumB, IIf([MstatusID]<4,[IRFNumB] & "-" & [IRFNumW] & "-" & [IRFNumN] & " R" & [rev],"") AS IRFNumSimple, forRepStep1.IRFNumBound, forRepStep1.IRFNumber, forRepStep1.Rev, forRepStep1.BuildingID, forRepStep1.DateDoc, forRepStep1.DateTimeAct, forRepStep1.Memo, forRepStep1.Desc, forRepStep1.RejectReasonID, forRepStep1.StatusDesc, forRepStep1.StatusAction, forRepStep1.CP, forRepStep1.DspCode, forRepStep1.MStatusID, Sched_Status0.MonitoringType, forRepStep1.StatusID1, forRepStep1.StatusID2, forRepStep1.StatusID3, forRepStep1.ByWhomID, forRepStep1.NCRNum, forRepStep1.IsClosed, forRepStep1.GivenID, forRepStep1.ClosedDateTime, forRepStep1.Status4, forRepStep1.PrjW, forRepStep1.Object, forRepStep1.ITPNumShort, forRepStep1.OBIName, forRepStep1.FacNameS, forRepStep1.ClosingComment, forRepStep1.TrackingComment0, forRepStep1.TrackingComment1, Sched_Status.Msg, Sched_Status.IsError, forRepStep1.DiscipDisp, IIf(IsNull([Rcode]),"Unknown",[Rcode] & ": " & [Reason]) AS RjR, forRepStep1.IRFNumW, forRepStep1.IRFNumN AS Expr1, forRepStep1.ClosedDateTimeSys, Sched_Status.NOCReq, Sched_Status.Cap3, forRepStep1.ItemDesc, forRepStep1.DateTimePlan, forRepStep1.NDIAName, IIf(propertext([ByWhomID])="","Contractor","KPIZ") AS Own, forRepStep1.ClosedPrjWeek, forRepStep1.ITPAct, forRepStep1.ContractorNumber
FROM ((forRepStep1 LEFT JOIN Sched_Status ON forRepStep1.Status4 = Sched_Status.Status4) LEFT JOIN Sched_Status0 ON forRepStep1.MStatusID = Sched_Status0.Status1) LEFT JOIN RejectReason ON forRepStep1.RejectReasonID = RejectReason.RCode;