Rept function in Access?

[Zack Barresse]

Hi all,

I'm looking for the functionality of the Rept() function in Excel to be used in Access. Is there any native function for this?

Let me explain my problem a little bit, maybe it's not the best way to go about it, plus the background would help I think.

Situation:
Newly created Access database to house monthly meter readings with report capabilities.

Field structure:
fldReadingID (primary key), fldReadingDate, fldReadingMeter, fldReadingValueStart, fldReadingValuePrevious

Problem:
Everything is working except my report. I have all the values in as needed but I have a calculated field with the following formula ...

IIf([txtstart]<[txtend],[txtend]-[txtstart],999999999-([txtstart]-[txtend]))

The "999999999" is the problem, this is for meter turnover. So if I have a start reading value of 998 and an end reading value of 25 the difference (formulated) value is 27. The problem lies where there will be a varying length for the meters (of values to the left of the decimal); some will have 3 numbers, some will have 7 numbers and a decimal, some will have 10 values. So my solution was a formula such as...

=IIf([txtstart]<[txtend],[txtend]-[txtstart],(1+(Rept(9,Len([txtstart])))-[txtstart])+[txtend])

This was to say that if the start number (txtstart) was greater than the end number than we could safely assume that the meter had turned over and started at 0 again. So in essence what the Rept() function was to be used for was this logic:

Start reading: 998

Take 1000 - 998, then add the end reading. To get the 1000 I was thinking about using a Rept()-type function to get the [variable] length, add one, etc.

Hope this makes sense; if not let me know what you need. Thanks.

 

[Oorang]

Hi FireFytr,
I ran into this myself. I know Office 2003 has a function called "String" that does pretty much the same thing as rept. Or you could just make your own rept by putting this in a module:

Public Function REPT(strChar As String, intRepetitions As Integer)
Dim intLpCntr As Integer
For intLpCntr = 1 To intRepetitions
    REPT = REPT & strChar
Next intLpCntr
End Function

Then you WILL have a rept. Another approach that I have not used would be to make a reference to the excel library and use Application.WorksheetFunction.Rept

 

[Zack Barresse]

Thanks Oorang!

I was playing around with the UDF and came up with this *just* before you posted ...

Function Rept(varValue As Variant, lngTimes As Long) As Variant
    Dim i As Long
    If Len(varValue) = 0 Then Exit Function
    For i = 1 To lngTimes 
        Rept = Rept & varValue
    Next i
End Function

LOL! The String function looks like I might be able to adapt it though, wasn't having much luck with the UDF; no errors, but the calculation isn't coming out right.

Thanks for the response though!!

 

[Zack Barresse]

Wow! I got it to work! Thanks for the tips Oorang! I ended up going with the UDF, but I had a syntax wrong in the formula. All looks well now, thank you!!

 

[giacomo]

just to satisfy my own curiosity I came up with a non-vb solution:

Replace(Space(Len([txtstart]))," ",9,1,Len([txtstart]))

not very elegant, but that wasn't really the point.

EDIT: I missed Oorang's mention of the STRING function, now I feel like a dummy. I actually tried it before I made my suggestion but I had the parameters flip-flopped and got a bunch of garbage.

 

[Oorang]

I wouldn't feel that way. I am not sure of the version implementation of STRING, so that could be very helpful for users of earlier versions.

 

[Norie]

String is available in Access 2000.

 

[Zack Barresse]

Thanks for the suggestion giacomo, I appreciate it. I'm still double checking the calculations but everything looks good thus far.

 

[ericwfrost]

I had a nasty bit of Excel formula code I didn't want to rewrite when porting to Access so I just flipped String around and made a Rept function...

'The parameters of the REPT function from Excel are reversed compared to the Access String function
'eric.frost@mp2kmag.com
Public Function REPT(szChar As String, nReps As Integer) As String
   REPT = String(nReps, szChar)
End Function

Here's what it looked like in Excel --

=""&REPT("n",MIN(AU2,10))&""&REPT("n",MAX(10-AU2,0))&"n"&REPT("n",MAX(AU2-9,0))&""&REPT("n",IF(MIN(19-AU2,10)<0,0,MIN(19-AU2,10)))&""

Here's what it ended up looking like as an Access function

Public Function myhtml(nIndex As Integer) As String
  myhtml = "" & REPT("n", EMin(nIndex, 10)) & _
   "" & REPT("n", EMax(10 - nIndex, 0)) & _

   "n    "" & REPT("n", IIf(EMin(19 - nIndex, 10) < 0, 0, EMin(19 - nIndex, 10))) & ""
End Function

Note that I created my own EMin and EMax functions...

'Simple "Excel" min and max functions, these only work with integers and only compares two numbers
'eric.frost@mp2kmag.com
Public Function EMin(nVal1 As Integer, nVal2 As Integer) As Integer
  If nVal1 <= nVal2 Then
    EMin = nVal1
  Else
    EMin = nVal2
  End If
End Function
Public Function EMax(nVal1 As Integer, nVal2 As Integer) As Integer
  If nVal1 >= nVal2 Then
    EMax = nVal1
  Else
    EMax = nVal2
  End If
End Function

So I ended up having to make only minimal changes to the Excel formula..

hope this helps someone!

Eric

__
http://www.mp2kmag.com/excel/