Type Mismatch (1 Viewer)

[forms_are_nightmares]

Hello All,

I can not figure out why I'm getting a Type Mismatch error with the following code. Any help/insight is appreciated.

If Condition then
Val = ("Cmgr = '" & Me.[CMgr_cmbo] & "'") And ("Aname = '" & Me.AccountCmbo & "'")

Else
Do Something Else
End If

Val is a string
Cmgr, Cmgr_cmbo, Aname, Accountcmbo are all text fields.

Essentially, the form will filter based on the values a user enters.

 

[boblarson]

val is an access function so you should use strVal instead of just Val.

 

[forms_are_nightmares]

Bob,

I did what you suggested but still can't get rid of the type mismatch error.

 

[gemma-the-husky]

and i am unsure about the expression

Val = ("Cmgr = '" & Me.[CMgr_cmbo] & "'") And ("Aname = '" & Me.AccountCmbo & "'")

you MAY be doing a boolean check (I find it hard to decipher the effect of nested punctuation in that way).

val = expression1 and expression2

which may be illegal with strings? I am not sure

 

[boblarson]

use debug.print to make sure that the combos are returning the values you think they should be returning.

 

[forms_are_nightmares]

Thanks for the quick replies:

Bob, I used the debug.print and verified the values are being returned properly.

Gemma/Dave, yes, there is a boolean check that occurs.

here is the full code:

If Not IsNull(Me.CMgr_cmbo) And Not IsNull(Me.AccountCmbo) Then
strVal = "Cmgr = '" & Me.[CMgr_cmbo] & "'" And AName = "'" & Me.AccountCmbo & "'"

 

[gemma-the-husky]

i meant that the expression to generate strval may not be what you want exactly

it depends exactly what you expect the finished string to look like

 

[forms_are_nightmares]

I think I understand your comment.

I split the code and tested each one independently, i.e., Cmgr and then the AName. The code worked. However, when I put the AND between them, I get Type Mismatch. I still don't understand why it would work independently but not together?

 

[boblarson]

I think I understand your comment.

I split the code and tested each one independently, i.e., Cmgr and then the AName. The code worked. However, when I put the AND between them, I get Type Mismatch. I still don't understand why it would work independently but not together?

I do - your statement there just sealed it:

change this:
strVal = "Cmgr = '" & Me.[CMgr_cmbo] & "'" And AName = "'" & Me.AccountCmbo & "'"

to this:

strVal = "Cmgr = '" & Me.[CMgr_cmbo] & "' And AName = "'" & Me.AccountCmbo & "'"

 

[forms_are_nightmares]

Bob,

Thanks. I put that in there, however, it comments out part of the code:

strVal = "Cmgr = '" & Me.[CMgr_cmbo] & "' And AName = "'" & Me.AccountCmbo & "'"

I played around with the code but can't get it to work properly

 

[boblarson]

oops, missed one extraneous dbl quote:

strVal = "Cmgr = '" & Me.[CMgr_cmbo] & "' And AName = '" & Me.AccountCmbo & "'"

 

[forms_are_nightmares]

Hey Bob,

Fixed the code based your change. Now, when I click on the button, nothing happens.

I've built in an else statement so that if the condition isn't satisfied, a form opens up (this is just so i can tell whether the code is working). When I leave one of the boxes blank, the form opens. When they both have values, nothing happens:

Code:
If Not IsNull(Me.CMgr_cmbo) And Not IsNull(Me.AccountCmbo) Then
strval = "Cmgr = '" & Me.[CMgr_cmbo] & "' And AName = '" & Me.AccountCmbo & "'"

I'm lost. I knew forms were nightmares.

 

[boblarson]

Put a breakpoint on your code at the start of the procedure and then do whatever you do to start the code. Then click F8 to step through the code and see what is happening and what the values of various things are as you do. This usually brings to light something that is not as obvious.

 

[boblarson]

oh, and forms are not really nightmares. Once you are used to them it is a breeze. And, we would have solved this almost immediately if we had the database to look at and to play with as well, but we're trying to slowly troubleshoot together and, since we can't see things, we are left to try to figure it out as best we can.

 

[forms_are_nightmares]

Bob,

I noticed something interesting in the code, which is preventing it from executing the second part, or the right part, of the AND statement.

By the way, the code now executes, not sure why it wasn't before.

strval = "Cmgr = '" & Me.[CMgr_cmbo] & "' And AName = '" & Me.AccountCmbo & "'"

The color of the And is not blue; it's black. As though it is regular text and not a function (or whatever you call it). I figured out why it's not executing; but I don't know why it isn't treating the AND properly.

 

[boblarson]

It should be black and it isn't a function.

 

[forms_are_nightmares]

Ah, ok. Sorry. I had a very Sherlock Holmes moment. All for nothing.

Anyway, I did the verification you recommended and everything checks. I moved the second part to the first and it will evaluate the Aname box but the not the Cmgr.

I just can't figure out why it's only "seeing" the first part.

 

[boblarson]

Ah, ok. Sorry. I had a very Sherlock Holmes moment. All for nothing.

Anyway, I did the verification you recommended and everything checks. I moved the second part to the first and it will evaluate the Aname box but the not the Cmgr.

I just can't figure out why it's only "seeing" the first part.

Time to upload a copy so we can take a look???

 

[boblarson]

Is Cmgr really the name of a field in your table or query?

 

[boblarson]

Oh, and it could be that your combo is not null but has an empty string in it, especially if you just highlighted and deleted something. It isn't automatically null except when you first open the form.

So, you might want to change the code to read:

If Len(Me.CMgr_cmbo & "") > 0 And Len(Me.AccountCmbo & "") > 0 Then
strVal = "Cmgr = '" & Me.[CMgr_cmbo] & "' And AName = '" & Me.AccountCmbo & "'"