VBA Module Not Being Passed to Query

[dataempress]

Hi All,

I've just learned how to create some very simple custom functions in Access VBA. I'm using Access 2007. My dilemma is our program has three possible Entry and Exit dates, set up that way in the database that was provided. What I'm trying to do is pull the number of students that were enrolled at any given time.

I created a form with two text boxes, one so the user can enter a begin date and another so they can enter an end date. Theoretically, the query should pull all students who are enrolled within that date range. The trick is I'm trying to factor in the multiple entry and exit dates. I've been working on this for about a week straight and it's making me insane.

The problem is if a student reenrolls, their new exit date becomes null because they're still in the program, just as they had been when they enrolled the first time. I set up a calculated field in my query that would call the module, and if it showed a student had reenrolled and their second exit date was blank, the module is supposed to put the end date that the user entered so the query would pick up that student as being enrolled.

The problem is in my module, I have the user entered dates as parameters, and I keep getting an Invalid Use of Null error that I can't seem to work around, even though I can see the query is pulling the right people. The parameters are in the form, and I'm trying to pass them from the form to the module, and finally call the module from the query.

I have no doubt I'm doing many things wrong, but I'm just starting to write my own functions, and if I can get this one to work, it will save me a lot of time. Any help would be much appreciated. Thank you!

[Siddharth Rout]

Can I see the module? If you can also upload a sample database then it would definitely help