Handling Null Fields in Access 2016 / VBA
I am a VB .Net guy and feel very much like a fish out of water in VBA. Need help working with Nulls coming from a DB field into a VBA function for further processing. Any help would be greatly appreciated.
I have a VBA function that takes a few parameters and runs some extensive logic on them to come up with a True or False result.
The call to the function is currently in the Query Designer interface as a column. The call to the function is bStaleDates([Run Ordered]).
The [Run Ordered] field is a date field but can be null if the item has not yet been ordered.
The code for bStaleDates(dDateIn as Date) as Boolean does some testing and returns True or False which is the value tested in the Query Designer's Criteria entry.
The problem are the null fields. If I leave the dDateIn as Date, nulls blow up the query. I thought I would try dDateIn as Object and Variant and test for IsNull() but that did not work either and query still blows up on data type mismatch when the field is Null.
I need the records where the field is Null so I can't eliminate them during the query before sending to my code, but I need a way for the code to correctly handle the Nulls so that the code can receive the parameter, perform a Null check, and then if it is a valid date, perform the rest of the testing on the value.
This is the issue in a nutshell and simplified. The actual code has about 9 date fields being passed to it from the record as the testing involves looking at how the dates relate to each other. It is used in the Query Designer because the result of the working function will be used as part of the Criteria for deciding whether or not the record is to be included in the query results.
However, if someone can tell me how to get the date field into my function even when it is null without a data type mismatch error, I would be grateful and can handle the remainder of the logic.
Thanks
I have a VBA function that takes a few parameters and runs some extensive logic on them to come up with a True or False result.
The call to the function is currently in the Query Designer interface as a column. The call to the function is bStaleDates([Run Ordered]).
The [Run Ordered] field is a date field but can be null if the item has not yet been ordered.
The code for bStaleDates(dDateIn as Date) as Boolean does some testing and returns True or False which is the value tested in the Query Designer's Criteria entry.
The problem are the null fields. If I leave the dDateIn as Date, nulls blow up the query. I thought I would try dDateIn as Object and Variant and test for IsNull() but that did not work either and query still blows up on data type mismatch when the field is Null.
I need the records where the field is Null so I can't eliminate them during the query before sending to my code, but I need a way for the code to correctly handle the Nulls so that the code can receive the parameter, perform a Null check, and then if it is a valid date, perform the rest of the testing on the value.
This is the issue in a nutshell and simplified. The actual code has about 9 date fields being passed to it from the record as the testing involves looking at how the dates relate to each other. It is used in the Query Designer because the result of the working function will be used as part of the Criteria for deciding whether or not the record is to be included in the query results.
However, if someone can tell me how to get the date field into my function even when it is null without a data type mismatch error, I would be grateful and can handle the remainder of the logic.
Thanks
ASKER
Thanks for responding so quickly. I wasn't expecting that and have gone out for a little while.
By blowing up, I mean the query stops on a data type mismatch....no records returned.
The NZ() function may work for me. I was not aware of it and will try it when I get back later today.
Thanks
By blowing up, I mean the query stops on a data type mismatch....no records returned.
The NZ() function may work for me. I was not aware of it and will try it when I get back later today.
Thanks
You might try something like:
Public Function bStaleDates(dDateIn as Variant) as Boolean
if IsNull(dDateIn) then
bStateDates = False
Exit Function
End if
'the rest of your code here
End Function
Nz([Run Ordered], 0) Will NOT work for a date field. You will end up with a date of Dec 30, 1899 being passed to the function since that is the "0" date. Dates are stored as double precision numbers with the integer portion representing the number of days since 12/30/1899 (or prior to in the case of negative numbers) and the decimal portion is the time since midnight.
Dale's solution should work.
Dale's solution should work.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am sorry for the extended delay in responding. Got tied up on something else.
I tried Dale's solution and it worked perfectly. Thanks Dale.
Sorry Norie. While your suggestion may have provided a good workaround, I agree with the others that a more solid solution is not to change the value of the field being tested but rather to process the NULL as NULL. Dale's solution allows for that.
Thanks everyone for your help.
I tried Dale's solution and it worked perfectly. Thanks Dale.
Sorry Norie. While your suggestion may have provided a good workaround, I agree with the others that a more solid solution is not to change the value of the field being tested but rather to process the NULL as NULL. Dale's solution allows for that.
Thanks everyone for your help.
ASKER
Thanks Dale!
What I suggested doesn't change the value of the field.
ASKER
You are correct. The value of the field itself is not physically changed but in code, the value being processed is not the actual value of the field but rather a stand-in value.
I think the Variant solutionnworks better as you are still processing the field and it's original value.
Sorry....I did not mean to give an incorrect impression of your suggestion.
I think the Variant solutionnworks better as you are still processing the field and it's original value.
Sorry....I did not mean to give an incorrect impression of your suggestion.
No problem, just wanted to clarify.
How is the function 'blowing up'?
Have you considered handling the Null when calling the function, perhaps using Nz?
bStaleDates(Nz([Run Ordered], 0))