f



DLookup("[PVIN]", "tblPreliminaryVINs", "[PVIN]=Forms!frmVINODO!SerialNum")

DLookup("[PVIN]", "tblPreliminaryVINs",
"[PVIN]=Forms!frmVINODO!SerialNum")

is giving me a Type Mismatch error. That's confusing to me
and I don't know how to circumvent it. The [PVIN] field in
tblPreliminaryVINs is a 17-char text field. Forms!frmVINODO!SerialNum
is just an unbound textbox on a form (frmVINODO).

I run the DLookup during the textbox's BeforeUpdate event code. Some
VIN values I type in there do NOT give rise to the error. Some do. By
nature, the VIN values are alphanumeric. Should I be wrapping the
Forms!frmVINODO!SerialNum in CStr( ) or something?
0
CRCI (2317)
1/11/2006 7:30:04 PM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

11 Replies
847 Views

Similar Articles

[PageSpeed] 32

"MLH" <CRCI@NorthState.net> wrote in message
news:6qmas11hj2qvolsr6r9ioi7tk3bv9reqr8@4ax.com...
> DLookup("[PVIN]", "tblPreliminaryVINs",
> "[PVIN]=Forms!frmVINODO!SerialNum")
>
> is giving me a Type Mismatch error. That's confusing to me
> and I don't know how to circumvent it. The [PVIN] field in
> tblPreliminaryVINs is a 17-char text field. Forms!frmVINODO!SerialNum
> is just an unbound textbox on a form (frmVINODO).
>
> I run the DLookup during the textbox's BeforeUpdate event code. Some
> VIN values I type in there do NOT give rise to the error. Some do. By
> nature, the VIN values are alphanumeric. Should I be wrapping the
> Forms!frmVINODO!SerialNum in CStr( ) or something?


Not CStr, but you need to wrap it in quotes.

