Hi,
I am currently trying to create a custom function that will accomplish the following:
- Look at a field (Field#3) in a record in a query. Field#1, Field#2 & Field#3 are coming from a different query.
- Determine if the field is populated
- If it is populated print the results to a different query field (Field#4)
- Save the populated field to a variable for use in the next record iteration
- Advance to the next record
- Determine if the field is populated (if it is not populated)
- Print to the query the saved data from #4 above to (Field#4)
The following is the layout of the query:
Field#1 Field#2 Field#3 Field#4
xxx xxx 100505
xxx xxx Null or ""
xxx xxx Null or ""
xxx xxx Null or ""
xxx xxx 100490
xxx xxx Null or ""
xxx xxx Null or ""
xxx xxx Null or ""
What I would like to see:
Field#1 Field#2 Field#3 Field#4
xxx xxx 100505 100505
xxx xxx Null or "" 100505
xxx xxx Null or "" 100505
xxx xxx Null or "" 100505
xxx xxx 100490 100490
xxx xxx Null or "" 100490
xxx xxx Null or "" 100490
xxx xxx Null or "" 100490
The following is the function that I have started. The problem I have encountered is that I am losing the variable as described in #4 above. When the next record processes, the variable is wiped out. I don't know how to save it outside of the routine so the next iteration has a value to compare to. I am successful in the first If statement as the first encountered product number is being saved to the query. Where I am not successful is in retaining varSecondProd and triggering the Else. Every cell evaluates to greater than zero even though it is "".
Code:
Public Function ProdNum(Prod) As Variant
Dim varFirstProd As Variant
Dim varSecondProd As Variant
varFirstProd = Prod
If varFirstProd > 0 Then
ProdNum = varFirstProd
varSecondProd = varFirstProd
Else
If varFirstProd = "" Then
ProdNum = varSecondProd
End If
End If
End Function