SimpleDLookup - "So I thought"

[RuralGuy]

Len simply returns a number. It sounds like you are still having problems with this code.

[KathyL]

I just trying to do a simple lookup in my UsedbookingNumber table to check if a numbers has been used.
This code is runs after LostFocus of the BookingNoticeBookingNo field.

DLookup("[UsedBookingNum]", "UsedBookingNumber", "[UsedBookingNum] = _
" & Forms![frmAsiaBooking]![BookingNoticeBookingNo])

I have tried many variations and get errors like:
"Type Mismatch" ,
"The expression you entered as a query parameter produced this error: WLK533741"
Which happens to be first number in my used number table.
and Runtime error 3075. Syntax error (missing operator) "[UsedBookingNum] = "
I have verified all fields are data type "Text"


Thank you in advance for any and all suggestions

OldCityCat

Do you have the lookup field defined with an index or key?

Are you doing this lookup as a calculated field on a form, or in event VB code? In a form's calculated field/text box, be sure you have included the '=' in front of the Dlookup.

I've been tripped up with the very error you're getting, 'cause I forgot the '=', or not having the field defined with an index.

[OldCityCat]

KathyL,

Do you have the lookup field defined with an index or key? "Yes indexed allow dups"

Are you doing this lookup as a calculated field on a form, No

or in event VB code? In a form's, "This code is runs after LostFocus"

calculated field/text box, be sure you have included the '=' in front of the Dlookup. " It's If DLookup"

I've been tripped up with the very error you're getting, 'cause I forgot the '=', or not having the field defined with an index.

Thanks
OldCityCat

[RuralGuy]

Are you using the code I posted in Post #8? You're still having trouble? Copy and Paste the code you are using into a post, please.

[OldCityCat]

Sorry I forgot to say it was working
Now I'm just trying to understand how/why

When adding a new record, and using a booking number that I know is in the UsedBookingNumber table.
DLookup should have found a match ,but still returned an Type mismatch error?

Where does Null come into play?

So LEN looks for any string grater then 0?

Don't mean to be a pain, just want to learn.

OldCityCat

[RuralGuy]

Have you looked up DLookup() and Len() in help to see how they work?

[OldCityCat]

Yes,
I what to understand the difference between your code and my original code.

[RuralGuy]

One of the biggest issues was you were treating the [UsedBookingNum] field as a number when it was a string.

[KathyL]

or in event VB code? In a form's, "This code is runs after LostFocus"

calculated field/text box, be sure you have included the '=' in front of the Dlookup. " It's If DLookup"

I've been tripped up with the very error you're getting, 'cause I forgot the '=',

you still have to have an '=' in VB code, I think. On either a DLookup or IIF

http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx

http://office.microsoft.com/en-us/ac...001228853.aspx

DLookup itself is not VB code, it is a Function. And you have to equate the function to something.