How to call a function from aMake table query (1 Viewer)

[jnh]

I am pretty new at this and have LOTS to learn....

Have a Make table query and don't seem to have correct coding that calls a function and places funtion result into specified table field


VBA coding (simplified):

private function Create_Week_Num () Week_Num as String
do something
end function

The VBA coding is in a lib named: Work1 Module1


Query field:
Week_Num:[Create_Week_Num])


Help is greatly appreciated...jnh

 

[spikepl]

Public function Create_Week_Num () Week_Num as String

 

[jnh]

Need a little more help:
How do I code the Make table query to call that function so that WeeK_Num gets
placed into correct field in given table. Thanks...jnh

 

[nanscombe]

You almost had it:

Week_Num:Create_Week_Num()

 

[jnh]

Some progress (hope ?) BUT now when runnung the Make table query it gives me the
error:
undefined function '[Create_Week_Num]' in expression


The Actual function is stored in _Work1 - Weekx (Code) as

Private Function Create_Week_Num(WeekNum As Integer)

What am I doing wrong ?

Thanks...jnh

 

[jnh]

CORRECTION
Correct VBA storage area is named:
Work1 - Weekx (Code) (NO leading Work1 _), sorry

 

[vbaInet]

But seriously, you shouldn't be saving calculated values. Calculate it on-the-fly in a SELECT query.

 

[jnh]

This is a MAKE table query and the field stored will be used as a selection criteria in several reports and select querries and/or input to lookup tables down the road.

This function is only done once a year.

Believe I am using the correct approach but feel free to correct me if you think I am wrong.

My problem is tying the query field to the function...jnh

 

[vbaInet]

Yes I know it's a Make-Table query and you are making the table from a source or a bunch of sources. So what's wrong with using a SELECT query and including that function there?

 

[jnh]

It truly is not a calculation but rather an assigned value (done yearly at beginning of season) which is then used as input to various Report generation, criteria for some Select Queries and input to a Lookup table.

Believe it makes little sense to re-create/repeat the same 'Assignment/Calculation' in app 100 reports/queries down the road and where the lookup table would present additional problems.


All I am trying to get info as to proper coding for a Make table query to call a function (one control) and have that function return/place result into specified control.

While I am very new to Access I am not exactly a beginner in programming (having written 1000s of programs starting with Univac 1, IBM 350 and 1401) but Access is a horse of a different color...jnh

 

[vbaInet]

I see. We always discourage people using the Make Table unecessarily but since you're a seasoned developer and you know what you're doing I will explain.

Your function expects a parameter as input and in your call to the function you're not passing any parameter. The function needs to be written like this:

Create_Week_Num(10)

... where 10 is a parameter, for example. You can also put a field name as parameter (if relevant).

 

[nanscombe]

Private Function Create_Week_Num(WeekNum As Integer)

What am I doing wrong ?

Thanks...jnh

It should also be

Public Function Create_Week_Num(WeekNum As Integer)

Otherwise your query won't be able to see it.


Just a thought. It also needs to be in a module rather than the code area of a Form.

 

[vbaInet]

It should also be

Public Function Create_Week_Num(WeekNum As Integer)

Otherwise your query won't be able to see it.

It doesn't really matter Nigel. In VBA Functions/Subs are Public by default. So the signature without the word "Public" is implied as being Public by default.

 

[gemma-the-husky]

i assume your weeknum function is probably assigning a week number to a transaction, for a calendar that is not following normally access weeknumbers. (ie so week 1 is not the first week in January. Relating calendars/week numbers is a bit subjective anyway. eg, some companies may determine that a date like 30 April 2012 is in either year 2011,2012,2013 - depending how they manage their fiscal calendar.

So, I can see the use of this, and I think in this circumstance it probably does make sense, as otherwise you will need to manipulate a date into a corresponding period number every time you need to use it.

- note that you possibly may need to store a year number as well, but maybe you have separate systems for each year.


there are also some practical points

obviously, in some circumstances a function may be safer - eg, if you ever decide to change your calendar, then you may need to renumber all your stored values. If instead, you determine the week nunber at runtime, then you don't need any renumbering - the function determines the week number.

and, there is always the possibility that you may leave the company, and the approach you have taken gets "lost" as time passes.

 

[nanscombe]

The Actual function is stored in _Work1 - Weekx (Code) as

Private Function Create_Week_Num(WeekNum As Integer)

It doesn't really matter Nigel. In VBA Functions/Subs are Public by default. So the signature without the word "Public" is implied as being Public by default.

But not if you explicitly use the keyword Private.

It also won't be found if it were in a Form rather than a Module, which is why I asked.

 

[vbaInet]

But not if you explicitly use the keyword Private which is why I wondered if it were in a form.

Ah, got ya Nige. Didn't even notice that.