Call Function Variable in Query

[matthewnsarah07]

Hi

How do I call a variable in a public function into a query.

I have an append query and need a variable within a function to append to a field.

The function ImmediateCount() has Mill, Low & Total as variable - I want the variable Total to be called

Thanks for your help

 

[vbaInet]

You simply put ImmediateCall() in a new column. But you must include the brackets unless it will not be recognised as a function.

The function must be declaredin a Module as Public. If the function requires arguments, remember to include them.

 

[matthewnsarah07]

Thanks for your help

I have added it like that and it is public in a Module - it now states ambiguous name error

 

[boblarson]

You can't reference a variable by itself.

1. A function can return a value. So, what is the code for ImmediateCount?

2. You don't name the module the same as the function.

3. If you want to return a VARIABLE'S value you would need to have it declared as public in the General Declarations section of the standard module and then you would need a separate function to return that value.

So, if you declared the variable Total in the general declarations section:

Option Compare Database
Option Explicit

Public Total As Currency


you would then need a function to return the value

Function GetTotal() As Currency
   GetTotal = Total
End Function

 

[matthewnsarah07]

Public Function CumbriaImmediate()
Dim MILLNESS As Integer
Dim LOWHURST As Integer
Dim Total As Integer
MILLNESS = DLookup("[Immediate]", "tblIncidents", "[Sector]='MILLNESS'")
LOWHURST = DLookup("[Immediate]", "tblIncidents", "[Sector]='LOWHURST'")
Total = MILLNESS + LOWHURST
End Function

Above is the function code - I have changed the function name, How do I now use Total in a query?

Thanks for your help so far

 

[vbaInet]

This is going to be slow. Include the tblIncidents table in the query and join it via the appropriate ID field. Then perform the addition in an alias field.

 

[boblarson]

You need to change the function to this:

Public Function CumbriaImmediate() [B][COLOR=red]As Integer
[/COLOR][/B]Dim MILLNESS As Integer
Dim LOWHURST As Integer
Dim Total As Integer
   MILLNESS = DLookup("[Immediate]", "tblIncidents", "[Sector]='MILLNESS'")
   LOWHURST = DLookup("[Immediate]", "tblIncidents", "[Sector]='LOWHURST'")
Total = MILLNESS + LOWHURST
 
CumbriaImmediate = Total
End Function

And then you can simply use

CumbriaImmediate()

in your query

Also, I would suggest possibly using LONG instead of INTEGER in the function just in case - there have been times where it is thought that you'll never have a number that high but then eventually, over time it becomes possible. That way you will be safe for an extremely long time.

 

[boblarson]

This is going to be slow. Include the tblIncidents table in the query and join it via the appropriate ID field. Then perform the addition in an alias field.

Good point - 2 DLookups happening for every record would be slow, slow, slow.

 

[matthewnsarah07]

Excellent thanks for your help both
The DLookups should be fine as there will only ever be 7 records in the table

 

[vbaInet]

Future requirements may mean the records returned could be in the hundreds so you're better off getting a much more effecient solution for the now than later. You just never know.

Merry xmas!