DLookup with parameter

[sohunter]

I'm trying to put a textbox in a report header that displays a name instead of ID. The user enters an id in a parameter from the report's underlying query, and I was hoping to base my dLookup criteria on it, but I keep getting error in that field.

Is it something with my quotes?

=DLookup("[Name]","Provider","[ProviderID] = " & [Enter ProviderID:])

(I want to display the Name field from the Provider table, instead of the ProviderID)

 

[LittleSmudge]

So the ProviderId is already available in the RecordSource ?

Then why get the user to enter it again ?

As it is in a REPORT - replace the text box with a combo box

Bind the combo box to ProviderId
Set the combo Rowsource to
"SELECT ProviderId, Name FROM Provider"
Column Count = 2
Bound Column = 1
Column Width = 0;

The report will then display a 'text box'* showing the [Name] associated with the Id

* 'Text Box' because a combo on a report is meaningless so it never displays the dropdown.



'ope-that-'elps



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[sohunter]

Aw, yes, the plot thickens.

The report is based on a cross tab query that doesn't have the providerID and providerId isn't in the detail of the report. The providerID is in a query the crosstab is based on. I actually have wild cards before or after the user parameter so that the user can 1) specify one provider or 2) get all providers

So, my DLookUp is actually going to be within an IIF (if user actually entered an ID in that parameter) but I didn't want to muddy the waters. Let's just assume the user did enter a ID number - and the report header is the only place the provider will ever show.

I like your suggestion, though - might be able to use it for something else down the line.

 

[dhookom]

I don't ever use parameter queries because the user interface is not very functional compared to a control on a form. If you use a control, you can use a text, combo, or list box. You can also set a default and check for the integrity of the value that gets entered. Also, the form can stay open so the value is always available for "grabbing" by the report.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[sohunter]

I agree with you, dhookom, but there's very few hours budgeted for this work, and we've agreed that I'll do it all just with Access defaults/built-ins. Just reports and queries, no GUI whatsoever.

And I must admit...the parameter feeding into the DLookup works now...BECAUSE I WASN'T SPELLING THE TABLE CORRECTLY! AGHHHH!!! Oh, the simplest things you discover.