I have a table in SQL which looks like this

RequestedChangeID ProjectID RequestedChangeNumber
140 10012 1
141 10012 2
142 10012 3
143 10012 4
144 10012 5


in vb I can crate a select query to display the data in a recordset like this

SELECT PROJECTREQUESTEDCHANGE.RequestedChangeID, [ProjectID] & "-RC" & Format([RequestedChangeNumber],"000") AS ChangeNumber, PROJECTREQUESTEDCHANGE.MediaDate AS [Date]
FROM PROJECTREQUESTEDCHANGE;

which gives me

RequestedChangeID ChangeNumber Date
140 10012-RC001 30/09/2008
141 10012-RC002 30/09/2008
142 10012-RC003 30/09/2008
143 10012-RC004 30/09/2008
144 10012-RC005 30/09/2008

but the format funtion doesn't work in SQl, so I am not sure how to do it, can anyone help please

You wrote:
> I have a table in SQL which looks like this

Since this is an Access forum, I *hope* you mean you have a table in Access that looks like this. I only am checking because SO many people say "in SQL" when they mean "in SQL Server", and of course this forum is not for SQL Server.

ANYWAY...

Assuming you *DO* mean "in Access", then the answer is easy: 100% the same.

You said "in vb I can crate [sic] a select query ..." But you are wrong. That is *ACTUALLY* a SELECT query in SQL. It just happens to be the dialect of SQL that Access understands. So you must have been using Access when you were working with VB.

Now, if you really *DID* mean "SQL Server" and you asked in the wrong forum, then I should tell you to change forums. But it's not worth it. The answer is dead easy.
     SELECT ProjectID + '-RC' + RIGHT('000' + RequestedChangeNumber, 3 ) AS ChangeNumber

Incidentally, that solution works in Access, as well. Though you might then want to change the + signs to & operators.

I have found that the FORMAT function does work as you have it above within an Access query.


      -Phil-