Is there an equivalent Replicate() function in Access

Hi,

I am trying to build a string from a datetime datetype and want to make sure that if a day or month is only one digit long then it is padded with a leading 0.

In SQL I can do it like this;

declare @.datetime datetime

select @.datetime = getdate()

select 'TPR'

+ replicate('0', 2 - len(datepart(dd,@.datetime))) + convert(varchar(2),datepart(dd,@.datetime))

+ replicate('0', 2 - len(datepart(mm,@.datetime))) + convert(varchar(2),datepart(mm,@.datetime))

+ convert(varchar(4),datepart(yyyy,@.datetime))

Do you know how I can do the same thing in Access?

Thanks for your help

It would be SO-O-O-O much easier to do it in this manner:

SELECT right( '00' + cast( datepart( day, @.DateTime ) AS varchar(2) ), 2 )

(Should work in Access AND SQL Server.)

But really, wouldn't something like this be a better solution: (this is SQL)

SELECT ( 'TPR' + SELECT replace( convert( varchar(10), @.DateTime, 101 ), '/', '' ))

For Access, you would have to use the FORMAT function instead of convert. Refer to the help for FORMAT().

|||

Yes Buddy.. You have it..

STRING(<number>, <Char>)

example:

String(5,"M") => MMMMM