DLookup("[PVIN]", "tblPreliminaryVINs", _
 "[PVIN]=""" & Forms!frmVINODO!SerialNum & """")


-- 
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

0
randy46 (685)
1/11/2006 7:39:40 PM
On Wed, 11 Jan 2006 19:39:40 GMT, "Randy Harris" <randy@SpamFree.com>
wrote:

>Not CStr, but you need to wrap it in quotes.
>
>DLookup("[PVIN]", "tblPreliminaryVINs", _
> "[PVIN]=""" & Forms!frmVINODO!SerialNum & """")

Hmmm? Hadn't thought about the quotes. I got sidelined trying
to determine whether Forms!frmVINODO!SerialNum even has
a readable value in the BeforeUpdate event code. I dunno if
that is contributing to my problem.
0
CRCI (2317)
1/11/2006 9:27:42 PM
>Not CStr, but you need to wrap it in quotes.
>
>DLookup("[PVIN]", "tblPreliminaryVINs", _
> "[PVIN]=""" & Forms!frmVINODO!SerialNum & """")

Further testing proves that I get the error if I enter
JT4RN81A0M0077777 into Forms!frmVINODO!SerialNum
#AND# there is a record in tblPreliminaryVINs with a [PVIN]
field value of JT4RN81A0M0077777. But if there is NOT a
record in tblPreliminaryVINs when with a [PVIN] field value
of JT4RN81A0M0077777the code line runs, no error is
triggered. That stumps me.
0
CRCI (2317)
1/11/2006 10:31:44 PM
>Not CStr, but you need to wrap it in quotes.
>
>DLookup("[PVIN]", "tblPreliminaryVINs", _
> "[PVIN]=""" & Forms!frmVINODO!SerialNum & """")
I cut 'n pasted your code. Am getting the same error
telling me there's type mismatch. Really has me under
a barrel.
0
CRCI (2317)
1/11/2006 10:56:42 PM
"MLH" <CRCI@NorthState.net> wrote in message
news:tuvas1lom0ske8v5nnp2sqv6jds8qbfni3@4ax.com...
> >Not CStr, but you need to wrap it in quotes.
> >
> >DLookup("[PVIN]", "tblPreliminaryVINs", _
> > "[PVIN]=""" & Forms!frmVINODO!SerialNum & """")
>
> Further testing proves that I get the error if I enter
> JT4RN81A0M0077777 into Forms!frmVINODO!SerialNum
> #AND# there is a record in tblPreliminaryVINs with a [PVIN]
> field value of JT4RN81A0M0077777. But if there is NOT a
> record in tblPreliminaryVINs when with a [PVIN] field value
> of JT4RN81A0M0077777the code line runs, no error is
> triggered. That stumps me.


What are you doing with the DLookup returned value?

-- 
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

0
randy46 (685)
1/11/2006 11:17:50 PM
On Wed, 11 Jan 2006 23:17:50 GMT, "Randy Harris" <randy@SpamFree.com>
wrote:

>What are you doing with the DLookup returned value?

Nothing really. Just trying to see if its already in the table to
prevent the user from inadvertently reprocessing the same VIN.
Here's the snippet. The first three 300's that are rem'd out all
failed in a similar fashion running the DLookUp.

'300:     If DLookup("[PVIN]", "tblPreliminaryVINs",
"[PVIN]=CStr(Forms!frmVINODOtesting!SerialNum)") Then     'Somehow,
JT4RN81A0M0077777 causes a type mismatch in this line
'300:     If DLookup("[PVIN]", "tblPreliminaryVINs",
"[PVIN]=Forms!frmVINODOtesting!SerialNum") Then     'Somehow,
JT4RN81A0M0077777 causes a type mismatch in this line
'300:     If DLookup("[PVIN]", "tblPreliminaryVINs",
"[PVIN]='JT4RN81A0M0077777'") Then     'Somehow, JT4RN81A0M0077777
causes a type mismatch in this line
300:     If DLookup("[PVIN]", "tblPreliminaryVINs", "[PVIN]=""" &
Forms!frmVINODOtesting!SerialNum & """") Then     'Randy Harris
suggestion off NG.
310:         DoCmd.CancelEvent
320:         SendKeys "{Esc}"
330:         MyString = "You have already begun processing on this
VIN. You are awaiting NC DMV faxed reply."
340:         MsgBox MyString, 48, "Be Careful - " & MyApp$ & ", rev. "
& MY_VERSION$
350:         Exit Sub
360:     End If

0
CRCI (2317)
1/11/2006 11:42:43 PM
IF must have something it can coerce into a Boolean. VBA can coerce a
Null into a Boolean. So IF has no problem when DLookup finds nothing
and returns a Null. But when DLookup finds something and returns a
string, IF does have a problem; VBA cannot coerce a string into a
Boolean (unless the string is recognizable as a Boolean, eg "True").

It is your IF that is causing the problem, not your DLookup.
Possibly
IF Nz(DLookup("[PVIN]", "tblPreliminaryVINs", "[PVIN]='" &
Forms!frmVINODOtesting!SerialNum & "'"), "") <> "" Then

or

IF Not IsNull(DLookup("[PVIN]", "tblPreliminaryVINs", "[PVIN]='" &
Forms!frmVINODOtesting!SerialNum & "'")) Then

may help.

(My suggestions have not been tested and may have syntax errors.)

I may say that this is not to be construed I endorse the use of
DLookup. I do not.

0
lylefairfield (1852)
1/12/2006 12:09:43 AM
Oh boy...  Examine the code you have there.  In your If statement, you are
using the DLookup. If the DLookup finds one or more matching records it will
return the PVIN values for those records.  So if there is a match, your
statement is effectively:

If "JT4RN81A0M0077777" Then
  execute some code
End If

The argument for your If needs to evaluate to either True or False.  Yours
does neither.  You could use

If Len(DLookup....) & "" > 0
or
If DLookup.... & "" <> ""

Those would each return a True or False value.

In this case, however, you probably should be using DCount to determine if
there is a matching record already in the table.

If DCount("[PVIN]", "tblPreliminaryVINs", "[PVIN]=""" & _
         Forms!frmVINODOtesting!SerialNum & """") > 0  Then

BTW - I would strongly recommend that you get out of the habit of using
GetKeys.  There is almost always a better alternative.

HTH
-- 
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.


"MLH" <CRCI@NorthState.net> wrote in message
news:is5bs1d46emenoq56ek834neldokhmlvkk@4ax.com...
> On Wed, 11 Jan 2006 23:17:50 GMT, "Randy Harris" <randy@SpamFree.com>
> wrote:
>
> >What are you doing with the DLookup returned value?
>
> Nothing really. Just trying to see if its already in the table to
> prevent the user from inadvertently reprocessing the same VIN.
> Here's the snippet. The first three 300's that are rem'd out all
> failed in a similar fashion running the DLookUp.
>
> '300:     If DLookup("[PVIN]", "tblPreliminaryVINs",
> "[PVIN]=CStr(Forms!frmVINODOtesting!SerialNum)") Then     'Somehow,
> JT4RN81A0M0077777 causes a type mismatch in this line
> '300:     If DLookup("[PVIN]", "tblPreliminaryVINs",
> "[PVIN]=Forms!frmVINODOtesting!SerialNum") Then     'Somehow,
> JT4RN81A0M0077777 causes a type mismatch in this line
> '300:     If DLookup("[PVIN]", "tblPreliminaryVINs",
> "[PVIN]='JT4RN81A0M0077777'") Then     'Somehow, JT4RN81A0M0077777
> causes a type mismatch in this line
> 300:     If DLookup("[PVIN]", "tblPreliminaryVINs", "[PVIN]=""" &
> Forms!frmVINODOtesting!SerialNum & """") Then     'Randy Harris
> suggestion off NG.
> 310:         DoCmd.CancelEvent
> 320:         SendKeys "{Esc}"
> 330:         MyString = "You have already begun processing on this
> VIN. You are awaiting NC DMV faxed reply."
> 340:         MsgBox MyString, 48, "Be Careful - " & MyApp$ & ", rev. "
> & MY_VERSION$
> 350:         Exit Sub
> 360:     End If
>

0
randy46 (685)
1/12/2006 12:18:07 AM
On Thu, 12 Jan 2006 00:18:07 GMT, "Randy Harris" <randy@SpamFree.com>
wrote:

>Oh boy...  Examine the code you have there.  In your If statement, you are
>using the DLookup. If the DLookup finds one or more matching records it will
>return the PVIN values for those records.  So if there is a match, your
>statement is effectively:
>
>If "JT4RN81A0M0077777" Then
>  execute some code
>End If
>
>The argument for your If needs to evaluate to either True or False.  Yours
>does neither.  You could use
>
>If Len(DLookup....) & "" > 0
>or
>If DLookup.... & "" <> ""
>
>Those would each return a True or False value.
>
>In this case, however, you probably should be using DCount to determine if
>there is a matching record already in the table.
>
>If DCount("[PVIN]", "tblPreliminaryVINs", "[PVIN]=""" & _
>         Forms!frmVINODOtesting!SerialNum & """") > 0  Then
Sorry it took so long to get back with you. I was looking for my head.
After peeking into my butt, sure enough, there it was - the last place
I looked, of course. Thanks for setting me straight. Couldn't see the
forest for the trees, or in this case, the light for the sphincter.

>
>BTW - I would strongly recommend that you get out of the habit of using
>GetKeys.  There is almost always a better alternative.
Yeah, the sendkeys thing - I'm using to clear
Forms!frmVINODOtesting!SerialNum. Seems
like an easy way. Its an unbound textbox. Whats 
your best recommendation - I hate sendkeys too.

>
>HTH

0
CRCI (2317)
1/12/2006 2:11:51 AM
On 11 Jan 2006 16:09:43 -0800, "Lyle Fairfield"
<lylefairfield@aim.com> wrote:

>IF must have something it can coerce into a Boolean. VBA can coerce a
>Null into a Boolean. So IF has no problem when DLookup finds nothing
>and returns a Null. But when DLookup finds something and returns a
>string, IF does have a problem; VBA cannot coerce a string into a
>Boolean (unless the string is recognizable as a Boolean, eg "True").
>
>It is your IF that is causing the problem, not your DLookup.
>Possibly
>IF Nz(DLookup("[PVIN]", "tblPreliminaryVINs", "[PVIN]='" &
>Forms!frmVINODOtesting!SerialNum & "'"), "") <> "" Then
>
>or
>
>IF Not IsNull(DLookup("[PVIN]", "tblPreliminaryVINs", "[PVIN]='" &
>Forms!frmVINODOtesting!SerialNum & "'")) Then
>
<snip>
Both good suggestions. Dunno what was wrong with me, but I
had a bad case of it. Thx for setting me straight.
0
CRCI (2317)
1/12/2006 2:13:55 AM
MLH <CRCI@NorthState.net> wrote in
news:7debs1pkpt05vmu5i71ckohccd1n9jf4vm@4ax.com: 

> On Thu, 12 Jan 2006 00:18:07 GMT, "Randy Harris"
> <randy@SpamFree.com> wrote:
>>BTW - I would strongly recommend that you get out of the habit
>>of using GetKeys.  There is almost always a better
>>alternative. 
> Yeah, the sendkeys thing - I'm using to clear
> Forms!frmVINODOtesting!SerialNum. Seems
> like an easy way. Its an unbound textbox. Whats 
> your best recommendation - I hate sendkeys too.
> 
me.undo

-- 
Bob Quintal

PA is y I've altered my email address.
0
rquintal7643 (498)
1/12/2006 3:05:13 AM
Reply: