Can an Autonumber Field Generate a Value of zero?

[kd2017]

Can an autonumber field generate a value of zero? And what about negative numbers?

[davegri]

Not to my knowledge.

[ranman256]

no. counting begins with 1 and goes up.

[kd2017]

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?

[davegri]

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.

[Micron]

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.

[kd2017]

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?

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

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.

See the following dummy 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

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).

[CJ_London]

Can an autonumber field generate a value of zero? And what about negative numbers?

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

[Micron]

if it would be safe to use a return value of zero as error code

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.

[kd2017]

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

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:

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.

[CJ_London]

you can get round it this way

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

and call it this way

dim id as long
if get_id_by_name("Jones",id)<>0 then
   debug.print id
end if

[Micron]

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:

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 general

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

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.