SQL Function Missmatch error (1 Viewer)

[VSolano]

I need the help of one of you regarding this function.

I am creating a form to pull value from employees for each month of the year and I want to create a function to pull those values from a table. the function is giving me a mismatch error and I can not figure out.

Thanks in advance.

Option Compare Database
Option Explicit

Public Function MonthlyAmt(FLNo As Integer) As Currency

Dim MthDate As Date
Dim MonthNO As Integer
Dim YearNo As Integer
Dim MatchAMT As Currency
Dim STCreteria As String
Dim FMTest As Form
Dim TAGValue As Integer
'Dim flno As Integer

Set FMTest = Forms!FROnScreenTest

TAGValue = FMTest.Tag
TAGValue = Int(TAGValue)


Debug.Print TAGValue
'STCreteria = " Month(MatchMonth) =" & MonthNO & " AND EmployeeID=" & FLNo





MthDate = DLookup("matchmonth", "TBMatchAmount")
MatchAMT = DLookup("matchamount", "tbmatchamount")

MonthNO = Month(MthDate)
YearNo = Year(MthDate)




' MonthlyAmt = DLookup("matchamount", "tbmatchamount", " month(matchmonth) =" & TAGValue & "" And " EmployeeID=" & FLNo & "")


Debug.Print MonthlyAmt


End Function

 

[isladogs]

Which line is giving a mismatch?
Normally due to using the wrong delimiters for number/date or text fields

This line seems to be commented out though its hard to see - using code tags make code easier to read

' MonthlyAmt = DLookup("matchamount", "tbmatchamount", " month(matchmonth) =" & TAGValue & "" And " EmployeeID=" & FLNo & "")

Anyway assumimng both TagValue & FLNo are numbers, I think it should be this

 MonthlyAmt = DLookup("matchamount", "tbmatchamount", "month(matchmonth) =" & TAGValue & " And EmployeeID =" & FLNo)

 

[Pat Hartman]

The function doesn't seem to do what you said it should. Maybe you should tell us what you intend to do with the single number the function will return. You almost certainly want to be using a query rather than a function to do what you described.

 

[VSolano]

The purpose of this function is to pull monthly amount for various employees on a continues form. I do not want to use the crosstab option because I want to be able to filter the form by year later one.
The final result should be something like this:

Employee Name Jan Feb March ....

XXXX 50 75 100

 

[Minty]

You can still use a cross tab, just reload/requery the underlying query criteria in code from your form.

 

[VSolano]

Can you please explain a more simple term what I should do to accomplish this task