By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,376 Members | 3,024 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,376 IT Pros & Developers. It's quick & easy.

Syntax issue - when strlinkcriteria is a number

P: 24
I have this code which works fine and will open the form to the correct record but it produces a "Type mismatch" msgbox on opening the record...

the stLinkCriteria consists of two values one of which is a number [AAID] - is the syntax causing this?

************************************************** ******************************************
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command13_Click()
  2. On Error GoTo Err_Command13_Click
  3.  
  4.     Dim stDocName As String
  5.     stDocName = "frmAAResults"
  6.     stLinkCriteria = "[AAID] =" & Me![lstEMPs].Column(0) & " AND [Department]='" & Me![lstEMPs].Column(1) & "'"
  7.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  8.  
  9.  
  10. DoCmd.Close "frmSEARCH-Results"
  11. Exit_Command13_Click:
  12.     Exit Sub
  13.  
  14. Err_Command13_Click:
  15.     MsgBox Err.Description
  16.     Resume Exit_Command13_Click
  17.  
  18. End Sub
  19.  
************************************************** ******************************************
May 17 '07 #1
Share this Question
Share on Google+
15 Replies


puppydogbuddy
Expert 100+
P: 1,923
Yes, I believe that since AAID is numeric, you have to wrap it with the string (str) function as shown:

stLinkCriteria = Str("[AAID] =" & Me![lstEMPs].Column(0)) & " AND [Department]='" & Me![lstEMPs].Column(1) & "'"
May 17 '07 #2

P: 8
Yes, I believe that since AAID is numeric, you have to wrap it with the string (str) function as shown:

stLinkCriteria = Str("[AAID] =" & Me![lstEMPs].Column(0)) & " AND [Department]='" & Me![lstEMPs].Column(1) & "'"
Yes, but wouldn't the syntax be:

stLinkCriteria = "[AAID] =" & str(Me![lstEMPs].Column(0)) & " AND [Department]='" & Me![lstEMPs].Column(1) & "'"

?
May 17 '07 #3

P: 24
Puppydogbuddy – your code produces the error msgbox “Type mismatch” as soon as you click the button and doesn’t open the form

evn678 – your code does the same thing as my original code – it opens the form to the correct record but still produces the error msgbox…

???
May 17 '07 #4

puppydogbuddy
Expert 100+
P: 1,923
[font=Verdana][size=2]You can try it that way, but I think I probably should have wrapped the entire expression with the CStr function instead of the Str function as shown:[/size][/font]

stLinkCriteria = CStr("[AAID] =" & Me![lstEMPs].Column(0) & " AND [Department]='" & Me![lstEMPs].Column(1) & "'" )
May 17 '07 #5

P: 8
Puppydogbuddy – your code produces the error msgbox “Type mismatch” as soon as you click the button and doesn’t open the form

evn678 – your code does the same thing as my original code – it opens the form to the correct record but still produces the error msgbox…

???
Go into your table definition and tell us the data types of .Column(0) and .Column(1)
May 17 '07 #6

missinglinq
Expert 2.5K+
P: 3,532
I assume that AAID is an identification "number." If this is true, there's no valid reason for it data type to be numeric! You should only use a numeric data type for fields that are going to be used in some sort of mathematical calculations, not for fields that just happen to be made up of all "numbers." Tyr changing the data type and run your original code and see what happens.
May 17 '07 #7

puppydogbuddy
Expert 100+
P: 1,923
Are you referring to the last code I submitted, using the CStr function? That should work.
May 17 '07 #8

P: 24
I think the number is the issue why I can't figure out BUT

AAID is a number because in the table it looks up to it is an auto number
Department is text

AAID + Dept is the composite key of the table we are searching for

The new code still produces the error msgbox...
May 17 '07 #9

puppydogbuddy
Expert 100+
P: 1,923
[color=black]
I think the number is the issue why I can't figure out ...
[/color]

[color=black] [/color]

[color=black]None of the syntaxes used previously were correct for combining text and numeric data types in one expression. Hopefully, we can get you the correct expression syntax.[/color]

