MS Access user defined function in select sql

Question

I am new to access I would like to use a UDF in my SELECT sql statement like this:

select birthdate,GoMonth(birthdate,12) as yearold from data

The above statement will give a field name yearold 12 months after birthdate

function GoMonth(pDate,num) as datetime
    GoMonth=DateAdd("m",num,pdate)
end function

I don't know where to put or write the function
I am working with other databases and don't want to change my sql statements for each of them.

are you using Access as a frontend aswell or just as a database? — hawbsl
– hawbsl, Commented Aug 3, 2011 at 10:24
Why do you need a user-defined function? In your example, you can just use the DateAdd() function directly. Or have you just used a bad example that implies things that don't apply to your real situation? — David-W-Fenton
– David-W-Fenton, Commented Aug 3, 2011 at 20:51

Jacob · Accepted Answer · 2011-08-03 11:40:43Z

3

If you use Access as a frontend, put it in a module and make it a public function(). If you have another frontend, then you can't use user-defined functions with a MS Access database, as the functions are interpreted by Access and not the JET driver.

answered Aug 3, 2011 at 11:40

Jacob

43.6k6 gold badges81 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MS Access user defined function in select sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related