Creating a function for a query

[TheNoocH]

is there a way to create a Public Function and use it in a query?
In my query i have the following as one of my columns:
JanSales: Sum(IIf([FiscalYYMM]='0401',[sales],0))

I tried to do the following and got external name not defined:

Public Function JanCYRSales() As Double
JanCYRSales = Sum(IIf([FiscalYYMM] = "0401", [sales], 0))
End Function

Any help would be great b/c i use this calculation in alot of queries and when the new year changes i have to go modify every query it's in rather than just in the main function. Any help or any better suggestion for ways to accomplish this would be great.
Thanks in advance.

 

[mdmilner]

Yes and that's exactly how you do it, but, it's a different syntax.

If [FiscalYYMM] = "0401" Then
   JanCYRSales = [sales]
Else
  JanCYRSales = 0;
End If

Mike

 

[Doktor]

Yes! (sort of...)
Example:
Public Function JanCYRSales(strFiscal as String, dblSales as Double) As Double
JanCYRSales = IIf(strFiscal = "0401", dblSales, 0)
End Function

SQL
SELECT Sum(JanCYRSales([FiscalYYMM],[sales])) AS JanSales
FROM tblTable

The SQL calls the public function JanCYRSales for every row and then groups the results. You cannot, as far as I know, send an entire column to a function.

--Doktor

 

[TheNoocH]

Mike,
How do i then use it in the query...i tried putting =JANCYRSales() and it changed it to Expr1: JanCYRSales()
but when i run it it's still getting External Name not defined on [FiscalYYMM]...any suggestions?

 

[Doktor]

In the top row of the query builder type:
JanSales: Sum(JanCYRSales([FiscalYYMM],[sales]))

Make sure you click the Group button.

--Doktor

 

[Doktor]

The fields you want to pass in have to exist in the tables.

 

[mdmilner]

Doktor is right, but...the Function will need an addition.
You have to tell it how to receive the fields.

Public Function JanCYRSales(FiscalYYMM As String, _
                                         sales As String) As Double 

If FiscalYYMM = "0401" Then 
   JanCYRSales = sales 
Else 
  JanCYRSales = 0; 
End If 

End Function

Please notice, I removed the [] brackets around the field names.
There is actually no requirement to use the fieldnames from your table. I could have used A & B as the field names in the Public Function declaration.

Mike

 

[mdmilner]

Just because I just did this yesterday, I'll throw out an example for something completely different. I wanted a way to build a limited 'scheduling' capability into a database. Specifically, I wanted my database to keep track of the last time I'd run a given report, AND, to remind me if I should have run it already.

The first segment is my SQL for my query. It compares dates in a single field with the current date...then looks at a field to determine if this is a weekly, monthly, quarterly or 'closeout' (closeout means custom schedule in a single field table with a list of dates in it)

It uses the custom function DatePuller to return the values. If everything is current, it returns a 0 record query. If anything is pending, it returns a list. This was just my first draft - probably make it niftier later on.

SELECT tblTask.dteStamp, tblTask.ScheduleType, DatePuller([dteStamp],[ScheduleType]) AS Pending
FROM tblTask
WHERE (((DatePuller([dteStamp],[ScheduleType]))>DateSerial(Year(Date()),1,1) And (DatePuller([dteStamp],[ScheduleType]))<=Date()));


Public Function DatePuller(Optional ByVal dtMine As Date, _
                           Optional ByVal strType As String) As Date
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL, strVal As String
Dim intVal As String

Set dbs = CurrentDb
'dtMine = #12/15/2004#          ' This is a test item comment out to not use

If Not IsNull(dtMine) Then
 Select Case strType
  Case "Closeout"
     strSQL = "SELECT TOP 1 * FROM tblCloseout WHERE sched_closeout >= #" _
               & dtMine & "# ORDER BY sched_closeout"
    Set rs = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
    DatePuller = rs.Fields(0).Value
  Case "Monthly"
     intVal = Month(dtMine)
     DatePuller = DateSerial(Year(dtMine), (intVal + 1), 1) ' 1st Monday of Month
     Select Case Weekday(DatePuller)
       Case 2: DatePuller = DatePuller
       Case Is > 2: DatePuller = DatePuller + Weekday(DatePuller) + 1
       Case 1: DatePuller = DatePuller + 1
       Case Else
     End Select
  Case "Quarterly"
     intVal = Month(dtMine)
     intVal = Int((intVal + 2) / 3) ' Identifies Current Quarter
     DatePuller = DateSerial(Year(dtMine), ((intVal) * 3 + 1), 1) ' 1st Monday of Quarter
  Case "Weekly"
     Select Case Weekday(dtMine)
       Case 2: DatePuller = dtMine + 7
       Case Is > 2: DatePuller = dtMine - (7 - Weekday(dtMine) + 1) + 7
       Case Is = 1: DatePuller = dtMine - (Weekday(dtMine) + 1) + 7
       Case Else
     End Select
  Case Else:
 End Select
Else
 DatePuller = #1/1/2004#
End If

Set rs = Nothing
Set dbs = Nothing
End Function

 

[TheNoocH]

Mike and Doktor,

Thanks for all your help...it worked exactly as I wanted...I did take it a step further by doing the following:

Public Const CYR As String = "04"

Public Function JanCYRSales(FiscalYYMM As String, Sales As String) As Double
If FiscalYYMM = CYR & "01" Then
   JanCYRSales = Sales
Else
   JanCYRSales = 0
End If
End Function

Public Function FebCYRSales(FiscalYYMM As String, Sales As String) As Double
If FiscalYYMM = CYR & "02" Then
   FebCYRSales = Sales
Else
   FebCYRSales = 0
End If
End Function

.
.
.
etc.

Now I'll only have to change the constant CYR line in one place...

Once again thanks for all your help....