DLOOKUP() problem

[Kicker]

Have been reading a lot of posts about this subject, but nothing seems to work....

To make things simple, I am just trying to get it to work before going any further and making it complex.

Table name: Officers
Field wanted: BadgeNum
Field Have: LName

Table is sorted on LName in Ascending order

The form has a textbox named LName and it is linked to the Officers table LName field.

dim testVal as string

testVal = DLookup("[BadgeNum]", "Officers", "[LName] = " & Me.LName.Value)

When I select debug and place the cursor on the "me.LName.value" section, the proper name appears so I know it is initialized properly.

(I did a cut and paste to get the above code here because I didn't want to make a typing mistake....

When run, it gives me the error...."The object doesn't contain the Automation Object "McCain."

I need a drink.....

I forgot...I do not have any relational items setup yet. so there is no referential integrity

ttfn
Kicker

 

[WayneRyan]

Hi Kicker,

testVal = Nz(DLookup("[BadgeNum]", "Officers", "[LName] = '" & Me.LName.Value & "'"), "")

You need add the Single-quotes to your criteria (to be nice to SQL).
Also, if not found, it will return a Null and give you problems. The
Nz function takes care of that.

Wayne

 

[Kicker]

Thank you Wayne....

It never stops to amaze me how much help a person can get from the variety of forums out here. What did we ever do without them????

ttfn
Kicker

 

[namliam]

Just 1 other thing

A dLookup can return a Null value, to prevent possible errors in the future you should do 1 of 2 things...

1)
dim testVal as Variant ' as variants can contain nulls and strings cant

2)
testVal = Nz(DLookup("[BadgeNum]", "Officers", "[LName] = '" & Me.LName.Value & "'"), "")
Which as i can see now had allready been provided or iin another way:
testVal = DLookup("[BadgeNum]", "Officers", "[LName] = '" & Me.LName.Value & "'") & vbnullstring

Just 1 last tip: Me.LName.Value is the same as Me.LName

.value is the default property and therefor can be omitted.

Regards

 

[Mike375]

Excuse me for butting in

But I remain puzzled at what code does that can't be done in macros.

For example I have this type of thing running down a macro and the conditions determing which one does the SetValue

=DLookUp("[LL13Wk]","1LOIRatesMnew","[AgeNB]=[Forms]![SumPremAllCats1]![anb]")*1.25*[LOIOcc]

The above is for calculating insurance premium rates and the table 1LOIRatesMnew has the base premium rates.

Someone tell me what I am missing from not using code. At the moment I am limited to using code to open Word at a specified .doc and insert data from Access fields.

Mike

 

[Rich]

Why use DLookUp ? a combo box will work better

 

[Mike375]

Why use DLookUp ? a combo box will work better

How would you get a combo to work where the value you want is an intersection of a row and a column? Also the combo requires someone to click and select etc. whereas DLookUp just does it on its own

Mike