Can I use a VB function in a SQL statement?

Question

Is it possible to use a VB function in a SQL statement?  The first screen shot demonstrates that my function works OK in the VB environment.  The second shows the error I get when I put it in a SQL statement.  This caused me some grief because the error doesn’t display when the SQL is executed inside a VB script.

If I can’t achieve my goal this way, does someone have a suggestion as to how I can select rows where [score_date] is within a week where we know the start date of that week?

Answer 1

Here's what I get.

Answer 2

Try this:

Select *, fncWeekStartDate(score_date) AS WeekStart

FROM tblScores

WHERE WeekStart = !0/24/22;

Answer 3

Here's my version of Access.

Answer 4

Function fncWeekStartDate(dtDate As Date) As Date

Dim ls_temp As String

On Error GoTo Err_WeekStartDate

fncWeekStartDate = DatePart("ww", dtDate, vbMonday)

Dim intDelta

intDelta = (DatePart("w", dtDate, vbMonday) - 1) * -1

fncWeekStartDate = DateAdd("d", intDelta, dtDate)

Exit_WeekStartDate:

Exit Function 

Err_WeekStartDate:

ls\_temp = "fncWeekStartDate: " & Err.Description 

MsgBox ls\_temp 

Resume Exit\_WeekStartDate 

End Function

Answer 5

Should be ok, if it's a Public function. Could you post (at least the first few lines of) the VBA code? What version of Access are you running?