DateSerial Expression in a Query 2

[ShannonSkipper]

I am trying to use the DateSerial expression in a query to find out who is up on a given month to recieve a newsletter. The subscription period frequencies are "Monthly", "Quarterly", "Semi-Annually", and "Annually". The period starts whatever month the customer subscribes to the newsletter. So, if I joined in March and want newsletters Quarterly then the query should pull my record for the months of March, June, September, & December. I am completely overwhelmed by this task! Any help or advice would be greatly appreciated.

My approach is currently to make a separate query for each month of the year. Do I need a different query for each frequency as well? Help?!?!

 

[SkipVought]

Hi,

Use this function

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

in your query as a criteria
[tt]
Where SendSubscription([StartMonth], [Frequency])=True
[/tt]


Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[ShannonSkipper]

I'm afraid i'm not following your instructions correctly. Do I paste the function you wrote under "Field:" in query design view and then use "Where SendSubscription([StartMonth], [Frequency])=True" as the criteria? When I enter the function under "Field:" I get an error that says "The expression you entered contains invalid syntax. You may have entered an operand without an operator." I have no idea what this means...am I doing it wrong?

I am really lost - your help is greatly appreciated!!!

Thanks,
Shannon

 

[ShannonSkipper]

I created a Module with the Function code you wrote and put the following expression "SendSubscription: SendSubscription([«StartMonth»],[«Frequency»])" as the Field: value under query design view. This seems to work as a parameter query but when I put "Where SendSubscription([StartMonth], [Frequency])=True" as the criteria I get the error message ""The expression you entered contains invalid syntax. You may have entered an operand without an operator.

 

[ShannonSkipper]

Am I on the right track to create a new module and paste the code SkipVought wrote?

Do I then build a parameter query? I am a complete noob... ;(

I appreciate any advice!!!

Thanks,
Shannon

 

[SkipVought]

Paste the code in a module.

Use the code in the SQL Where Clause as previously specified.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[ShannonSkipper]

Okay, I pasted the code in a module named SendSubscription. Then I build a select query in SQL view that look like:

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? Thanks in advance for the help!!!

 

[PHV]

The function must be defined as Public in a standard code module not having the same name as the function, not a form module.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244