Help Please

[Samand]

I have a simple function:

Public Function DayY(ByRef Var1 As Variant, _
    ByRef Var2 As Variant) As String
    
    Dim nDayY As String
    
    If IsNull(Var1) And Var2 = "Yes" Then
        nDayY = "Yes"
    Else
        nDayY = "No"
    End If
    
    DayY = nDayY
        
    End Function

Var1 and Var2 are data in table with 25000 rows. I can call the result using:
Day Y: DayY([table1]![Data1],[table1]![Data2]) in my query.
I want to hard code [table1]![Data1] & [table1]![Data2] int the VBA so that I can call it using:
DayY T: DayY()
how do I do this?

 

[pbaldy]

How would the function know which record you meant when you called it?

 

[Samand]

I don't understand the question.
The table with the data is in the query.

 

[pbaldy]

When you call it as you do now, you are passing it the 2 values from the same record in the table that the query is on at the time. Each line returned by the query is passing its individual values to the function.

You say you don't want to pass the values, so I'm asking how the function will know which record you want it to use? I ask because I don't think it can.

 

[Samand]

When you call it as you do now, you are passing it the 2 values from the same record in the table that the query is on at the time. Each line returned by the query is passing its individual values to the function.

Yes, that sounds right.

You say you don't want to pass the values, so I'm asking how the function will know which record you want it to use? I ask because I don't think it can.

The values are always from the same fields in the record. Because I will need some VBA that will need up to 8 fields and alot of If's I was hoping that there is way to hard code them into the VBA as it is easier to read and modify if needed.

 

[pbaldy]

If you want each record returned by the query to use values from that record in the function, you either need to pass the values as you are now, or you could perhaps pass a key field value and have the function look them up, but frankly that would be much less efficient.

Not knowing the end goal, I can't suggest anything other than passing all 8 values to the function and have it handle the logic.

 

[Samand]

The end goal is to get the function to calculate the result for each record. The current method works fine, so I suppose I will stick with it.
Thanks.

 

[pbaldy]

No problem.