Using DLookup with an Expression from a field

[TechnoBishop]

Hi everyone, I'm having a bit of an issue with DLookup and can't seem to figure out why.



I'm building a database to keep track of sign up info for a annual family camp. Each family has a record which includes who's attending, when they'll be there, etc. In a separate table, I've listed out the costs for each age category the campers fall into (baby, child, adult) with and without an early sign up discount, and each record corresponds to how many days the camper will be onsite. See below:



For each family record, I have the following:

1. a field that calculates how long they'll be staying (for ease of entry) based on when they arrive and depart
2. a field that returns early or late based on their sign-up date
3. and a field that returns their age category based on the entered age

All this is used in a DLookup within a control box on the entry form to list what their cost per camper will be, and that's where I run into an issue.

If I run my calculation with this code:

=DLookUp("EarlyAdult","Rates","ID =  [Camp Attendees]![Time1]")

It correctly returns the cost from my table. However, what I'd like to do is replace the "EarlyAdult" in the code with a reference to a field that has the fieldname I want to look for stored in it.

I've tried this:

=DLookUp("[Camp Attendees]![RateType1]","Rates","ID =  [Camp Attendees]![Time1]")

But Access thinks I'm looking for a field called RateType1, not the data stored in that field, and returns #Error in my control box. How do I get Access to use the data in [RateType1] to look for a field rather than look for [RateType1] itself?

[Micron]

Your biggest problem is probably going to be the table design (it's laid out like a spreadsheet). You appear to be suffering from Spreadsheetitis, so maybe take a look at normalization. The next is that you're using calculated fields in a table(s) - at least that's how the post reads. The next is that you cannot refer to a form control as the source of criteria for DLookup without telling Access to start looking for the control on Forms!frmFormName. Next, take a look here or anywhere else you fancy, at the structure of DLookup, where you should not find any examples of where ! or . is used in the field reference for a domain. Lastly, DLookup is for retrieving a value from a record from a field in a domain. That domain has to be a query or table (or maybe a recordset) - it cannot be a form, which is how I interpret your post. If that's not correct, then you should refer to form/report controls as controls and query or table "columns" as fields in order to distinguish from one another. Or say form fields, or be specific as to whether or not you're referring to a form or table.

[TechnoBishop]

Your biggest problem is probably going to be the table design (it's laid out like a spreadsheet). You appear to be suffering from Spreadsheetitis, so maybe take a look at normalization.

I definitely suffer from spreadsheetitis, as that's where most of my experience lies.

The next is that you're using calculated fields in a table(s) - at least that's how the post reads. The next is that you cannot refer to a form control as the source of criteria for DLookup without telling Access to start looking for the control on Forms!frmFormName.

I am using calculated fields in a table, as to me that's the simplest (but maybe not the best) way of making use of the data I have. I could pull some or all of the calculation into a query or other formula accepting source, but that would require quite a bit of nested code that I would like to avoid. The code I posted is in a control box on my entry form as part of the control source (a calculated control, I believe it's called), so I'm not referencing a form at all.

Next, take a look here or anywhere else you fancy, at the structure of DLookup, where you should not find any examples of where ! or . is used in the field reference for a domain.

I agree, there's no listing of it, however, that is the way it is inserted into the formula when I try and reference that field using the available dropdowns.

Lastly, DLookup is for retrieving a value from a record from a field in a domain. That domain has to be a query or table (or maybe a recordset) - it cannot be a form, which is how I interpret your post. If that's not correct, then you should refer to form/report controls as controls and query or table "columns" as fields in order to distinguish from one another. Or say form fields, or be specific as to whether or not you're referring to a form or table.

So, my DLookup is successfully finding the data in the "EarlyAdult" field, located in the "Rates" table, where the ID field matches "Time1" (a calculated field that does some math with their arrival and departure days, and returns a number, in this case "4"). What I'd like to do is have "EarlyAdult" be the data in a field (which is currently called RateType1) in my "Camp Attendees" table, and have DLookup use the data stored in RateType1 as the fieldname.

Maybe the data that is saved in RateType1 needs to be put together inside of the DLookup, but I'd like to try and avoid that.

[June7]

I really don't see how the correct rate is returned. You want the source field and the filter criteria to be dynamic. Those are variables. Concatenate variables and don't put them within quotes.

=DLookup([RateType1], "Rates", "ID = " & [Time1])

This expression needs to be in a textbox on the same form that has [RateType1] and [Time1] fields in its RecordSource or could be in a query that includes those fields. Saving the retrieved value into a table will require code (macro or VBA) or an UPDATE action SQL.

[pbaldy]

Post 3 was moderated, posting to trigger email notifications.

[TechnoBishop]

June7, I just tested the code updated with your suggestion and it works properly now! For all the searching and reading I did on DLookup, that info never came up. Thanks for the help!