Can an autonumber field generate a value of zero? And what about negative numbers?
Can an autonumber field generate a value of zero? And what about negative numbers?
Not to my knowledge.
no. counting begins with 1 and goes up.
So in a VBA function with a Long return type that returns a record's Primary Key that is of AutoNumber data type, it's perfectly safe for my function to return zero or even a negative number in the event of an error (or not found, etc.) and I won't inadvertently be returning a "false negative" error to my calling sub?
A function that returns a long can certainly return zero or a negative number. Depends on the function's calculations. The function will return whatever you tell it to return. If you return an error value, that's not a record's primary autonumber key, is it?
Explain what the function does, and what you want it to return, post it's code. We can evaluate what you are trying to achieve.
not disagreeing with the suggestion to post the function, etc. but why not have the function return a variant if it can only find or not find the autonumber value? If the return value is a long, then you have the number you need. If not, you make it return Null. Then you can deal with the return value right away - in the calling part of the code. Like
If IsNull (MyFunction) Then
do number not found stuff, like exit sub
Else
do number found stuff
End If
Of course, you can reverse the steps, but hopefully you get the idea.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
I think you've misunderstood the question. I wondering if it would be safe to use a return value of zero as error code to the calling sub with out risking returning a false error in the following scenario
See the following dummy code:Explain what the function does, and what you want it to return, post it's code. We can evaluate what you are trying to achieve.
If an AutoNumber field could ever equal zero this would be a problem for the dummy code posted above, a "false negative". The calling sub would think it's an error code but it really was the id that it was looking for. So, that's why I ask if an AutoNumber field can ever generate a zero (or even a negative).Code:Function get_id_by_name( name as string) as Long On error goto ErrHandler 'do work get_id_by_name = results from work above or 0 if not found ExitHandler: Exit Function ErrHandler: get_id_by_name = 0 resume ExitHandler End Function Sub testing() Dim id as Long id = get_id_by_name("Frank") if id = 0 then 'frank wasn't found or there was an error so do something about it else 'do work end if End Sub
the autonumber can be set to random rather than incremental. Something I have done in the past to prevent users from assigning any meaning to the PK. When set to random, it will generate negative numbers but so far as I am aware, not a zero.Can an autonumber field generate a value of zero? And what about negative numbers?
note you can populate an autonumber field with a zero using an append query
It's not an error code per se, and you're not using it as one either. You're using the numerical result of the function to decide on subsequent code execution, thus it's not an error at all - it's a result. So yes, it's safe since you're not trying to update the autonumber with the return value, nor create another record using that value as a PK or FK. You could have just as easily decided on what to do using "apples" vs "oranges". I still think returning a variant makes the most sense since you don't have to declare the id variable or assign it to anything.if it would be safe to use a return value of zero as error code
Last edited by Micron; 02-21-2018 at 04:59 PM. Reason: spelin and gramur
Ah okay. So definitely don't do what I was suggesting then. I'll continue using variant return types as Micron suggested. It would just be slightly nicer and more readable if I could do like this:the autonumber can be set to random rather than incremental. Something I have done in the past to prevent users from assigning any meaning to the PK. When set to random, it will generate negative numbers but so far as I am aware, not a zero.
note you can populate an autonumber field with a zero using an append query
Dim rslt as Long
rslt = my_function()
if rslt then
'do work
end if
rather than doing like this:
Dim rslt as Variant
rslt = my_function()
if not iserror( rslt ) then
'do work
end if
Oh well. Thank you for the schooling, gents.
you can get round it this way
and call it this wayCode:Function get_id_by_name( strname as string, FoundID as Long) as Long On error goto ErrHandler 'do work FoundID= results from work above get_id_by_name=1 ExitHandler: Exit Function ErrHandler: get_id_by_name = 0 resume ExitHandler End Function
Code:dim id as long if get_id_by_name("Jones",id)<>0 then debug.print id end if
As mentioned, you don't need a variable at all - depending on how you do this. But there's a lot missing from your function so I have to be very generalI'll continue using variant return types as Micron suggested. It would just be slightly nicer and more readable if I could do like this:
The calling code example for this is in post 6. Not sure any more what the issue is about returning a number over a variant, or vice versa. The only reason I suggested the variant is because then you don't need a variable for the result - you can use the IsNull function to directly determine. Other than that, there's not much difference, unless you need to use the result of the function somehow. This is unclear.Code:Function get_id_by_name(name as string) as Variant On error goto ErrHandler 'do work, get results if results not found then get_id_by_name = Null if results found then get_id_by_name = the result ExitHandler: Exit Function End Function