How to set query result as a control value

S

SAC

I'd like to set a value of a control to this sql statement:

SELECT Max(tblShows.AssociationRevenue) AS MaxOfAssociationRevenue
FROM tblShows
WHERE (((tblShows.ShowDay1)<[Forms]![frmBookingShows]![txtShowDay1]))
GROUP BY tblShows.Dept, tblShows.AssociationLookup
HAVING (((tblShows.Dept)=[Forms]![frmBookingShows]![txtDept]) AND
((tblShows.AssociationLookup)=[Forms]![frmBookingShows]![cboAssociationLookup]));

It returns one value and I'd like to control to show this value.

Not sure what to do with it.

Thanks.
 
M

Marshall Barton

SAC said:
I'd like to set a value of a control to this sql statement:

SELECT Max(tblShows.AssociationRevenue) AS MaxOfAssociationRevenue
FROM tblShows
WHERE (((tblShows.ShowDay1)<[Forms]![frmBookingShows]![txtShowDay1]))
GROUP BY tblShows.Dept, tblShows.AssociationLookup
HAVING (((tblShows.Dept)=[Forms]![frmBookingShows]![txtDept]) AND
((tblShows.AssociationLookup)=[Forms]![frmBookingShows]![cboAssociationLookup]));

It returns one value and I'd like to control to show this value.
Click to expand...


Translate that to a DMax function call. I think it woul be
like:

=DMax("AssociationRevenue", "tblShows", "ShowDay1 < " &
txtShowDay1 & " And Dept = " & txtDept & " And
AssociationLookup = " & cboAssociationLookup)
 
S

SAC

OH, thanks! Got it! Used to using Dlookup, but forgot about DMax.

Marshall Barton said:
SAC said:
I'd like to set a value of a control to this sql statement:

SELECT Max(tblShows.AssociationRevenue) AS MaxOfAssociationRevenue
FROM tblShows
WHERE (((tblShows.ShowDay1)<[Forms]![frmBookingShows]![txtShowDay1]))
GROUP BY tblShows.Dept, tblShows.AssociationLookup
HAVING (((tblShows.Dept)=[Forms]![frmBookingShows]![txtDept]) AND
((tblShows.AssociationLookup)=[Forms]![frmBookingShows]![cboAssociationLookup]));

It returns one value and I'd like to control to show this value.
Click to expand...


Translate that to a DMax function call. I think it woul be
like:

=DMax("AssociationRevenue", "tblShows", "ShowDay1 < " &
txtShowDay1 & " And Dept = " & txtDept & " And
AssociationLookup = " & cboAssociationLookup)
Click to expand...
 
Top