Return full value of field in Query?

[willlobb]

Hi all,

I am creating a query to give each student in my database a Student Number based on values for their record in the Student tbl.

[tblStudent]

Student ID (00001, 00002, 00003, etc.)
Student Name (John Smith)
School ID (101, 102, 103, etc.)
Joining Year (2011, 2012, etc.)

My query creates a calculated field: Student Number where:

Student Number: "BR" & [Joining School ID] & "-" & Right([Joining Year],2) & "-" & [Student ID]

This gives something like: BR101-11-1

However, what I would like is BR101-11-00001 with the full value o the Student ID field.

Is there a function I can use to get the full value with all the 0000s?

Many thanks

 

[SydneyGeek]

Try:
Student Number: "BR" & [Joining School ID] & "-" & Right([Joining Year],2) & "-" & Format([Student ID],"00000")

Denis

 

[willlobb]

Hi Denis,

I had tried the Format function but had not put " " round the 00000, but that worked perfectly!

Thanks for your help

 

[SydneyGeek]

You're welcome

Denis