showing just the latest value in a text box

[pat67]

Ok i have a form with a query showing value from different weeks in a
chart. The numbers are large so i cannot show the values for each on
the chart because then the chart would be difficult to read. So what i
am trying to do is show the current value in a text box. I used dsum
like this in the control source

=DSum("[Cancel Value]","qryExc_Message_Weekly_1",Max([Date]))

but this totals the entire list not the max date. I ran a query and i
can get the result i am looking for if i use the exact date,
5/16/2011, in the criteria of the Date field. So i need to know how to
do that in a text box. Any ideas?

 

[John W. Vinson]

Ok i have a form with a query showing value from different weeks in a
chart. The numbers are large so i cannot show the values for each on
the chart because then the chart would be difficult to read. So what i
am trying to do is show the current value in a text box. I used dsum
like this in the control source

=DSum("[Cancel Value]","qryExc_Message_Weekly_1",Max([Date]))

but this totals the entire list not the max date. I ran a query and i
can get the result i am looking for if i use the exact date,
5/16/2011, in the criteria of the Date field. So i need to know how to
do that in a text box. Any ideas?

The last argument to a Domain function (if you include it at all) must be a
valid SQL WHERE clause withouth the word Where. I'd guess that you want to use
DMax() to find the maximum date and construct a criterion for that date:

=DSum("[Cancel Value]","qryExc_Message_Weekly_1","[Date] = #" & DMax("[Date]",
"[qryExc_Message_Weekly_1]") & "#")

Not sure what range of dates you want for the "max", this just finds the
latest date in qryExc_Message_Weekly_1.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[pat67]

Ok i have a form with a query showing value from different weeks in a
chart. The numbers are large so i cannot show the values for each on
the chart because then the chart would be difficult to read. So what i
am trying to do is show the current value in a text box. I used dsum
like this in the control source

=DSum("[Cancel Value]","qryExc_Message_Weekly_1",Max([Date]))

but this totals the entire list not the max date. I ran a query and i
can get the result i am looking for if i use the exact date,
5/16/2011, in the criteria of the Date field. So i need to know how to
do that in a text box. Any ideas?

The last argument to a Domain function (if you include it at all) must bea
valid SQL WHERE clause withouth the word Where. I'd guess that you want to use
DMax() to find the maximum date and construct a criterion for that date:

=DSum("[Cancel Value]","qryExc_Message_Weekly_1","[Date] = #" & DMax("[Date]",
"[qryExc_Message_Weekly_1]") & "#")

Not sure what range of dates you want for the "max", this just finds the
latest date in qryExc_Message_Weekly_1.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

I am just looking for the latest date. Let me try this. Thanks

 

[pat67]

Ok i have a form with a query showing value from different weeks in a
chart. The numbers are large so i cannot show the values for each on
the chart because then the chart would be difficult to read. So what i
am trying to do is show the current value in a text box. I used dsum
like this in the control source

=DSum("[Cancel Value]","qryExc_Message_Weekly_1",Max([Date]))

but this totals the entire list not the max date. I ran a query and i
can get the result i am looking for if i use the exact date,
5/16/2011, in the criteria of the Date field. So i need to know how to
do that in a text box. Any ideas?

The last argument to a Domain function (if you include it at all) must bea
valid SQL WHERE clause withouth the word Where. I'd guess that you want to use
DMax() to find the maximum date and construct a criterion for that date:

=DSum("[Cancel Value]","qryExc_Message_Weekly_1","[Date] = #" & DMax("[Date]",
"[qryExc_Message_Weekly_1]") & "#")

Not sure what range of dates you want for the "max", this just finds the
latest date in qryExc_Message_Weekly_1.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Ok. That gives me the same as I have when i did it this way

=DLookUp("sum([Cancel Value])","qryExc_Message_Weekly_1","[Date]
=[txtDate]") where the txtDate box value is this
=DateSerial(Year(Date()),Month(Date()),Day(Date())-Weekday(Date())
+2) so it shows the Monday of the week.

The probem is i am looking for this value to change when I filter the
chart. This always gives me the overall total. I have this sql in my
chart so that when i change the vendor box the chart changes

SELECT qryExc_Message_Weekly_1.Date AS Expr1,
Sum(qryExc_Message_Weekly_1.[Cancel Value]) AS [Canx Value]
FROM qryExc_Message_Weekly_1
WHERE (((qryExc_Message_Weekly_1.[Vendor Name])=[Forms]!
[frmExc_Mess_Charts]![txtVendorNum])) OR ((([Forms]!
[frmExc_Mess_Charts]![txtVendorNum]) Is Null))
GROUP BY qryExc_Message_Weekly_1.Date;


Bottom line i need to filter the Dsum or Dlookup box the same as the
chart.

Not too confusing is it?