How do I use this Module in a query??? 3

[ShannonSkipper]

SkipVought was kind enough to write the following code for me:

Function SendSubscription(StartMonth As Integer, Frequency As String) As Boolean
  
   Select Case Frequency
      Case "Monthly"
         f = 1
      Case "Quarterly"
         f = 3
      Case "Semi-Annually"
         f = 6
      Case "Annually"
         f = 12
   End Select
   
   If StartMonth Mod f = Month(Date) Mod f Then
      SendSubscription = True
   Else
      SendSubscription = False
   End If
End Function

(I am trying to get a query to tell me who is up to recieve a subscription this month...)

So, I pasted the code in a module named SendSubscription. Then I build a query that looks like the following in SQL view:

SELECT Demographics.DemographicsID, Demographics.FirstName, Demographics.LastName, Action.Date, Action.Frequency, Month([Date]) AS StartMonth
FROM Demographics INNER JOIN [Action] ON Demographics.DemographicsID = Action.[Foreign Key]
WHERE SendSubscription([StartMonth],[Frequency])=True;

When I try to run the query I get the following error: "undefined function 'SendSubscription' in expression".

What am I doing wrong? I'm afraid I am very new at this... Thanks in advance for the help!!!

 

[ALEKSJAY]

I'm not sure if the default option for the functions is to be Public or Private, so you might try making the function to be Public explicitly by adding the word "PUBLIC" (no quotes) before the word "Function" in the first line

Public Function SendSubscription(StartMonth As Integer, Frequency As String) As Boolean

 

[willir]

I think this is a "scope" issue. Make sure the Function is in a module, not a form.

From the database window, "Isert" -> "Module" and paste your code here.

By the way, I think there is a tiny improvement to be made...

Function SendSubscription(StartMonth As Integer, Frequency As String) As Boolean
[COLOR=blue]
   Dim F as Interger[/color]
  
   Select Case Frequency
      Case "Monthly"
         f = 1
      Case "Quarterly"
         f = 3
      Case "Semi-Annually"
         f = 6
      Case "Annually"
         f = 12
   End Select
   
   If StartMonth Mod f = Month(Date) Mod f Then
      SendSubscription = True
   Else
      SendSubscription = False
   End If
End Function

I suspect Option Explicit is not set so any undefined variables are automatically defined. Otherwise you would get another error.

Richard

 

[TomCologne]

When I get this error, it's usually because of a typo. Those can sometimes be hard to find.

There may also be a naming issue here:

ACC2000: Same Named Module and Procedure Causes Errors

http://support.microsoft.com/default.aspx?scid=kb;en-us;210098&Product=acc2000

A user-defined function or a subroutine have the same name as a module. These errors occur even if the procedure is not contained in the module with the same name.


Using prefixes might save you some troubleshooting.

TomCologne

 

[ShannonSkipper]

Thank you so much for your help! All three of your sugestions were very usefull. I seem to have made some progress. However, now when I try to run the query that calls up the Module, visual basic debugger pops up a window that says:

"Compile error: Can't find project or library."

Any ideas? "F as Integer" is highlighted in grey and after I click okay, the first line, "Public Function SendSub...", is highlighted in yellow.

Thanks again for all your help,
Shannon

 

[TomCologne]

Pleasure, Shannon!

Problem is probably just the DAO 3.6 reference that needs to be set. In VBA window, Tools>References, check "Microsoft DAO 3.6 Object Library"


TomCologne

 

[willir]

Maybe not.

Note that F was never defined elsewhere in the code presented.

I advised...
Dim F As Integer
...but I spelt Integer incorrectly

 

[ShannonSkipper]

I never would have noticed the misspelling - thanks! I'm having one more problem, however. I am getting the same error, "Compile error: Can't find project or library" except now it highlights the word "date" from the following line of code:

If StartMonth Mod F = Month([COLOR=red]Date[/color]) Mod F Then

Thanks soo much for your help!

-Shannon

 

[ALEKSJAY]

Sometimes, when I have that error in some VBA functions, I add "VBA." in front of the name of the function to declare explcitly where it comes from. Try changing "Date" to "VBA.Date" to see if it works.

 

[ShannonSkipper]

After changing "date" to "VBA.Date" when I run the query a window labeled "Enter Parameter Query" pops up like before asking for the StartMonth. When I put a number in and hit enter I get an error window that reads: "data type mismatch in criteria expression".

Thanks for your help,
-Shannon

 

[ShannonSkipper]

P.S. - I think I wrote my query wrong. I put in the expression, "Month([Date]) AS StartMonth" and then refer to that expression in the SQL statement, "WHERE SendSubscription([StartMonth],[Frequency])=True". Why is it asking me to enter a parameter value-isn't it supposed to be getting that value from the expression in the query.

 

[TomCologne]

Take a look at this article:

List of reserved words in Access 2000

http://support.microsoft.com/default.aspx?scid=kb;en-us;209187&Product=acc2000

Your field name "Date" collides with the function Date().

I would also recommend looking into "Naming Conventions",
e.g. here:

http://support.microsoft.com/?kbid=110264

Having distinct object names will not only prevent some errors that occur when different objects have the same name, e.g. calculated fields in forms or reports, but make it easier to identify objects in general.

It's a little effort with great pay off.

TomCologne

 

[ShannonSkipper]

Thanks so much guys - it works! I am thrilled! The only thing that isn't working perfectly is it is prompting me for the parameter value of the StartMonth instead of getting it from the StartMonth Expression in the query that pulls the Month as an Integer from the Date field (now named sDate .

All your help has been invaluable!

Thanks so much,
Shannon

 

[ShannonSkipper]

Resolved the final problem - Thank you all so much for your help!!!