Issue with seemingly simple DLookUp()

[steezy]

Hello all,



I've been spending most of the day setting up a new database in Access and in one of the first forms I'm creating, I'm having an issue.

For some reason, this expression I'm using as a control in a textbox simply will not return anything other than null values.

(For background, I have a number of different types of events in table EventTypes. Each record in the table has an integer for Days. In this instance, when someone selects the title of the event in the combo box above, I want this text box to simply pull the Days value.)

=Nz(DLookUp("[Days]","[EventTypes]","[Title]='" & [Forms]![New Event]![EventDesired] & "'"),0)

I've tried a number of different ways to pull this info, and it simply returns zero every time. Where am I going wrong? I've been searching for hours and getting nowhere.

[pbaldy]

This might be another option:

BaldyWeb - Autofill

Offhand, the syntax of the DLookup() looks correct, presuming Title is a text field. Have you confirmed what the form control contains?

[June7]

First, have you used Lookups in tables? I NEVER do that http://access.mvps.org/access/lookupfields.htm

Second, what data type is EventDesired?

Is this field on the same form as the DLookup expression textbox? Try:

=Nz(DLookUp("[Days]","[EventTypes]","[Title]='" & [EventDesired] & "'"),0)

Why do you need to use DLookup()? Usually better methods - include table in RecordSource or reference combobox column.

[steezy]

This might be another option:

BaldyWeb - Autofill

Offhand, the syntax of the DLookup() looks correct, presuming Title is a text field. Have you confirmed what the form control contains?

Thanks for the link Paul, I had just found it myself. I'm going to try that.

First, have you used Lookups in tables? I NEVER do that http://access.mvps.org/access/lookupfields.htm

Second, what data type is EventDesired?

Is this field on the same form as the DLookup expression textbox? Try:

=Nz(DLookUp("[Days]","[EventTypes]","[Title]='" & [EventDesired] & "'"),0)

Why do you need to use DLookup()? Usually better methods - include table in RecordSource or reference combobox column.

Yes, I have used lookups in tables... but the field Title in the EventTypes table is not a lookup. Being a newbie, I didn't realize that wasn't good practice. What should I be doing instead? (And just to answer the question, EventDesired is a text string.

EDIT: Also, June7's modified expression didn't work any differently.

[June7]

Instead of lookups in tables, build comboboxes on forms.

What is the combobox RowSource? Is the actual value of the combobox an ID or the event description?

If you want to provide db for analysis, follow instructions at bottom of my post.

[steezy]

Here's the Row Source. The value in the combobox is Title, not the ID.

SELECT [EventTypes].[EventTypeID], EventTypes.[Title] FROM EventTypes;

I may take you up on the offer to send the db for analysis.

Thanks,
Sam

EDIT: I actually have a larger issue with this file, but I will start a new thread for it eventually.

[June7]

The EventTypeID field is the first field in the RowSource. If the BoundColumn is set to 1 then the actual value of the combobox is EventTypeID, not Title. This would explain why the DLookup criteria is not working. Even though EventDesired is a text field, if the BoundColumn is set to 1 then the EventTypeID is saved, not Title.

=Nz(DLookUp("[Days]", "[EventTypes]", "[EventTypeID]=" & [EventDesired]),0)

Now this will possibly generate "datatype mismatch" error, if EventTypeID is a number field and EventDesired is text field, even if it is storing digit values, it's still a text field.