Data Type Mismatch

[atlantis43 via AccessMonster.com]

In a procedure, I've been using the following to determine a Val:
DLookup("[fee]", "tblFeeSched", "[CPTCode] = '" & c "' And [year] ='" & y "'
And [PayType] = 0 ").

[PayType] is Text in underlying table, and this code has worked
fine for many years. Suddenly I'm finding that when code runs,
I'm getting msg of "Data type mismatch in criteria expression"
for this line.
If I change code to read " [PayType] = False " then it works OK.

Can anyone explain why my code wants the 'text' datatype to suddenly
change to Boolean? I made no changes to the data in the underlying
table to prompt such action.

TIA, Richard

 

[dymondjack]

Just out of curiousity, if PayType is a Text field, why are you comparing the
value as a number?

Shouldn't it be "[PayType] = '0'" ?

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[atlantis43 via AccessMonster.com]

Dymondjack:
Remarkably, what you have suggested is, indeed, correct. The odd thing is
that it has been working as a 'number' all along. I don't recall ever
changing the datatype of the value, but I'll have to check some old backup
copies of my back-end Db to see if I did make such a change. Thanks for the
quick solution.
Richard

Just out of curiousity, if PayType is a Text field, why are you comparing the
value as a number?

Shouldn't it be "[PayType] = '0'" ?

In a procedure, I've been using the following to determine a Val:
DLookup("[fee]", "tblFeeSched", "[CPTCode] = '" & c "' And [year] ='" & y "'

[quoted text clipped - 11 lines]

TIA, Richard

 

[dymondjack]

Thanks for the quick solution.

Really, I have no idea if this is the solution or not... I just happened to
see it say to myself 'I wonder why...'

However, I CAN say that I try to never let Access figure anything out for
itself. I *think*, in a case like this, Access may be converting that number
into a string to try and match the field datatype. Better off explicitly
evaluating a string.

It's interesting you say that if you replace it with False (not evaluated as
a string either, unless that was a typo in the post). I know I've compared a
boolean datatype to a string datatype of "True" or "False", and it works
(probably for the same reason that the number 0 works in a string comparison,
Access deciding what you 'actually' meant). But a boolean value, deep down,
is really processed as a number.

I don't have the answer, but the above is cause for speculation, I think.
I've come into situations letting access 'take control' has backfired at a
later date, for whatever reason.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

Dymondjack:
Remarkably, what you have suggested is, indeed, correct. The odd thing is
that it has been working as a 'number' all along. I don't recall ever
changing the datatype of the value, but I'll have to check some old backup
copies of my back-end Db to see if I did make such a change. Thanks for the
quick solution.
Richard

Just out of curiousity, if PayType is a Text field, why are you comparing the
value as a number?

Shouldn't it be "[PayType] = '0'" ?

In a procedure, I've been using the following to determine a Val:
DLookup("[fee]", "tblFeeSched", "[CPTCode] = '" & c "' And [year] ='" & y "'

[quoted text clipped - 11 lines]

TIA, Richard