Where/Criteria Statement not working

[Adrianna]

I've done this a couple of times and never had a problem before. I've been sick and now I'm rushing to meet my deadline and I can't seem to get his to work.

I'm working with two ctriteria in the DoCmd.OpenReport function

I was using the stLinkCriteria and even tried just using the following line in the WHere statement. Everything complies fine, but i get a Type MisMatch error.

"[ID]=" & ME![ID2Delete]&" AND [Division]=" & "'"& Me![Devision2Delete] &"'"

[ID] and [Division] are fields in me main table
[ID] is an auto number
[Division] is text

[ID2Delete] and [Devision2Delete] are unbound textboxes that the user fills in. Actually [Division2Delete] is now a drop down menu from a query, but the mismatch error occurred before that too.

I've tried all sort of combinations of writing this....both as text....one as text, the other as a number....I thought that it had to do with the stLinkCriteria sorting the information as a String, but that's not it.

Can someone point out what I'm blindly over looking?


Thanks!

 

[Adrianna]

Pat...I tried :

"[ID]=" & ME.ID2Delete &" AND [Division]=" & "'" & Me.Division2Delete & "'"

and yes...it was just a typo....I was very sick yesterday....working from home to meet my deadline and I can't hook up my office laptop to my dialup, so I was just rushing to retype it.

I did replace my ! with . but it didn't change anything. i don't understand why the Type Mismatch error continues to appear.

Funny thing is that I made the same function on an older version of the database (using the same code) and it worked fine.

I can't even get the :
"[Division]=" & "'" & Me.Division2Delete & "'"
part to work by itself, so I'm assuming that it is the source of my problem, but I can't figure out how. I have to demo my work in a few hours

Need I mention that when it worked...it was on my Access 2002(XP) system, but when I do it on my release version, Access 2000 it doesn't work.

Fluke?

 

[Adrianna]

okay...I was misdirected

Okay,
I'm sorry for all of the madness. I'm not crazy...I just found that out. My String for the stLinkCriteria was fine. I keep rewriting it and it works, until I place it into the form that I want to use. So...I got some medicine to clear my head and I'm thinking a little more clearly. The only difference between my test code and in the form i want to use is that I have an If statement checking the unbound text boxes for Null? I don't see why this would cause the type mismatch, but when I remove the If Then Else....all is good....but I really do need to check for Null.

 

[Rich]

Post the rest of the code with the If

 

[Adrianna]

If IsNull([Division2Del] or [ID2Del]) Then
MsgBox "You must fill in the Division and ID fields before you can proceed"

Else

stDocName = "Archive Justification"
stLinkCriteria = .......etc

End If



Could it be because the () around the two IsNull fields makes them boolean?

 

[Adrianna]

Pat...You're amazing

Pat,
It's amazing the things that we can over look...over and over again. I on't know why I didn't think about writing the statement out in two parts....I was looking through my code and that's how I'd handled it else where. I guess the Type Mismatch error was really throwing me off course.
Luckily since I've been so sick I was able to push back my presentation until today! Thank you for the quick response!

Adrianna