Better way to test for field being left empty

[TD]

I use this to test if a user has left a text field empty:

If Me.txtProductID = "" Or IsNull(Me.txtProductID) = True Then
do something here
End If

Is there a better way to determine if a user has left a field empty?

Thanks,
TD

[Wayne Gillespie]

On 10 Nov 2005 07:32:46 -0800, "TD" <dl**@adsi-sc.com> wrote:

I use this to test if a user has left a text field empty:

If Me.txtProductID = "" Or IsNull(Me.txtProductID) = True Then
do something here
End If

Is there a better way to determine if a user has left a field empty?

Thanks,
TD

I use -
If Len(Me.txtProductID & vbNullString)=0 Then 'empty field

[jimfortune]

TD wrote:

I use this to test if a user has left a text field empty:

If Me.txtProductID = "" Or IsNull(Me.txtProductID) = True Then
do something here
End If
What is the procedure for inserting a blank string into a textbox on a
form?
Is there a better way to determine if a user has left a field empty?
You can check all the controls with names beginning with "txt" in one
loop. Maybe put a value in the Tag Property of textboxes that are
required to have data.

Thanks,
TD

James A. Fortune

[Keith W]

"TD" <dl**@adsi-sc.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

I use this to test if a user has left a text field empty:

If Me.txtProductID = "" Or IsNull(Me.txtProductID) = True Then
do something here
End If

Is there a better way to determine if a user has left a field empty?

What you have there is a belt 'n' braces approach as you're testing for both
nulls and for zero-length strings. The only slight change I'd make is

.... Or IsNull(Me.txt) Then

but that's just personal preference.

Keith.
www.keithwilby.com

[Phil Stanton]

What's the matter with

If Nz(me.txt) = "" then
....
End If

Phil
"TD" <dl**@adsi-sc.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

I use this to test if a user has left a text field empty:

If Me.txtProductID = "" Or IsNull(Me.txtProductID) = True Then
do something here
End If

Is there a better way to determine if a user has left a field empty?

Thanks,
TD

[David W. Fenton]

"TD" <dl**@adsi-sc.com> wrote in
news:11**********************@o13g2000cwo.googlegr oups.com:

I use this to test if a user has left a text field empty:

If Me.txtProductID = "" Or IsNull(Me.txtProductID) = True Then
do something here
End If

Is there a better way to determine if a user has left a field
empty?

Why are you allowing zero-length struings? In my opinion, it's bad
design to define your underlying table to store ZLS's.

Form controls do not store ZLS if unbound or bound to a field that
disallows ZLS. So, in either of those cases, you need only check for
IsNull().

As has been pointed out, though, if you *are* storing ZLS and need
to check for Null or ZLS, then checking the length of the control
value concatenated with a ZLS is the most efficient method.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Phil Stanton" <ph******@xxstantonfamily.co.uk> wrote in
news:43***********************@ptn-nntp-reader02.plus.net:

What's the matter with

If Nz(me.txt) = "" then
....
End If

Well, for one, you're allocating memory to store your zero-length
string for comparison.

If Nz(me.txt) = vbNullString then

is always going to be more efficient, as vbNullString is a
predefinied Access constant, so the memory for it is already
allocated.

I vaguely remember, also, that MichKa seemed to think that testing
Len() was more efficient than any alternatives, though that
different would matter in only large loops.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Lyle Fairfield]

David W. Fenton wrote:

vbNullString is a
predefinied Access constant, so the memory for it is already
allocated.

How much memory?

--
Lyle Fairfield

[Larry Linson]

"Lyle Fairfield" wrote

vbNullString is a
predefinied Access constant, so the memory for it is already
allocated.

How much memory?

I'd guess: Not enough that you'd seriously miss it with 1GB of RAM, or more,
as is common in even modestly priced notebook computers these days.

Larry

[lylefair]

It may be safer to set the properties of the field to "Required" and
not to allow ZLS, and then to let Access (and the DB engine ...JET?) do
their thing, handle the warnings, requirings etc.
Access is what we pay for, why not use it?
Access has a standard way of doing things. When we have Access do them
throughout our application then the application and its gui are
standardized.
The chance of Access screwing up is about 0.0000000000000001 %.
I've developed in Access since ... it's so far back I can't remember
whether I got on at V1 or V2. The chance of my screwing up is about
(IMO) 5 %.
What is yours?
Of course, maybe my guesses as to why you want to check this are wrong.

[Jim Devenish]

To a user one or more blanks in a field looks the same as none at all
and so I always test:

If Trim(Me.txtProductID) = "" Or isnull(Me.txtProductID) then

[(PeteCresswell)]

Per David W. Fenton:

is always going to be more efficient, as vbNullString is a
predefinied Access constant,

Is there any consideration around where the code has to go to get vbNullString -
as opposed to an inline ""?

I guess it's just an address either way... but I don't know anything about what
goes on below VBA.
--
PeteCresswell

[lylefair]

Try StrPtr(vbNullString)
and StrPtr("")

[David W. Fenton]

"(PeteCresswell)" <[e-mail]> wrote in
news:gl********************************@4ax.com:

Per David W. Fenton:

