Expression builder -> customised function?

  • Thread starter Thread starter Remco
  • Start date Start date
R

Remco

In Excel you can use VBA to program your own worksheet function. Can I
create a similar function within Access for use within the expression
builder in a query?

TIA
Remco
 
The short answer is yes - create a Public function in a standard module (one
that's in the modules list in the database window), and you can call that
function from anywhere in your application - including from queries. What
is it you're trying to do?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
For uploading in an AS400 database I need to convert my Date format
dates into a numeric date CCYYMMDD format. Currently I do this by
creating a large multiple nested function. I would like to automate
this by programming a function that has a date (Date format) as input
and a date (CCYYMMDD format) as output.
 
If you're trying to convert a date/time field from an Access table to
CCYYMMDD, then this should do it:

Year([MyDate]) & Format(Month([MyDate]), "00") & Format(Day([MyDate]), "00")

I suppose you could put that in a user function. What am I missing? What
do you mean by "large multiple nested function?"

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
Thanks John!

Your solutions looks pretty straight forward - and it works. I did not
know to look for the format function. Untill today I used to use this
formula:

CCYYMMDD: Year([Effective Date]) & IIf(Month([Effective Date])<10;"0"
& Month([Effective Date]);Month([Effective Date])) &
IIf(Day([Effective Date])<10;"0" & Day([Effective
Date]);Day([Effective Date]))

Your solution makes me feel lost in Access.

Best regards,
Remco
 
Back
Top