Format on report Problem

[Bob V]

Trying to get BillDate and PaidAmount to format on a Report
Thanks for any Help....Bob
="Last Payment"+DLookUp("Format,d-mmm-yy[BillDate] & ' ' &
Format,($#,##0.00)[PaidAmount]","qryAccountStatus","OwnerID=" & [tbOwnerID])

 

[fredg]

Trying to get BillDate and PaidAmount to format on a Report
Thanks for any Help....Bob
="Last Payment"+DLookUp("Format,d-mmm-yy[BillDate] & ' ' &
Format,($#,##0.00)[PaidAmount]","qryAccountStatus","OwnerID=" & [tbOwnerID])

Why make life difficult? Juse use 2 DLookUps.

="Last Payment " &
Format(DLookUp("[BillDate]","qryAccountStatus","[OwnerID] = " &
Me.[OwnerID]),"d-mmm-yy") & " " &
Format(DLookUp("[PaidAmount]","qryAccountStatus","[ID] = " &
Me.[OwnerID),"$ #,##0.00")

 

[boblarson]

Plus Fred didn't explicitly say it (although he did do it in his example) -

Format it AFTER you pull it, not before.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________

Trying to get BillDate and PaidAmount to format on a Report
Thanks for any Help....Bob
="Last Payment"+DLookUp("Format,d-mmm-yy[BillDate] & ' ' &
Format,($#,##0.00)[PaidAmount]","qryAccountStatus","OwnerID=" & [tbOwnerID])

Why make life difficult? Juse use 2 DLookUps.

="Last Payment " &
Format(DLookUp("[BillDate]","qryAccountStatus","[OwnerID] = " &
Me.[OwnerID]),"d-mmm-yy") & " " &
Format(DLookUp("[PaidAmount]","qryAccountStatus","[ID] = " &
Me.[OwnerID),"$ #,##0.00")

 

[Bob V]

Thanks Fred having trouble in getting it to work, This is the 2 text boxes
that do work that I am trying in integrate
=DLookUp("BillDate","qryAccountStatus","OwnerID=" & [tbOwnerID])
=DLookUp("PaidAmount","qryAccountStatus","OwnerID=" & [tbOwnerID])
Thanks for your help............Bob

Trying to get BillDate and PaidAmount to format on a Report
Thanks for any Help....Bob
="Last Payment"+DLookUp("Format,d-mmm-yy[BillDate] & ' ' &
Format,($#,##0.00)[PaidAmount]","qryAccountStatus","OwnerID=" &
[tbOwnerID])

Why make life difficult? Juse use 2 DLookUps.

="Last Payment " &
Format(DLookUp("[BillDate]","qryAccountStatus","[OwnerID] = " &
Me.[OwnerID]),"d-mmm-yy") & " " &
Format(DLookUp("[PaidAmount]","qryAccountStatus","[ID] = " &
Me.[OwnerID),"$ #,##0.00")

 

[fredg]

Thanks Fred having trouble in getting it to work, This is the 2 text boxes
that do work that I am trying in integrate
=DLookUp("BillDate","qryAccountStatus","OwnerID=" & [tbOwnerID])
=DLookUp("PaidAmount","qryAccountStatus","OwnerID=" & [tbOwnerID])
Thanks for your help............Bob

Trying to get BillDate and PaidAmount to format on a Report
Thanks for any Help....Bob
="Last Payment"+DLookUp("Format,d-mmm-yy[BillDate] & ' ' &
Format,($#,##0.00)[PaidAmount]","qryAccountStatus","OwnerID=" &
[tbOwnerID])

Why make life difficult? Juse use 2 DLookUps.

="Last Payment " &
Format(DLookUp("[BillDate]","qryAccountStatus","[OwnerID] = " &
Me.[OwnerID]),"d-mmm-yy") & " " &
Format(DLookUp("[PaidAmount]","qryAccountStatus","[ID] = " &
Me.[OwnerID),"$ #,##0.00")

I don't quite understand.
Before, you were using one text control, for which I gave you the
correct (I believe) syntax.
Now you are using 2 controls, and say you're having trouble getting it
to work.
But you haven't said what trouble you are having.
Please let us know 'what' the troule is next time.

I'll take 2 guesses.

1) I'll guess you are getting an #error in the control.
Make sure the name of the control is not the same as the name of any
field used in it's control source expression.

2) I'll guess it's not returning any value.
The Where clause may have incorrect syntax because the datatype of
the criteria field in the query ([OwnerID]) in not a number datatype.
I went with the datatype you used in the original post (Number
datatype).

If it is a text datatype, then use "[OwnerID] = '" & Me![tbOwnerID] &
"'"
where [OwnereID] is the name of the field in the query and [tbOwnerID]
is the name of the control ON THE FORM or REPORT that contains the
value you wish to filter in the query.
You need to verify that you are using the correct field and control
names, and that their datatypes match the query field datatypes.

Look up DLookUP
as well as
Restrict data to a subset of records
in VBA help files