is always going to be more efficient, as vbNullString is a
predefinied Access constant,

Is there any consideration around where the code has to go to get
vbNullString - as opposed to an inline ""?

I guess it's just an address either way... but I don't know
anything about what goes on below VBA.

I don't understand your question.

When your code compares two things, memory has to be allocated for
the comparison. If one side of the comparison is a global constant,
then the memory is already allocated, so no new allocation is
required.

Obviously, this would have to be in a loop of 1000s of iterations
for it to make any real difference, but it's not that uncommon a
scenario for loops of that nature.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Lyle Fairfield <ly***********@aim.com> wrote in
news:8j********************@read1.cgocable.net:

David W. Fenton wrote:

vbNullString is a
predefinied Access constant, so the memory for it is already
allocated.

How much memory?

That's irrelevant. What matters is the time it takes to allocate the
memory for the empty string in each iteration of the loop.

Now, if you did this:

Dim strMyEmptyString As Strig
strMyEmptyString = ""

If Nz(me.txt) = strMyEmptyString then
. . .
End If

then you've removed the problem, as you've allocated memory for it
by creating a variable and storing the value for it.

In a loop, you wouldn't see any different between strMyEmptyString
and vbNullString, since the CPUs necessary to allocate the memory
for your variable would be very small and happen only once.

But why do that when there's a global constant with that value
stored in it already defined? THe memory is already allocated every
time you start up Access, so why allocate more, even when there is
no realistic perfromance penalty?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Jim Devenish" <in***************@foobox.com> wrote in
news:11**********************@f14g2000cwb.googlegr oups.com:

To a user one or more blanks in a field looks the same as none at
all and so I always test:

If Trim(Me.txtProductID) = "" Or isnull(Me.txtProductID) then

Why would you allow the storage of spaces in a field?

The default behavior of Access text controls trims all trailing
spaces, so the only way I've ever ended up with spaces in a field
was when I was appending data in code or SQL and was sloppy about
it.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[(PeteCresswell)]

Per lylefair:

Try StrPtr(vbNullString)
and StrPtr("")

"" looks tb faster.

For 100 million iterations, I got 5.203 seconds for vbNullString and 3.828
seconds for "".

viz:
-------------------------
Const mLim = 100000000
Const mWhoopie = "xxx"

Public Sub aa()
Dim i As Long
Dim L As Long

DoCmd.Hourglass True
LogTime True, "Begin vbNullString"
For i = 1 To mLim
L = StrPtr(vbNullString)
Next i
LogTime False, "End vbNullString"
DoCmd.Hourglass False
End Sub
--------------------------

Then I did the real thing, getting 29.250 seconds for vbNullString and 27.922
seconds for "".
viz:
-------------------------
Const mLim = 100000000
Const mWhoopie = "xxx"

Public Sub aa()
Dim i As Long
Dim L As Long

DoCmd.Hourglass True
LogTime True, "Begin vbNullString"
For i = 1 To mLim
If Len(mWhoopie & vbNullString) > 0 Then
'Do nothing...
End If
Next i
LogTime False, "End vbNullString"
DoCmd.Hourglass False
End Sub
-------------------------
Bottom line: it looks like "" is faster; but only in the most technical,
legalistic sense. As a practical matter, the execution speeds of the two
appear tb identical.
--
PeteCresswell

[lylefair]

hmmmmmmmm
Well, creative minds do creative things, even unexpected things. I
thought you would investigate the StrPtr of "" and of vbNullString.
What you would find out if you were here in Carlisle ON Canada is that
"" is a long pointer (VarPtr eg 1308288 ) to a long String Pointer
(StrPtr eg at 1308288 we find four bytes which represent 134606676). At
the memory location of the StrPointer 134606676 we find the string in
unicode (Thus "Pete" would take up 8 bytes. The length of the string is
found in the four bytes before the StrPtr memory location, in ""'s
case, zero, [in Pete's case, 8] and of course there would be no
relevant bytes at the location, as the length of "" is zero.
BUT, vbNullString is a long pointer to nowhere; that is its string
pointer is zero (0). I am guessing therefore that there is no memory
allocated for vbNullString, not when Access opens and not ever.

[(PeteCresswell)]

Per David W. Fenton:

I don't understand your question.

I was thinking in terms of references - that maybe one way there was only a
direct reference and another way it might have to go through a handle or
something.

All seemingly moot in light of my little experiment...
--
PeteCresswell

[lylefair]

David W. Fenton wrote:

But why do that when there's a global constant with that value
stored in it already defined? THe memory is already allocated every
time you start up Access, so why allocate more, even when there is
no realistic perfromance penalty?

How much memory?

[David W. Fenton]

"(PeteCresswell)" <[e-mail]> wrote in
news:3u********************************@4ax.com:

Bottom line: it looks like "" is faster; but only in the most
technical, legalistic sense. As a practical matter, the
execution speeds of the two appear tb identical.

You're not testing the situation I said was faster.

I said that:

If [expression] = vbNullString Then

is going to be faster in a loop than

If [expression] = "" Then

Lyle's StPter() code is not looking at the same task, and your test
code is concatenating, which is introducing irrelevant tests.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc