DLookup for Report!

[Bob V]

I am trying to get the Value of Payable in qryAccountStatus with the same
OwnerID
On my Report there is a text box with the OwnerID, is this close???
Thanks for any help...........Bob
DLookUp("Payable","qryAccountStatus","OwnerID=" & [tbOwnerID])

 

[Pat Hartman]

You need to precede the dLookup() with an = sign in the event property.

However, dLookup() and other domain functions should not be used when simple
joins will obtain the necessary data. Based on what your dLookup() looks
like, you can modify the report's RecordSource query to join to
qryAccountStatus and obtain the Payable field directly. This will be
significantly more efficient for Jet or SQL Server. You can then just
choose Payable from the list of fields available for your report and bind a
control to it.

 

[Bob V]

Thanks Pat, worked great , just needed that dam =
Regards Bob

You need to precede the dLookup() with an = sign in the event property.

However, dLookup() and other domain functions should not be used when
simple joins will obtain the necessary data. Based on what your dLookup()
looks like, you can modify the report's RecordSource query to join to
qryAccountStatus and obtain the Payable field directly. This will be
significantly more efficient for Jet or SQL Server. You can then just
choose Payable from the list of fields available for your report and bind
a control to it.

I am trying to get the Value of Payable in qryAccountStatus with the same
OwnerID
On my Report there is a text box with the OwnerID, is this close???
Thanks for any help...........Bob
DLookUp("Payable","qryAccountStatus","OwnerID=" & [tbOwnerID])

 

[Pat Hartman]

I guess you didn't pay attention to my suggestion on how it should be done
Just because the dLookup() will work in this situation, doesn't mean
that you should use it. You obviously have only a small amount of data or
your post would be more like "why is this report sooooooo slooooow". What
you are asking Access to do is to run a separate query for each report
record. So, if your report has 100 records, 100 additional queries need to
be run - one for each row. With the join method, the jet engine gets to
optimize the lookup.

Thanks Pat, worked great , just needed that dam =
Regards Bob

You need to precede the dLookup() with an = sign in the event property.

However, dLookup() and other domain functions should not be used when
simple joins will obtain the necessary data. Based on what your
dLookup() looks like, you can modify the report's RecordSource query to
join to qryAccountStatus and obtain the Payable field directly. This
will be significantly more efficient for Jet or SQL Server. You can then
just choose Payable from the list of fields available for your report and
bind a control to it.

I am trying to get the Value of Payable in qryAccountStatus with the
same OwnerID
On my Report there is a text box with the OwnerID, is this close???
Thanks for any help...........Bob
DLookUp("Payable","qryAccountStatus","OwnerID=" & [tbOwnerID])

 

[Bob V]

Thanks Pat I have 2 Reports
Statement / which has 25 =DLookUp Fields,"tblCompanyInfo
with No record Source
-------------------------------------------
Invoices / with a record source
SELECT tblInvoice.InvoiceID, tblInvoice.InvoiceNo, tblInvoice.OwnerName,
tblInvoice.OwnerAddress, tblInvoice.HorseID, tblInvoice.HorseName,
funGetHorse(tblInvoice.InvoiceID,tblInvoice.HorseID) AS Horse,
tblInvoice.InvoiceDate, tblInvoice.SubTotal, tblInvoice.TotalAmount,
tblInvoice.OwnerPercentAmount, tblInvoice.GSTOptionsText,
tblInvoice.GSTOptionsValue, tblInvoice.GSTContentsText,
tblInvoice.GSTContentsValue, tblCompanyInfo.GSTNoLable,
tblCompanyInfo.GSTNo, tblGSTOptions.GSTPercentage,
tblCompanyInfo.CompanyAddress, tblCompanyInfo.CompanyWebAddress,
tblCompanyInfo.CompanyName, tblCompanyInfo.CompanyOwnerName,
tblCompanyInfo.CompanyPhone1, tblCompanyInfo.CompanyPhone2,
tblCompanyInfo.CompanyEmail, tblInvoice.OwnerPercent, BottomComment FROM
tblCompanyInfo, tblInvoice, tblGSTOptions WHERE
tblInvoice.GSTOptionsText=tblGSTOptions.GSTOptionsText And
tblInvoice.CompanyID=tblCompanyInfo.CompanyID And tblInvoice.InvoiceID=465;
***So should I set my Statement up the same as my Invoice,,Thanks for your
advise....Bob

I guess you didn't pay attention to my suggestion on how it should be done
Just because the dLookup() will work in this situation, doesn't mean
that you should use it. You obviously have only a small amount of data or
your post would be more like "why is this report sooooooo slooooow". What
you are asking Access to do is to run a separate query for each report
record. So, if your report has 100 records, 100 additional queries need to
be run - one for each row. With the join method, the jet engine gets to
optimize the lookup.

Thanks Pat, worked great , just needed that dam =
Regards Bob

You need to precede the dLookup() with an = sign in the event property.

However, dLookup() and other domain functions should not be used when
simple joins will obtain the necessary data. Based on what your
dLookup() looks like, you can modify the report's RecordSource query to
join to qryAccountStatus and obtain the Payable field directly. This
will be significantly more efficient for Jet or SQL Server. You can
then just choose Payable from the list of fields available for your
report and bind a control to it.



I am trying to get the Value of Payable in qryAccountStatus with the
same OwnerID
On my Report there is a text box with the OwnerID, is this close???
Thanks for any help...........Bob
DLookUp("Payable","qryAccountStatus","OwnerID=" & [tbOwnerID])