How to call Function from Query?

[fredalina]

I have a user-definied function in a module that contains a Do Until Loop that works backward to determine the start date. I also have a query that uses Between [StartDate] and Date(). Specifically this queries back 10 business days, and the function looks backward to eliminate company holidays and weekends.

How do I call the function from within the criteria of the query to return the value of StartDate from the function?

Thank you!

 

[KenHigg]

What do you pass the function and what exactly does it do?

 

[fredalina]

The query is intended to return backorder information for the previous 10 business days.

The function is intended to determine what the first business day in the previous 10 is (there are no backorders on non-business days). The function uses two counters, one (datecounter) is by date, and one (counter) is an integer. It uses a Do Loop to iterate the datecounter backward day by day from the present day, looking at that day to determine if it is a weekend or a holiday. If it is not a weekend or holiday, it increases the counter by one, until counter reaches 10, 10 business days. Before this it must determine the last business day as well, by looking backward from today to determine if today (and yesterday and backward if needed) is a business day through a similar loop.

I should probably revise it to send both datecounter and enddt back to the query as the between criteria, but if today isn't a business day it won't matter since there won't be any backorders on non-business days.

Public Sub start(datecounter As Date)
Dim enddt As Date
Dim start As Date
Dim counter As Integer
Dim datecounter As Date
Dim strFilter

'Determine enddt as previous Friday
Select Case Weekday(Date)
Case 1
'Today is a Sunday; enddt is Date()-2
enddt = Now() - 2
Case 2
'Today is a Monday; enddt is Date()-3
enddt = Date - 3
Case 3
'Today is a Tuesday; enddt is Date()-4
enddt = Date - 4
Case 4
'Today is a Wednesday; enddt is Date()-5
enddt = Date - 5
Case 5
'Today is a Thursday; enddt is Date()-6
enddt = Date - 6
Case 6
'Today is a Friday; enddt is previous Friday, or Date()-7
enddt = Date - 7
Case 7
'Today is a Saturday; enddt is previous day, or Date()-1
End Select

strFilter = "[Date]= '" & enddt & "'"

'Determine if end date is a holiday
Do Until IsNull(DLookup("[Holiday]", "Holidays", strFilter)) = True
enddt = enddt - 1
strFilter = "[Date]='" & enddt & "'"
Loop

'Determine start date
datecounter = enddt - 1
counter = 1
Do Until counter = 10
Select Case Weekday(datecounter)
Case 1 'Sunday
'Do nothing, it is NOT a workday
datecounter = datecounter - 1
Case 7 'Saturday
'Do nothing, it is NOT a workday
datecounter = datecounter - 1
Case Else 'Any weekday
'Check to see if datecounter is a holiday. If not, increase the counter. If so, don't increase counter.
strFilter = "[Date]='" & datecounter & "'"
If IsNull(DLookup("[Holiday]", "Holidays", strFilter)) = True Then
' This is NOT a holiday, so increase counter!
counter = counter + 1
datecounter = datecounter - 1
Else
'This IS a holiday, so do not increase counter!
datecounter = datecounter - 1
End If
End Select
Loop

End Sub

Edited to add sorry the formatting didn't come through. How does one encapsulate code for this forum?

 

[KenHigg]

I'm going to skip trying to figure out what it is your doing and try to answer your original question: Generally speaking you send a function a value and it returns some value. From your post it looks like you're passing in 'datecounter' as a date. The question is what do you want back? Whatever is the new value for 'datecounter' at the bottom of you routine?

If so...

For starters you do a function as follows:

Public Function myFunctionName() As String
...
End Function

In this example the function is going to return a string. I'm guessing you are going to want to return a date, so you would do:

Public Function myFunctionName() As Date
...
End Function

Finally, to get the modified 'datecounter' value back when you call the function you can simply do something like:

Public function start(datecounter As Date) as date
'
'Put all of you other code here
'
'At the bottom put this line of code
start = datecounter
End Sub

Hope all of this makes sense

 

[fredalina]

Thank you; that's great!

But from the query, how do I call the function?

 

[KenHigg]

In a new column put something like the following in the top row:

MyNewDate: start([myDateFieldName])

Of course your function needs to be in a stand regular stand alone code module and not in a form code module.

 

[fredalina]

Thank you so much once again!

 

[KenHigg]

Glad to help -

It helps that you can read my jibberish:

.. in a stand regular stand alone code module ...