VBA Sql String

[Krystoff]

Hello all, I have a function that I am trying to write to sum a field in a table and pass that number to a form (Call the function from the form)

My function looks like this:

Function GetDays(StartDate, EndDate As Date)

Dim strSQL As String

strSQL = "SELECT SUM(BR_DT.[#ofWorkDays]) AS [Days]FROM BR_DT Where (((BR_DT.[BR Date]) Between #" & [StartDate] & "# And #" & [EndDate] & "#))"

End Function

StartDate and EndDate I pass into the function from the form.

I placed a text box on the form and this is what I used.
=GetDays(StartDate, EndDate)

All that comes back is blank.

What am I doing wrong?

Any help is appreciated!

Chris

 

[Rick39]

Try

strSQL = "SELECT SUM(BR_DT.[#ofWorkDays]) AS [Days]FROM BR_DT Where (((BR_DT.[BR Date]) Between #" & Me![StartDate] & "# And #" & Me![EndDate] & "#))"

 

[Krystoff]

Rick,

I actually made this in a module so it gives me a Invalid use of Me keyword error.

Should I move this to the udnerlying code of the form?

 

[Rick39]

Yes...my bad

 

[Krystoff]

How do I call it from the underlying form? I get a #Name error in my text box and if I try to run it in the debug window (With ?GetDays(04/01/2003,04/30/2003) ) then it gives me a Sub or Function not defined error.

I used Private Sub GetDays(Startdt, Enddt as Date)

And I removed the name of Getdays from the module.

Chris

 

[Rick39]

Try something like:

Dim db as database
sim rs as recordset
Dim strSQL As String

strSQL = "SELECT SUM(BR_DT.[#ofWorkDays]) AS [Days]FROM BR_DT Where (((BR_DT.[BR Date]) Between #" & [StartDate] & "# And #" & [EndDate] & "#))"

set db = CurrentDb
Set rs = rs.openrecordset(strSQL)
rs.movefirst
Me!YourTextField = rs!Days
rs.close