Access sActualFieldValue

Question

I am working on a database that was created years ago by a colleague and updated most recently by a software designer who in his words has "bowed out" of servicing it as our database has become too complex.

I've received the following Run-time error '13": Type Mismatch.

The hi-lighted error is:

Me.Controls("Label" & i & "Desc").Caption = DLookup("[Description]", "tbl_Contract_Items", "[Mat_Item_No]='" & sActualFieldValue & "'")

It is a billing database, we bill our customer with item numbers like 1.23, 2.31, 24.02.03 and 21.03.15. Items with the single decimal work fine but it is the items with two decimals that cause the error. ie 24.02.03. The issue is new and was not a problem until recently.

Private Sub Report_Open(Cancel As Integer) Dim db As DAO.Database Dim rs As DAO.Recordset Dim i As Integer Dim fld As Field Dim sActualFieldValue

Set db = CurrentDb()
Set rs = db.OpenRecordset("qxt_tbl_WEC_Qty_Crosstab1")

For i = 1 To rs.Fields.Count - 1 'rs(0) is Date

    If i > 14 Then Exit For

    sActualFieldValue = Replace(rs(i).Name, "_", ".", , 1)

    Me.Controls("Text" & i).ControlSource = rs(i).Name
    Me.Controls("Label" & i).Caption = sActualFieldValue
    Me.Controls("Label" & i & "Desc").Caption = DLookup("[Description]", "tbl_Contract_Items", "[Mat_Item_No]='" & sActualFieldValue & "'")
    Me.Controls("Text" & i & "Tot").ControlSource = "=sum([" & rs(i).Name & "])"
Next

'Stop

End Sub

Hi Tom, I can't really help you with your problem, unfortunately, since I'm not a VBA expert, one thing to note - people who reply here are all volunteers and some might not appreciated "need help ASAP", as your job emergencies are not their problems. Back to the topic, you might want to remove the second dot in your number since it's not needed for anything, right? — oblio
If you want help fast, I suggest to start with tagging your question appropriately. the access tag excerpt says DO NOT USE this tag for Microsoft Access, use [ms-access] instead and this code seems to be VBA code, not vb.net code. — Erik A
Please read Under what circumstances may I add “urgent” or other similar phrases to my question, in order to obtain faster answers? - the summary is that this is not an ideal way to address volunteers, and is probably counterproductive to obtaining answers. Please refrain from adding this to your questions. — halfer
Thank you all for your comments . I apologize if I "ASAP" was rude, it wasn't intended. oblio, yes, the second dot is required. — Tom H

Gustav Gustav · Accepted Answer · 2018-11-16T16:59:47

Property Caption must be a string, but DLookup returns Null for not found. So wrap in Nz():

Me.Controls("Label" & i & "Desc").Caption = Nz(DLookup("[Description]", "tbl_Contract_Items", "[Mat_Item_No]='" & sActualFieldValue & "'"))

Access sActualFieldValue

1 Answers