Altering control Source with expression

[APB1981]

Hello:

Having a few difficulties here.

I am creating a form in access and want to display my Case_Id field. However, I want the field to display something slightly different.

Case_Id currently looks like: 199

I want it to display: 2005/0199 (Case.Case_date 'yyyy' / 0 Case_Id

Should I do this directly in the form , or is it possible to create a new field in the database that automatically creates this field.

Thanks for any help you can provide.

APB

 

[KenReay]

Hi

Several ways to do this:

No need to add another field to the table

In a query based on the table you could have a "calculated" column

FullCaseId: Format([Case_Date],"yyyy") & "/" & [Case_id]

you can then use FullCaseId directly as a bound couln in your form

OR

in the form have a textbox control with source of:

=Format([Case_Date],"yyyy") & "/" & [Case_id]

if you use the latter method ensure your textbox control is NOT named Case_Date or Case_id



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[APB1981]

Thanks KenReay

I have used this version in the form (and it works well):

FullCaseId: Format([Case_Date],"yyyy") & "/" & [Case_id]

However, could we make a small change:

after the / I want 4 characters. if the Case_Id = 26 then 0026

if the case_id = 189 then 0189

I was thinking something like:

=Format([Case_Date],"yyyy") & "/" & Left('0000',4-Len(Format([Case_Id])))

But it doesn't show the Case_Id now.

APB

 

[KenReay]

Hi

=Format([Case_Date],"yyyy") & "/" & Format("0000",[Case_Id])

should do it

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PHV]

=Format([Case_Date], "yyyy") & "/" & Format([Case_Id], "0000")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[KenReay]

Oops, yes, thank you for spotting that PHV

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK