sql in textbox?

  • Thread starter Thread starter JKlein
  • Start date Start date
J

JKlein

How can i show the result of this sql in a text box?

SELECT Sum(tbl_TempCalendarData.totalTime) AS SumOftotalTime
FROM tbl_TempCalendarData
HAVING (((tbl_TempCalendarData.meetingdate)>=fformdate() And
(tbl_TempCalendarData.meetingdate)<DateAdd("m","1",fformdate())));
 
Try DSum().

Something like this:

=DSum("totalTime", "tblTempCaldndarData", "(meedingdate >= " &
Format(fformdate(), "\#mm\/dd\/yyyy\#") & ") AND (meedingdate < " &
Format(DateAdd("m", 1, fformdate()), "\#mm\/dd\/yyyy\#") & ")")
 
Jeff,

First you should fix it. "1" should be 1 and HAVING should be WHERE.
Don't know what fformdate refers to.

1. You could save it as a Query, and then include the query in the
Record Source of the form or report that the textbox is on, in which
case you can bind the textbox directly to the SumOftotalTime field.

2. You could use something similar to this in the textbox's Control
Source...
=DSum("[totalTime]","tbl_TempCalendarData","[meetingdate] Between
fformdate() And DateAdd("m",1,fformdate())")

3. You could use VBA code on an applicable event, for example, along
these lines...

Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT Sum(totalTime) ..."
Set rst = CurrentDb.OpenRecordset(strSQL)
Me.YourTextbox = rst!SumOftotalTime
rst.Close
Set rst = Nothing

4. You could make a form based on the tbl_TempCalendarData table, whack
it onto the existing form as a subform, set its Detail section's Visible
property to No, and in the Footer put a textbox with its Control Source
set to something like this...
=Abs(Sum([totalTime]*([meetingdate] Between fformdate() And
DateAdd("m",1,fformdate())))

Hope that might give some applicable ideas...
 
Back
Top