Create query that calculates the due dates using Dlookup

Question

I am trying to use a module as of of my fields for a query that looks up the frequencies of a training and how long until it is due for a certain employee. I have a table which holds all the employee info (Workday_Excel_Update) as well as the training course number, when he was enrolled and if it was completed. I also have a second table with all the training numbers (CourseNumbers), the month-day when the training should be complete and the frequency in months.

I wrote the code in the module to use as field but I keep getting the following error:

"The expression you entered as a query parameter produced this error: 'Microsoft Access cannot find the name 'CourseNum' you entered in the expression'"

the reason for the dlookup is to find the corresponding date and frequency for the course number.

the CourseNumbers table has the following fields: -CourseNumber -CourseName -Frequency (which is the month-day) -Months (The frequency in months until the training is required again)

If anyone can tell me why and if you have a solution I would greatly appreciate it.

Image of my query with the module

Function getDueDate(CourseNum As String)


Dim yearCounter As Integer
Dim yearStart As Date
Dim yearNow As Integer
Dim monthFreq As Integer
Dim daysDue As Integer

If Not IsNull(DLookup("[Months]", "CourseNumbers", "CourseNumber = CourseNum")) Then

 
monthFreq = DLookup("[Months]", "CourseNumbers", "CourseNumber = CourseNum")

yearStart = DLookup("[Frequency]", "CourseNumbers", "CourseNumber = CourseNum")

yearCounter = Year(yearStart)

yearNow = Year(Date)

While yearCounter <= yearNow
    
    yearStart = DateAdd("m", monthFreq, yearStart)
    
    yearCounter = yearCounter + (monthFreq / 12)
    
Wend

daysDue = DateDiff("d", Date, yearStart)

Else

daysDue = 0

End If


getDueDate = daysDue

End Function

braX · Answer 1 · 2021-12-02T01:20:31.463

CourseNum is a variable. You do not include variables inside of quotes.

You also do not specify if the field CourseNumber is a text field or a numeric field, so depending on that, you want to:

Replace "CourseNumber = CourseNum" (in all 3 places):

If it's a number field, replace it with:

"CourseNumber = " & CourseNum

If it's text field, replace it with:

"CourseNumber = '" & CourseNum & "'"

_{Unrelated: If your field is text, then you should probably not name it something that has the word "number" in it.}

Create query that calculates the due dates using Dlookup

1 Answers1