Member Avatar for Erick_3

How do i retrieve an ID with an AUTONUMBER as DATATYPE and has alphanumeric format ex(ER001)? Can some genius programmer out there help? :P?

im using Access for backend and VB 2010.

My code so far only returning the last number of the ID Column ex(1 instead of ER001)

Dim SQL As String = "SELECT @@IDENTITY FROM ReqItemList WHERE ReqItem = " & inputin & " "

Edited by Erick_3
  • 3 Contributors
  • 7 Replies
  • 241 Views
  • 19 Hours Discussion Span
  • Latest Post Latest Post by Reverend Jim

Dani AI

Generated

Short answer: keep the Access Autonumber as a plain integer and format the presentation layer instead of changing the stored value. That avoids quoting/concatenation pitfalls and race conditions that come from trying to build an "ER001" string inside SQL.

Building on the thread: is correct that the DB field is numeric, and was heading in the right direction with Format. A straightforward, robust approach is to read the numeric ID from the database (or read it immediately after an INSERT using the same open connection) and then zero-pad in VB. Example formatting (assumes an integer variable named idNum):

' idNum is the integer retrieved from the Autonumber column (e.g. 1)
Dim numberPart As String = idNum.ToString("D3")   ' yields "001"
Dim formattedId As String = "ER" & numberPart     ' yields "ER001"

Practical tips and cautions:

  • Retrieve the integer ID via a parameterized command (avoid string concatenation so values are quoted safely).
  • If the formatted identifier must be created right after insert, get the generated ID on the same open connection and then format it; do not use MAX(ID)+1 to compute the next value (race condition in multi-user scenarios).
  • If many places need the formatted form, store only the numeric Autonumber in the table and generate the formatted string in a view/query or the application layer for consistency.
  • If formatting in Access SQL is required, ensure the format mask is passed as a quoted string and the function is applied to the numeric field (thread examples show common mistakes with argument order and quoting).

This keeps the data model clean and makes producing "ER001" deterministic and easy to maintain.

Recommended Answers

All 7 Replies

Member Avatar for Reverend Jim

What happens if you use the actual column name in the query like

SQL = "SELECT ID FROM ReqItemList WHERE ReqItem = " & inputin

When you are doing a SELECT it doesn't matter if the column is AUTONUMBER or not.

Edited by Reverend Jim
Member Avatar for Erick_3

I have already tried that its giving out the same output only returning the last number of the ID 1 instead of ER001. thanks for the reply

Member Avatar for Reverend Jim

If the column is AUTONUMBER then the value being stored is a simple numeric value, not a string. You are getting back the exact contents of the field in the database. I may be mistaken but I don't see how you can have an AUTONUMBER string value.

Edited by Reverend Jim
Member Avatar for Erick_3

ive done some tinkering with my code now it looks like this

 Dim SQL As String = "SELECT (Format('RQ',000)& MAX(ID)) FROM ReqItemList WHERE ReqItem ='" & inputin & "'"

ive successfully added the RQ now the output looks like this 'RQ1' my problem now is the '000' is not appearing while in the DB it looks like this 'RQ001'

what about retrieving just the whole number '001' not just '1' any thoughts?

Member Avatar for Santanu.Das

It should be

 Dim SQL As String = "SELECT ('RQ' & Format(MAX(ID),000)) FROM ReqItemList WHERE ReqItem ='" & inputin & "'"
Member Avatar for Erick_3

Sorry mate thanks for trying but still a no, same output. RQ1

Member Avatar for Reverend Jim

@shark_1 - as for

Dim SQL As String = "SELECT ('RQ' & Format(MAX(ID),000)) FROM ReqItemList WHERE ReqItem ='" & inputin & "'"

You use MAX to get the maximum value of a column and WHERE to get the value of a specific column (in this case). You wouldn't use both, especially since AUTONUMBER will automatically generate the next available ID anyway. The OP didn't put single quotes around inputin so I am assuming inputin is a numeric value. Also, the actual prefix specified was ER, not RQ.

I don't have Access installed so I can't test this, but FORMAT(fieldname,"000") is supposed to pad with zeroes so

Dim SQL As String = "SELECT ID = 'ER' & FORMAT(ID,'000')" &
                    "  FROM ReqItemList" &
                    " WHERE ReqItem = " & inputin

should give you what you want.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.