Retrieve an ID with an Autonumber Dataype

[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 & " "

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

[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

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

[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?

[Santanu.Das]

It should be

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

[Erick_3]

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

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