[color=black] [/color]

[color=black]Let's try the following expression syntax: I simplified it by removing the Column(0) from the numeric component because column(0) is the bound (default) column in that expression and should not be needed. I also added variables for each component of the expression to be able to determine that is evaluated properly.[/color]

[color=black] [/color]

[color=black]Dim iNumericPart As Integer[/color]

[color=black]Dim strTextPart As String[/color]

[color=black] [/color]

[color=black]iNumericPart = [/color]"[AAID] = ” & Me![lstEMPs]

strTextPart = [color=black] “[/color][Department]= '" & Me![lstEMPs].Column(1) & "'"

[color=black] [/color]

stLinkCriteria = iNumericPart & “ AND “ & strTextPart



If the above doesn’t work, you need to go to the debug window and type in the variable name for each component of the expression by itself and let me know which component does not evaluate properly.
May 17 '07 #10

P: 24
Thank You - I see you dim(ed) an integer AND a string which is what I thought the issue to be all along – so that makes sense – however, go figure - when I trashed the form and made a new one from scratch it works this way:
************************************************** **********************
Private Sub cmdOpenResults_Click()
On Error GoTo Err_cmdOpenResults_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmAAResults"

stLinkCriteria = CStr("[AAID] =" & Me![lstWS].Column(0) & " AND [Department]='" & Me![lstWS].Column(2) & "'")
DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.Close acForm, "frmSearchResults"


Exit_cmdOpenResults_Click:
Exit Sub

Err_cmdOpenResults_Click:
MsgBox Err.Description
Resume Exit_cmdOpenResults_Click

End Sub

I am learning sometimes access has gremlins…

But I wanted to know how to dim 2 separate data type variables and you showed me so thanks very much…
May 18 '07 #11

puppydogbuddy
Expert 100+
P: 1,923
Glad I could help. I am curious about one thing, though.
Your final expression:
stLinkCriteria = CStr("[AAID] =" & Me![lstWS].Column(0) & " AND [Department]='" & Me![lstWS].Column(2) & "'")

is the same as the one I recommended in one of my prior posts (post #5):
stLinkCriteria = CStr("[AAID] =" & Me![lstEMPs].Column(0) & " AND [Department]='" & Me![lstEMPs].Column(1) & "'" )

except that you are referencing a different listbox. Could this have been the problem all along?
May 18 '07 #12

P: 24
I would have to say no, I changed the order several times, but if it had been the wrong column number it would not have been able to locate the correct record (which it always did)…

The column count determines which field to match against the form’s record, so I am thinking if I had the wrong one it would have crashed...correct?
May 18 '07 #13

puppydogbuddy
Expert 100+
P: 1,923
The column count determines which field to match against the form’s record, so I am thinking if I had the wrong one it would have crashed...correct?
Not neccessarily, but there would be a noticeable difference in the return values, i.e. the values returned wouldn't make sense in the context of what would be expected.

However, I am referring to the object name, not the order:
CStr("[AAID] =" & Me![lstWS]
.......................vs......................... ...............
CStr("[AAID] =" & Me![lstEMPs]
May 18 '07 #14

P: 24
Sorry I missed that - yes you are right I did change the listbox name to the current example...so now I am curious, are you saying that the wrong name of the listbox might still have opened the form yet had a mismatch error? I don't think so, I believe it would have debugged and opened to the list box name in the VBE, right?
May 21 '07 #15

puppydogbuddy
Expert 100+
P: 1,923
Sorry I missed that - yes you are right I did change the listbox name to the current example...so now I am curious, are you saying that the wrong name of the listbox might still have opened the form yet had a mismatch error? I don't think so, I believe it would have debugged and opened to the list box name in the VBE, right?
Yes, it would have to be debugged. I was not referring to the listbox name in that discussion about crashing the system. In that context, I was talking about colums within the listbox.
May 21 '07 #16

Post your reply

Sign in to post your reply or Sign up for a free account.