Control Source Error

[Sondra]

I have the following Control Source and its giving me an
error can someone tell me where I have written it wrong???

= Right("CM020-"&Format([Year],2)&"-"&Format
([CM_Number],000))

Ultimately I want:

CM020-03-001
CM020-03-002
CM020-03-003
etc.

Thanks in advance.

 

[Marshall Barton]

I have the following Control Source and its giving me an
error can someone tell me where I have written it wrong???

= Right("CM020-"&Format([Year],2)&"-"&Format
([CM_Number],000))

Ultimately I want:

CM020-03-001
CM020-03-002
CM020-03-003
etc.

The formats are not right, You probably meant to use:

= Right("CM020-" & Format([Year], "00") & "-" &
Format([CM_Number],"000"))

 

[Tim Ferguson]

= Right("CM020-" & _ Format([Year],2) & _
"-" & _
Format([CM_Number],000) _
)

The second argument to the Format function has to be a string value

The Right function has two arguments, and you have only supplied one.

Year is a reserved word in VBA, SQL, and Jet, and is therefore a really
dumb thing to use for a field name.

Try this:

Format([YearNumber] Mod 100, """CM020-""00") & _
Format([CM_Number], "\-000")

Hope that helps


Tim F

 

[Sondra]

The formats are not right, You probably meant to use:

=Right("CM020-" & Format([Year], "00") & "-" &
Format([CM_Number],"000"))
*********
Tried the way you said and it now says I have too many
arguments.....

Please explain or help??

 

[Marshall Barton]

The formats are not right, You probably meant to use:

=Right("CM020-" & Format([Year], "00") & "-" &
Format([CM_Number],"000"))
*********
Tried the way you said and it now says I have too many
arguments.....

Sorry, but I missed your use of the Right function (which
doesn't seem to have anything to do with your question).

See Tim's response for a more accurate answer.

 

[Sondra]

Do I get rid of my =Right function???

-----Original Message-----

= Right("CM020-" & _ Format([Year],2) & _
"-" & _
Format([CM_Number],000) _
)

The second argument to the Format function has to be a string value

The Right function has two arguments, and you have only supplied one.

Year is a reserved word in VBA, SQL, and Jet, and is therefore a really
dumb thing to use for a field name.

Try this:

Format([YearNumber] Mod 100, """CM020-""00") & _
Format([CM_Number], "\-000")

Hope that helps


Tim F



.

 

[Van T. Dinh]

Try:

= "CM020-" & Format([Year] MOD 100, "00") & "-" &
Format([CM_Number], "000")

 

[Tim Ferguson]

Do I get rid of my =Right function???

I wasn't at all clear what it was meant to be doing. If you want to throw
away the century part of a year (and take on board all the damage that does
to sorting and grouping -- how short are the memories of people anyway?)
then the simplest way is to use a Mod operator.

Try reading (a) the response and(b) the help files.

B Wishes


Tim F