VBA Trim Statement Syntax

[cb9002]

Hi

The code below is part on the onclick sub of a form

If Trim(Initals) = "" Then
MsgBox "Please enter your initals in the box", vbOKOnly
Else: CorrectField = CorrectField + 1
End If

The idea is, if the text box Initals is blank, bring up the dialog box, otherwise add one to the counter.

For some reason, whether the text box is empty or not, the dialog box isnt brought up and the counter is incremented.

I've also tried Trim(Me!Initals) and Trim(Me(Initals))

Thanks for any help.

 

[bubba100]

What about If me!Initals="" then

 

[cb9002]

Thanks but I need to trim the string so I pick up null values and blank spaces.

 

[cb9002]

OK, I'm using a temporary fix of

If IsNull(Initals)

but that wouldn't trap a whitespace entry, so it would be nice to know why my code isn't working!

 

[JoeAtWork]

You could do:

If Trim(nz(Me.Initials,"")) = ""



Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[missinglinq]

Actually

If IsNull(Initals)

is one of the pretty standard ways of handling this. Are you really expecting someone to copy initials form an outside source, past them into the Initials texbox, then delete them, leaving "white space?" entering a space in an Access textbox, not surrounded by text on either side, does not enter "white space." The control is still Null. Entering data and then deleting it doesn't do it either, the textbox is still Null.

If you assign a empty string to Initials, like

Me.Initials = " "

the textbox will hold then hold "white space" but that isn't likely to happen, is it?



The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[JoeAtWork]

Missingling - I was surprised by what you said about white spaces typed into the box being set to Null. I had to test it out - and sure enough it's true. In fact, I could find no way of entering and saving a string of just spaces, whether copying and pasting one in, or editing the field.

It leaves the question, how would a user who actually wants a string of spaces input it into a bound form? I was looking in the properties of both the field (in table design) and the control, and found nothing equivalent to "Allow blank spaces". It seems to me a flaw that Access is automatically deciding for me that blank spaces are illegitimate. That should be up to me to decide.

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[RoyVidar]

It leaves the question, how would a user who actually wants a string of spaces input it into a bound form? I was looking in the properties of both the field (in table design) and the control, and found nothing equivalent to "Allow blank spaces". It seems to me a flaw that Access is automatically deciding for me that blank spaces are illegitimate. That should be up to me to decide.

To allow spaces, one could convince the user to use non-breaking space (Chr(160)) by hitting for instance Alt + 0160 on Windows system, or do a little replacement in the KeyPress event of the control;

[tt]if keyascii = 32 then keyascii = 160[/tt]

This of course also brings on new challenges... ...I need to remove all the spaces, but replace(<text>, chr(32), vbnullstring) doesn't work...

You can also define a field as fixed width (but not through the interface, only through ADO / OLE DB), but it would require some valid character to be stored there, but will then pad the rest of the field with spaces - not quite per requirements either.

For some of us, the Null/ZLS provides sufficient entertainment ;-)

Roy-Vidar

 

[missinglinq]

And this, of course, begs the question of why would you want to enter just spaces into a textbox?

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[JoeAtWork]

And this, of course, begs the question of why would you want to enter just spaces into a textbox?

For the common business applications most of us are familiar with, probably never. But who knows what very unique circumstances may some day appear where it may be just the thing to solve a particular problem.

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[PHV]

To trap null, blank and zero length:
If Trim(Me!Initals & "") = "" Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886