Access Expression Builder-Builtin Functions-Rept

[MJPPaba]

Access 2000.
The help file explains how I can use the worksheet function Rept to repeat a specified string a number of times. however it is refered to as a worksheet function not a query function. I try use it in the formula

Field1: rept("0",10-Len(

![field])

but when I run the query I get the error:
'Undefined function rept in expression'

Help!

Cheers

Matthew Paba


The only bad question is the question you dont ask!

 

[lupins46]

Help in A2000 is pretty hopeless, and as you have seen often gives you references which are nothing to do with Access.

You can use some Excel worksheet functions in Access. You need to set a reference to Excel (Tools>References in the VBA code environment) and then prefix the function with:
Excel.Application.WorksheetFunction.
(Including the final fullstop)

 

[MJPPaba]

Thanks lupins46

I have selected the only reference to Excel I could find...Microsoft Excel 9.0 Object Library and used the Excel.App etc syntax but it still doesn't like it, any thoughts?

cheers

Matthew

The only bad question is the question you dont ask!

 

[xlbo]

I think you are getting confused. Worksheet refers to Excel - REPT is an Excel function. It does not exist in Access except within VBA - NOT as part of the built in functions

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[TonyJollans]

Hi MJPPaba,

Have you tried using the [blue]String[/blue] builtin function in Access instead of an Excel function?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[MJPPaba]

I understand that the referenced could be used in the VBA but I have to admit I did interpret the posting to imply that inclusion of the Referenced object library would also allow it to be used in the Expression builder.

Alas maybe not. Does anyone have any thoughts how I can concatonate two strings of differing lengths to make the first 6 characters from the first field and the second 4 chars from the second field even though the actual string length may not be 6 and 4 chars respectively?


cheers

Matthew

The only bad question is the question you dont ask!

 

[ArkM]

What's true use case? If add leading zeroes, try format() function (it works OK in Access):

f1:format([t]![f],"0000000000")

 

[lupins46]

Assuming from your first Q that you want to fill out with zeros:

Right("000000" & cstr(firstfieldname),6) & Right("0000" & cstr(secondfieldname),4)

 

[MJPPaba]

Most splendid my excellent friends.

M

The only bad question is the question you dont ask!