Using Right in VBA

[TinaCa]

I need to concatenate an invoice Id with the last two digits of a date field (invoice date) to get the invoiceid & Year (otherwise I'll have 60000 invoice id's ). I can't seem to figure out the best way to do this within a recordset. here is the code I am using.

......
CustId = rs.Fields("companyId")
InvNoYr = rs.Fields("ItemDate") Here is where I am trying to get the two digit year (91, 92 etc)

Inv

'right( [tblInvoiceDetails]![InvoiceDate] ,2)

Do While rs.EOF = False

If rs.Fields("companyId") = CustId Then



rs.Edit
rs!InvoiceId = InvNo & InvNoYr - Here is where I need to join the invoiceNo to the Invoice Year
rs.Update
rs.MoveNext
.....

[pbaldy]

If that's a date/time field, try

Format(Year(rs.Fields("ItemDate")), "yy")

[TinaCa]

Your code gave me a number 5 for the variable. So I changed to code InvNoYr = Right((rs.Fields("ItemDate")), "2") and got the year 91. Thanks for the help.

[orange]

Just curious... what's the issue with 60000 invoice numbers, especially if each invoice has a Customer Number and an Invoice Date (or equivalent)?

[pbaldy]

I goofed it, should simply be the Format function. I'm guessing your field is text though, which changes everything.