Calling VBA subroutine from a query?

  • Thread starter Thread starter DavidY
  • Start date Start date
D

DavidY

Can I call a VBA subroutine from within an Access query? I wrote some English
to Metric conversion routines in the Access VBA code and would like to run a
query on the data that will return coverted values. I need to be able to
execute this from outside the database (run the query from another program).
 
Yes. The function must be a Public Function in a standard module. You use a
calculated control to return the results of the query.

ConvertedValue: MyFunction([SomeField])
 
Thank you for the quick reply. Can a calculated control be used in a query?
If not, how would I call it from an external program? I would like to be able
to retrieve the converted values in a recordset.

Klatuu said:
Yes. The function must be a Public Function in a standard module. You use a
calculated control to return the results of the query.

ConvertedValue: MyFunction([SomeField])

--
Dave Hargis, Microsoft Access MVP


DavidY said:
Can I call a VBA subroutine from within an Access query? I wrote some English
to Metric conversion routines in the Access VBA code and would like to run a
query on the data that will return coverted values. I need to be able to
execute this from outside the database (run the query from another program).
Click to expand...
Click to expand...
 
Sorry, I meant calculated field. Yes, they can be used in queries. If you
can call the query from an external program, it will work.
--
Dave Hargis, Microsoft Access MVP


DavidY said:
Thank you for the quick reply. Can a calculated control be used in a query?
If not, how would I call it from an external program? I would like to be able
to retrieve the converted values in a recordset.

Klatuu said:
Yes. The function must be a Public Function in a standard module. You use a
calculated control to return the results of the query.

ConvertedValue: MyFunction([SomeField])

--
Dave Hargis, Microsoft Access MVP


DavidY said:
Can I call a VBA subroutine from within an Access query? I wrote some English
to Metric conversion routines in the Access VBA code and would like to run a
query on the data that will return coverted values. I need to be able to
execute this from outside the database (run the query from another program).
Click to expand...
Click to expand...
Click to expand...
 
Ok, thanks!

Klatuu said:
Sorry, I meant calculated field. Yes, they can be used in queries. If you
can call the query from an external program, it will work.
--
Dave Hargis, Microsoft Access MVP


DavidY said:
Thank you for the quick reply. Can a calculated control be used in a query?
If not, how would I call it from an external program? I would like to be able
to retrieve the converted values in a recordset.

Klatuu said:
Yes. The function must be a Public Function in a standard module. You use a
calculated control to return the results of the query.

ConvertedValue: MyFunction([SomeField])

--
Dave Hargis, Microsoft Access MVP


:

Can I call a VBA subroutine from within an Access query? I wrote some English
to Metric conversion routines in the Access VBA code and would like to run a
query on the data that will return coverted values. I need to be able to
execute this from outside the database (run the query from another program).
Click to expand...
Click to expand...
Click to expand...
 
I'm trying to calculate elapsed time in a query in my Access database. I
copied the Microsoft code in a module and tried to call from query.

HoursAndMinutes: ([Timeout]-[Timein])

It wants to treat this as a variable and never calls the function.

Klatuu said:
Yes. The function must be a Public Function in a standard module. You use a
calculated control to return the results of the query.

ConvertedValue: MyFunction([SomeField])

--
Dave Hargis, Microsoft Access MVP


DavidY said:
Can I call a VBA subroutine from within an Access query? I wrote some English
to Metric conversion routines in the Access VBA code and would like to run a
query on the data that will return coverted values. I need to be able to
execute this from outside the database (run the query from another program).
Click to expand...
Click to expand...
 
It doesn't look like you have the name of your function there. Shouldn't it
be
HoursAndMinutes: MyFunction([Timeout]-[Timein]) ?
Where "HoursAndMinutes" is the name of your calculated field, "MyFunction"
is the name of your Function (Note it has to be a function, not a subroutine
as you mentioned in your original post), and "[TimeOut]-[Timein]" the
argument you are passing to your functions.


Michelle said:
I'm trying to calculate elapsed time in a query in my Access database. I
copied the Microsoft code in a module and tried to call from query.

HoursAndMinutes: ([Timeout]-[Timein])

It wants to treat this as a variable and never calls the function.

Klatuu said:
Yes. The function must be a Public Function in a standard module. You
use a
calculated control to return the results of the query.

ConvertedValue: MyFunction([SomeField])

--
Dave Hargis, Microsoft Access MVP


DavidY said:
Can I call a VBA subroutine from within an Access query? I wrote some
English
to Metric conversion routines in the Access VBA code and would like to
run a
query on the data that will return coverted values. I need to be able
to
execute this from outside the database (run the query from another
program).
Click to expand...
Click to expand...
Click to expand...
 
Thank guys,

I can see that through some error on my part I inadvertently dropped the
function name. I will make a note of this format for future use.

Skip said:
It doesn't look like you have the name of your function there. Shouldn't it
be
HoursAndMinutes: MyFunction([Timeout]-[Timein]) ?
Where "HoursAndMinutes" is the name of your calculated field, "MyFunction"
is the name of your Function (Note it has to be a function, not a subroutine
as you mentioned in your original post), and "[TimeOut]-[Timein]" the
argument you are passing to your functions.


Michelle said:
I'm trying to calculate elapsed time in a query in my Access database. I
copied the Microsoft code in a module and tried to call from query.

HoursAndMinutes: ([Timeout]-[Timein])

It wants to treat this as a variable and never calls the function.

Klatuu said:
Yes. The function must be a Public Function in a standard module. You
use a
calculated control to return the results of the query.

ConvertedValue: MyFunction([SomeField])

--
Dave Hargis, Microsoft Access MVP


:

Can I call a VBA subroutine from within an Access query? I wrote some
English
to Metric conversion routines in the Access VBA code and would like to
run a
query on the data that will return coverted values. I need to be able
to
execute this from outside the database (run the query from another
program).
Click to expand...
Click to expand...
Click to expand...
 
Back
Top