Two variables output in Dlookup 1

[CTR]

I have a table with user defined data, i.e. company name, address...

Can I use Dlookup to output more than one varialbe? I want to output:
City, State ZIP.
I am considering this option so that it will format correctly on the report without unecessary space or overlaps and the user can edit the data when needed.

Thanks. _____________

Cliff

 

[danvlas]

You have to use the DLookup function for each output value you need, then combine the values.
For instance, your text box control source could be:

=Trim(Nz(DLookup("Field1Name","TableName","Criteria&quot,"&quot & " " & Nz(DLookup("Field2Name","TableName","Criteria&quot,"&quot)

Set the CanGrow and CanShrink properties of the text box to Yes.

Better performance may be achieved by including such values in the report record source (underlying table/query/sql statement). You can do this by a proper IIf combination:
CombinedFieldInQuery: IIf(IsNull(TableName!City),TableName!Zip,TableName!City & " " & TableName!Zip)

What do you mean by "the user can edit the data when needed"? RecordLocks property of the report set to All Records? Change it to No Records and the report will have no 'authority' over live data.

HTH

Dan

 

[LittleSmudge]

If you are doing more than one Dloopuk on the same table with the same set of criteria in the WHERE clause then you'll get better performance at run time ( even if it takes a bit more coding ) by opening a Recordset

rst.open "SELECT City, State, ZIP FROM tblName WHERE clause"

txtControl = rst!City & ", " & rst!State & " " & rst!ZIP



'ope-that-'elps.

GLS

 

[CTR]

Thanks to both. I used Dan's suggestion as that was the one I understood.
The user has the ability to change some of the info that will print on the header of a report, i.e. their company name and address. There will only be one value in the table, so there is no criteria set (at this time).
It works great, thanks! _____________

Cliff

 

[CTR]

OK, how about an IIf with a Dlookup? There is a subreport on the report that has a calcualted value. If it is above a certain limit a statement prints:

=IIf([Donor Plasma Loss Query].[Report]![plasma]>=12001,"blah blah blah.","&quot

Can I put a Dollokup in the true part of the IIf? I've tried a few times with no success. I get a syntax error.

I was thinking of this:

=IIf([Donor Plasma Loss Query].[Report]![plasma]>=12001,"DLookup ([val1], UserVariables","&quot

I know this isn't the best method, but I'm new and giving it a "logical, untrained" attempt. _____________

Cliff