Dlookup function

[nybaseball22]

Hello,

I am trying to use a dlookup function on a form nd keep getting an
error. I am trying to lookup a number from one table based on a date
in my form. Specifics:

Date comes from table "ExpensesMain" and is on form "ExpensesDetail"
in a field called "Report Date". I am trying to pull a number (field
called "Report Number") from a table called "ExpenseRPTNumber". This
table also has a field called "Report Date" that has the same values
as Report Number in "ExpensesMain" table.

The code I am using is:
=DLookUp("[Report Number]","[ExpensesMain]","[Report Date]='" & Forms!
ExpensesDetail![Report Date] & "'")

I keep getting an #Error.

Thanks for the help.

 

[Allen Browne]

Assuming that the [Report Date] field in ExpensesMain table is a Date/Time
field, you need to use # instead of quotes as the delimiter:

=DLookUp("[Report Number]","[ExpensesMain]",
"[Report Date] = #" & Forms!ExpensesDetail![Report Date] & "#")

If the Report Date text box on the form is unbound, it may help to set its
Format property to General Date so Access understands it should be treated
as a date.

 

[Guest]

Your syntax would be correct if Report Date were a string. If it is a date
data type, you use # as the delimiters. Also, you said the Report Number is
in the table ExpenseRPTNumber, so that is the table you should be querying.

=DLookUp("[Report Number]","[ExpenseRPTNumber]","[Report Date]= #" & Forms!
ExpensesDetail![Report Date] & "#")

ExpenseRPTNumber

 

[nybaseball22]

Perfect. Thank you very much.

How about one more?

I am using a dlookup on a sub form that opens on another form. Is
there a way to change the code so it will lookup the value on the sub
form when it is opened on the main form?

I can send the details if needed, but it is very similar to the last
examle.

Thanks again.

 

[Allen Browne]

Not sure I follow.

Perhaps you could use the Current event of a form (or possibly its Load
event) to perform you DLookup() if you are sure the right record is loaded.

 

[nybaseball22]

Ok, I will give the details. I have 2 subforms called
"AutosGasData1sub" and "AutosGasData2sub". There is a field on each
subform called "Best MPG" that pulls the best MPG from queries called
"AutosGasStats1" and "AutosGasStats2" respectively. The subforms are
attached to a form called "AutomobileGasolineSearch".

I have added lookup fields to find the date of the Best MPG, and it
works when I open the subforms in form view. However, when I open the
subforms on the Main form, I get an #Error.

Thanks again.

 

[Allen Browne]

Okay, I'm not sure how these forms relate to the DLookup() expression that
referes to a date on the ExpensesDetail form.

If you have something like:
Forms!AutosGasData1sub![best MPG]
that will work when you open AutosGasData1sub as a form, but not as a
subform. That's because the subform is not open in its own right, i.e. it is
not part of the Forms collection.

You may need to use an expression such as:
Forms!AutomobileGasolineSearch!AutosGasData1sub.Form![Best MPG]

Explanation in:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html