Report - General Question... 1

[sanders720]

I have a report that is returning data. I would like to take data from my detail, and use dlookup for those specific rows. Is this possible?

 

[Steve101]

This is certainly possible. You can either put the dlookup command as the control source of the report's text control;

eg. =Dlookup("expr", "table", "criteria&quot

or you can assign the results of a Dlookup to an unbound text control on the report as part of the Detail line's OnFormat event.

You might however be better off extending the query underlying your report to incorporate the data you're looking up, by using an inner join. Post more details if you need further help,



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[sanders720]

I really appreciate your help. You've pointed me the right direction - I need just a little more help if you've got the time.

=dlookup([Hours], timecards, [Employee Number] = 2255 AND [Date] =#4/21/2003# AND [Job Number] = 36435 AND [Order Number] = 1A AND [Op Number] =95)

I'm having a syntax problem with the above statement. Do you have any answers that could shed some light on the problem?

 

[sanders720]

The dLookup would be nice. Basically, I have a report that spits out the Employee Number, Job Number, Order Number, and Operation Number for a particular week. The date is excluded, as I just want the particular different data for the week range. I have excluded the date because this woulld duplicate data for different days.


SELECT DISTINCT TimeCards.[Employee Number], TimeCards.[Job Number], TimeCards.[Order Number], TimeCards.[Op Number]
FROM TimeCards
WHERE (((TimeCards.[Employee Number])=2255) AND ((TimeCards.Date)>=#4/21/2003# And (TimeCards.Date)<=#4/27/2003#));


Now, I want to lookup the hours worked on each day. There are seven columns with seven text boxes. This is where I hoped to use dLookup, and base the criteria from the results of the query above, which now populates each row of my report.

Thanks again for your help.

 

[Steve101]

sanders720,

A couple of points re your last posts:

(a) The syntax for the DLookup command requires the three parameters to be strings. Thus your version:
[tt]
=dlookup([Hours], timecards, [Employee Number] = 2255 AND [Date] =#4/21/2003# AND [Job Number] = 36435 AND [Order Number] = 1A AND [Op Number] =95)
[/tt]
needs to be modified to reflect this; ie; something like:

=dlookup([red]&quot;[/red][Hours][red]&quot;[/red], [red]&quot;[/red]timecards[red]&quot;[/red], [red]&quot;[/red][Employee Number] = 2255 AND [Date] =#4/21/2003# AND [Job Number] = 36435 AND [Order Number] = [red]'[/red]1A[red]'[/red] AND [Op Number] =95[red]&quot;[/red])

(b) Now in your report, you'd presumably like to tie the constants in the above example, to actual controls in the detail section of the report. To do this, use the control names, the & Concatenation operator, and our friends, single and double quotes; for example, the above line could become:


=dlookup([red]&quot;[/red][Hours][red]&quot;[/red], [red]&quot;[/red]timecards[red]&quot;[/red], [red]&quot;[/red][Employee Number] = [blue]&quot; & DetailField1 & &quot;[/blue] AND [Date] = [blue]CVDate('&quot; & DetailField2 & &quot;') [/blue] AND [Job Number] = [blue]&quot; & DetailField3 & &quot;[/blue] AND [Order Number] = [red]'[/red][blue]&quot; & DetailField4[/blue] & &quot;[red]'[/red] AND [Op Number] =[blue]DetailField5[/blue][red]&quot;[/red])

(c) As you can see, getting the syntax right can be a bit tricky, and you do need to know how to construct string expressions etc.

Finally, I'm still now quite sure I understand what you're trying to do; you might be better off to constuct a single query up front which contains the extended info you require, using the appropriate table joins in the query.

Let me know if this helps,

Cheers,




Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[sanders720]

Why can't I pass this information to my control source in code. The dlu string below interprets correctly!

=DLookUp(&quot;[Hours]&quot;,&quot;timecards&quot;,&quot;[Employee Number]=2255 AND [Date]=#4/21/2003# AND [Job Number]='36435' AND [Order Number]='1A' AND [Op Number]=95&quot

I can paste this manually into the controlsource with good results! Is there a secret to doing this in VBA?

Thanks for the help!

dlu = &quot;=dlookup(&quot; & Chr(34) & &quot;[Hours]&quot; & Chr(34) & &quot;,&quot; & Chr(34) & &quot;timecards&quot; & Chr(34) & &quot;,&quot; & Chr(34) & &quot;[Employee Number]=&quot; & txtEN.Value & &quot; AND [Date]=#&quot; & txtPS.Value & &quot;# AND [Job Number]=&quot; & Chr(39) & Me.txtJobNumber.Value & Chr(39) & &quot; AND [Order Number]=&quot; & Chr(39) & Me.txtOrderNumber.Value & Chr(39) & &quot; AND [Op Number]=&quot; & Me.txtOperationNumber.Value & Chr(34) & &quot&quot;

txtXXX.ControlSource = dlu