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

drop table PiecesMinifigsSets;
create table PiecesMinifigsSets (item_description text,qty double,PaymentDate datetime);
insert into PiecesMinifigsSets values('black title',0.11,#2022-10-26#);
insert into PiecesMinifigsSets values('black title',0.11,#2022-10-21#);
select *,PaymentDate-weekday(PaymentDate,2),dateadd('d',-1*DatePart('w',PaymentDate,2)+1,PaymentDate) from PiecesMinifigsSets;

Expr1000	Expr1001	item_description	qty	PaymentDate
2022/10/23	2022/10/24	black title	.11	2022/10/26
2022/10/16	2022/10/17	black title	.11	2022/10/21

select *,PaymentDate-weekday(PaymentDate,3) from PiecesMinifigsSets where (PaymentDate-weekday(PaymentDate,3))=#2022-10-24#;

Expr1000	item_description	qty	PaymentDate
2022/10/24	black title	.11	2022/10/26

select *,dateadd('d',-1*DatePart('w',PaymentDate,2)+1,PaymentDate) from PiecesMinifigsSets where (dateadd('d',-1*DatePart('w',PaymentDate,2)+1,PaymentDate))=#2022-10-24#;

Expr1000	item_description	qty	PaymentDate
2022/10/24	black title	.11	2022/10/26

Answer 2

I don't understand the relevance of this to my question.

Answer 3

drop table PiecesMinifigsSets;
create table PiecesMinifigsSets (item_description text,qty double,PaymentDate datetime);
insert into PiecesMinifigsSets values('black title',0.11,#2022-10-26#);
insert into PiecesMinifigsSets values('black title',0.11,#2022-10-21#);
select *,PaymentDate-weekday(PaymentDate,2) from PiecesMinifigsSets;

Expr1000	item_description	qty	PaymentDate
2022/10/23	black title	.11	2022/10/26
2022/10/16	black title	.11	2022/10/21

select *,PaymentDate-weekday(PaymentDate,3) from PiecesMinifigsSets where (PaymentDate-weekday(PaymentDate,3))=#2022-10-24#;

Expr1000	item_description	qty	PaymentDate
2022/10/24	black title	.11	2022/10/26

Answer 4

The reason for that is that the column heading is used in the WHERE clause, not the expression which returns that column. Also the date is not delimited with # characters, which is necessary for a date literal. Otherwise the value would be interpreted as an arithmetic expression.

You might like to take a look at DatabaseBasics.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have trouble downloading a specific file, clicking on the 'Download' button at the top of the page while no files are selected should download a zip file of all files in the folder, from which you should then be able to unpack the relevant file.

In this demo file towards the end of the section on 'retrieving data from the database' is a form which illustrates three ways of returning the same data in a report whose RecordSource query calls a the following WeekStart function

Public Function WeekStart(intStartDay As Integer, Optional varDate As Variant)

    ' Returns 'week starting' date for any date

    ' Arguments:

    ' 1. intStartDay - weekday on which week starts, 1-7 (Sun - Sat)

    ' 2. vardate - optional date value for which week starting

    '   date to be returned.  Defaults to current date

    If IsMissing(varDate) Then varDate = VBA.Date

    If Not IsNull(varDate) Then

        WeekStart = DateValue(varDate) - Weekday(varDate, intStartDay) + 1

    End If

End Function

The function can be used to restrict the result table to a specific week, e.g.

SELECT *

FROM CustomerPayments

WHERE WeekStart(1,PaymentDate)=#2011-10-16#;