Access sActualFieldValue
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
ms-access access-vba
asked Nov 16 '18 at 15:12
Tom HTom H
32
add a comment |
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
ms-access access-vba
asked Nov 16 '18 at 15:12
Tom HTom H
32
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
Nov 16 '18 at 15:24
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 von Asmuth
Nov 16 '18 at 15:41
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
Nov 16 '18 at 20:12
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
Nov 17 '18 at 19:30
add a comment |
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
ms-access access-vba
asked Nov 16 '18 at 15:12
Tom HTom H
32
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
ms-access access-vba
ms-access access-vba
asked Nov 16 '18 at 15:12
Tom HTom H
32
asked Nov 16 '18 at 15:12
Tom HTom H
32
edited Nov 17 '18 at 19:41
Tom H
asked Nov 16 '18 at 15:12
Tom HTom H
32
asked Nov 16 '18 at 15:12
Tom HTom H
32
asked Nov 16 '18 at 15:12
Tom HTom H
32
32
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
Nov 16 '18 at 15:24
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 von Asmuth
Nov 16 '18 at 15:41
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
Nov 16 '18 at 20:12
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
Nov 17 '18 at 19:30
add a comment |
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
Nov 16 '18 at 15:24
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 von Asmuth
Nov 16 '18 at 15:41
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
Nov 16 '18 at 20:12
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
Nov 17 '18 at 19:30
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
Nov 16 '18 at 15:24
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
Nov 16 '18 at 15:24
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 von Asmuth
Nov 16 '18 at 15:41
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 von Asmuth
Nov 16 '18 at 15:41
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
Nov 16 '18 at 20:12
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
Nov 16 '18 at 20:12
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
Nov 17 '18 at 19:30
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
Nov 17 '18 at 19:30
add a comment |
1 Answer
1
active
oldest
votes
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 & "'"))
answered Nov 16 '18 at 16:59
GustavGustav
29.7k51835
Hey Gustav, thank you very much. I will give ot a shot. I appreciate the help.
– Tom H
Nov 17 '18 at 18:57
Guatav, that fixed the issue but caused an error to the line beow it. Included Nz to it and it works. Thank you again for your help.
– Tom H
Nov 19 '18 at 16:58
Great! Then please mark as answered.
– Gustav
Nov 19 '18 at 17:13
Done, sorry for the delay. I'm not familiar with the site yet.
– Tom H
Nov 21 '18 at 4:04
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53340529%2faccess-sactualfieldvalue%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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 & "'"))
answered Nov 16 '18 at 16:59
GustavGustav
29.7k51835
Hey Gustav, thank you very much. I will give ot a shot. I appreciate the help.
– Tom H
Nov 17 '18 at 18:57
Guatav, that fixed the issue but caused an error to the line beow it. Included Nz to it and it works. Thank you again for your help.
– Tom H
Nov 19 '18 at 16:58
Great! Then please mark as answered.
– Gustav
Nov 19 '18 at 17:13
Done, sorry for the delay. I'm not familiar with the site yet.
– Tom H
Nov 21 '18 at 4:04
add a comment |
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 & "'"))
answered Nov 16 '18 at 16:59
GustavGustav
29.7k51835
Hey Gustav, thank you very much. I will give ot a shot. I appreciate the help.
– Tom H
Nov 17 '18 at 18:57
Guatav, that fixed the issue but caused an error to the line beow it. Included Nz to it and it works. Thank you again for your help.
– Tom H
Nov 19 '18 at 16:58
Great! Then please mark as answered.
– Gustav
Nov 19 '18 at 17:13
Done, sorry for the delay. I'm not familiar with the site yet.
– Tom H
Nov 21 '18 at 4:04
add a comment |
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 & "'"))
answered Nov 16 '18 at 16:59
GustavGustav
29.7k51835
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 & "'"))
answered Nov 16 '18 at 16:59
GustavGustav
29.7k51835
answered Nov 16 '18 at 16:59
GustavGustav
29.7k51835
answered Nov 16 '18 at 16:59
GustavGustav
29.7k51835
answered Nov 16 '18 at 16:59
GustavGustav
29.7k51835
29.7k51835
Hey Gustav, thank you very much. I will give ot a shot. I appreciate the help.
– Tom H
Nov 17 '18 at 18:57
Guatav, that fixed the issue but caused an error to the line beow it. Included Nz to it and it works. Thank you again for your help.
– Tom H
Nov 19 '18 at 16:58
Great! Then please mark as answered.
– Gustav
Nov 19 '18 at 17:13
Done, sorry for the delay. I'm not familiar with the site yet.
– Tom H
Nov 21 '18 at 4:04
add a comment |
Hey Gustav, thank you very much. I will give ot a shot. I appreciate the help.
– Tom H
Nov 17 '18 at 18:57
Guatav, that fixed the issue but caused an error to the line beow it. Included Nz to it and it works. Thank you again for your help.
– Tom H
Nov 19 '18 at 16:58
Great! Then please mark as answered.
– Gustav
Nov 19 '18 at 17:13
Done, sorry for the delay. I'm not familiar with the site yet.
– Tom H
Nov 21 '18 at 4:04
Hey Gustav, thank you very much. I will give ot a shot. I appreciate the help.
– Tom H
Nov 17 '18 at 18:57
Hey Gustav, thank you very much. I will give ot a shot. I appreciate the help.
– Tom H
Nov 17 '18 at 18:57
Guatav, that fixed the issue but caused an error to the line beow it. Included Nz to it and it works. Thank you again for your help.
– Tom H
Nov 19 '18 at 16:58
Guatav, that fixed the issue but caused an error to the line beow it. Included Nz to it and it works. Thank you again for your help.
– Tom H
Nov 19 '18 at 16:58
Great! Then please mark as answered.
– Gustav
Nov 19 '18 at 17:13
Great! Then please mark as answered.
– Gustav
Nov 19 '18 at 17:13
Done, sorry for the delay. I'm not familiar with the site yet.
– Tom H
Nov 21 '18 at 4:04
Done, sorry for the delay. I'm not familiar with the site yet.
– Tom H
Nov 21 '18 at 4:04
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53340529%2faccess-sactualfieldvalue%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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
Nov 16 '18 at 15:24
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 von Asmuth
Nov 16 '18 at 15:41
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
Nov 16 '18 at 20:12
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
Nov 17 '18 at 19:30