Add P in front of a numberr

[angie]

Hi I have a field called " workordersID" and a check box named "Pollock's" I would like to do the following.

If the check box Pollock's is checked (yes) than I would like workordersID to add a "P" in front of the workordersID number.



The workorderID is a auto number. Can this be done?

Thanks Angie

[pbaldy]

IIf([Pollock's] = True, "P", "") & workordersID

You don't really have the inadvisable apostrophe in the field name, do you?

[angie]

Which field do I put this in the workordersID or the Pollock's?
And do I add it as default value ?
Thanks
Angie

[pbaldy]

I would probably just use that for display, not store it.

[Missinglinq]

You have to understand that this cannot be done in the Textbox that is bound to your workordersID Field; Autonumber Fields cannot be edited, period. So you'll need to have another Control on any Forms/Reports that you want this to appear on. And as Paul suggested, because this can be a calculated value, it should not be bound to a Field in the underlying Table, but simply recalculated as needed. So place an Equal Sign in front of his code

=IIf([Pollock's] = True, "P", "") & [workordersID]

and place it in the Control Source Property of an Unbound Textbox.

BTW, his concern with having that apostrophe in Pollack’s is dead on; sooner or later it will bite you in places you don’t want to be bitten in, and will cause you extra work, even before that!

Also, having said all of this, you also need to understand that the Autonumber Datatype is intended to be used for one purpose and only one purpose, to provide a unique identifier for each Record. Here's a post I've archived from a gentleman named John Vinson, MVP, explaining how Autonumbers work:

When using Autonumber, do be aware that there will be gaps in the numbering any record that's deleted will leave a gap; hitting <Esc> after starting a record will leave a gap; adding records using an Append query may leave a gap, often a huge one; replicating the database will make your invoice numbers random.

In short... it's best NOT to use Autonumbers for human consumption, and particularly not for consumption by accountants and auditors. Invoice sequences like 319, 321, 322, 385, 386, 221841246, 1083225152 make such people get very nervous.

If the possible problems John has pointed out won't present a problem for you, then go ahead and use the Autonumber.

Linq ;0)>

[June7]

To echo those comments, avoid spaces and special characters/punctuation (underscore is exception) in naming convention. Also avoid reserved words as names.

[ItsMe]

You can format a field at the table layer. I believe the syntax is.
>" P"

[angie]

Thanks everyone it works. You are great. Have a great day. Angie