fill empty field with *

[deepbreath]

suppose i have a "Address" field of 70 characters. if i enter only 50 characters. is it possible to fill the rest of 20 with "*" atuomatically when report is generated.

 

[jimbrooking]

If the field is named tbxAddress you could (maybe in the OnFormat event) write

---begin---
intLen = 70 - Len(me.tbxAddress)
strStars = ""
For intI = 1 to intLen
strStars = strStars & "*"
Next intI

Me.tbxAddress = Me.tbxAddress & strStars
---end---

You may want to add error checking in case the Address has more than 70 characters, for example, unless that's a restriction in the underlying field.

[My favorite language, Rexx, has a Copies(string, count) function, but I don't see that in VBA.]

 

[deepbreath]

sorry didn't get it. where to put the code

 

[dhoffman]

Go to the OnFormat event of the detail section, or whatever section has the textbox. Click on the dots, choose code, put the code where the cursor ends up.

Or you could write a function like this:

Public function FormatAddress(address as variant) as string
dim intLen as integer
dim i as integer
address = Trim(address)
intLen = 70 - Len(address)
for i = 1 to intLen
address = address & "*"
Next i
FormatAddress = address
End function

You would put this function into a module object. Then you could use this function anywhere in your database. So for a textbox control on your report, instead of making the data source simply the name of a field from the underlying query such as fldAddress, you would make that data source an expression like this:
=FormatAddress([fldAddress])

Easy as pie.


[This message has been edited by dhoffman (edited 07-16-2001).]

 

[jimbrooking]

I have filled yet another gap in my knowledge of Access/VBA. The String Function does what's needed. Editing dhoffman's function:

Public function FormatAddress(address as variant) as string
FormatAddress = Trim(address) & String("*",70-Len(Trim(address))
End function

Still need to worry about error checking...

 

[dhoffman]

good call, jim, I admire somebody who likes to cut a function down to as little code as possible.