Outdated help (feat. Access '97 and VB4)

[ZillionDollarSadist]

Hello,
I'm working at a simple Access '97 + VB4 application, and I ran into a
terrible problem: something I never modified now gives me a totally
unwanted "Invalid use of null" error.
It happens in a Text.LostFocus event, this block:

Do While Not TB6.EOF
If Year(TB6(0)) = Val(Trim(Text1. Text)) Then
!!!!!!!!!!!!!He re!!!!!!!!!
...
bla bla bla
...
End If

Where TB6 is a table. The text in Text1 is compared to the Year taken
out of the first colum in TB6. Checking the database itself, I have
entries for 2002, 2002, 2003, 2005... and up to this morning everything
worked so good. Now, it only works ok for 2005, while other values are
considered to be null and error creeps in.
Any idea? I really don't know what to do.
Thanks!
ZDS

[Danny J. Lesandrini]

Re: Outdated help (feat. Access '97 and VB4)

If Text1.Text is NULL, then how can you trim it?

Try this ...

Val(Trim(Text1. Text & ""))

This will force the NULL to an empty string, which can be trimmed.

--

Danny J. Lesandrini
dlesandrini@hot mail.com

http://amazecreations.com/datafast

"ZillionDollarS adist" <carlister@gmai l.com> wrote ...[color=blue]
> Hello,
> I'm working at a simple Access '97 + VB4 application, and I ran into a
> terrible problem: something I never modified now gives me a totally
> unwanted "Invalid use of null" error.
> It happens in a Text.LostFocus event, this block:
>
> Do While Not TB6.EOF
> If Year(TB6(0)) = Val(Trim(Text1. Text)) Then
> !!!!!!!!!!!!!He re!!!!!!!!!
> ...
> bla bla bla
> ...
> End If
>
> Where TB6 is a table. The text in Text1 is compared to the Year taken
> out of the first colum in TB6. Checking the database itself, I have
> entries for 2002, 2002, 2003, 2005... and up to this morning everything
> worked so good. Now, it only works ok for 2005, while other values are
> considered to be null and error creeps in.
> Any idea? I really don't know what to do.
> Thanks!
> ZDS
>[/color]

[ZillionDollarSadist]

Re: Outdated help (feat. Access '97 and VB4)

Mmmh, I think I didn't explain myself enough. Text1.Text is never
empty. But Year(TB6(0)) is, and it shouldn't - its value is taken from
an Access table where there's a date in column 0. The fact is, column 0
is never empty. It has lots of entries for 2001, 2002, 2003 and some
for 2005. And only the latter ones, NOW, are considered not null.
Totally absurd since a few hours ago it all worked ok and I didn't
change anything related to this..
ZDS

[Danny J. Lesandrini]

Re: Outdated help (feat. Access '97 and VB4)

Yeah, I guess I completely ignored that part, since it didn't make the
least bit of sense to me. What does that syntax reference? An array?

Year(TB6(0))

So, is TB6() and array and you want element zero? Is it a variant
array? If so, then TB6(0) is NULL and you have to handle that.

Year(Nz(TB6(0), 0))
--

Danny J. Lesandrini
dlesandrini@hot mail.com

http://amazecreations.com/datafast

"ZillionDollarS adist" <carlister@gmai l.com> wrote ...[color=blue]
> Mmmh, I think I didn't explain myself enough. Text1.Text is never
> empty. But Year(TB6(0)) is, and it shouldn't - its value is taken from
> an Access table where there's a date in column 0. The fact is, column 0
> is never empty. It has lots of entries for 2001, 2002, 2003 and some
> for 2005. And only the latter ones, NOW, are considered not null.
> Totally absurd since a few hours ago it all worked ok and I didn't
> change anything related to this..
> ZDS
>[/color]

[ZillionDollarSadist]

Re: Outdated help (feat. Access '97 and VB4)

TB6(0) is column 0 of a database table. And you know what? I created
for error another column, so that there was an empty new column in
position 0...
Thanks anyway for your patience!
ZDS

[David W. Fenton]

Re: Outdated help (feat. Access '97 and VB4)

"Danny J. Lesandrini" <dlesandrini@ho tmail.com> wrote in
news:puOdnUKlLb 3JkQLenZ2dnUVZ_ sudnZ2d@giganew s.com:
[color=blue]
> If Text1.Text is NULL, then how can you trim it?
>
> Try this ...
>
> Val(Trim(Text1. Text & ""))
>
> This will force the NULL to an empty string, which can be trimmed.[/color]

And I believe it's always best to use vbNullString in place of an
empty string.

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

[Danny J. Lesandrini]

Re: Outdated help (feat. Access '97 and VB4)

I've never tried that. (There are many things I've never tried.)
Is it faster or simply more bullet proof?

--
Danny J. Lesandrini



"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote ...[color=blue]
>
> And I believe it's always best to use vbNullString in place of an
> empty string.[/color]

[David W. Fenton]

Re: Outdated help (feat. Access '97 and VB4)

"Danny J. Lesandrini" <dlesandrini@ho tmail.com> wrote in
news:cZydnQxaUd qdsALeRVn-jg@giganews.com :
[color=blue]
> "David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote ...[color=green]
>>
>> And I believe it's always best to use vbNullString in place of an
>> empty string.[/color]
>
> I've never tried that. (There are many things I've never tried.)
> Is it faster or simply more bullet proof?[/color]

Well, the way I see it, there are two issues:

1. it's a pre-declared constant. Memory has already been allocated
for it. If you use "" inline in code, memory has to be allocated for
that. For a single line, well, that's going to be insignificant, but
in extensive loops, or in functions used in queries, it could be
quite significant.

2. it's easier to read code that uses vbNullString than "", because
one might encounter the latter and ask "what happened to the
string?" When you use the named constant, you are declaring very
precisely with no ambiguity that you're using a zero-length string.

--
David W. Fenton http://www.dfenton.com/
dfenton at bway dot net http://www.dfenton.com/DFA/

[Lyle Fairfield]

Re: Outdated help (feat. Access '97 and VB4)

David W. Fenton wrote:[color=blue]
> "Danny J. Lesandrini" <dlesandrini@ho tmail.com> wrote in
> news:cZydnQxaUd qdsALeRVn-jg@giganews.com :
>[color=green]
>> "David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote ...[color=darkred]
>>> And I believe it's always best to use vbNullString in place of an
>>> empty string.[/color]
>> I've never tried that. (There are many things I've never tried.)
>> Is it faster or simply more bullet proof?[/color]
>
> Well, the way I see it, there are two issues:
>
> 1. it's a pre-declared constant. Memory has already been allocated
> for it. If you use "" inline in code, memory has to be allocated for
> that. For a single line, well, that's going to be insignificant, but
> in extensive loops, or in functions used in queries, it could be
> quite significant.
>
> 2. it's easier to read code that uses vbNullString than "", because
> one might encounter the latter and ask "what happened to the
> string?" When you use the named constant, you are declaring very
> precisely with no ambiguity that you're using a zero-length string.[/color]

Run this in Access 2003. Which is fastest? Which is slowest? If you have
time, please report back so that we can confirm our results.

Private Declare Function GetTickCount Lib "kernel32" () As Long
Private Const iterations As Long = 1000000
Sub temp()
Dim r As String
Dim s As String
Dim t As Long
Dim u As Long
r = "P P"

Debug.Print "" = vbNullString
Debug.Print "" = s
Debug.Print s = vbNullString
Debug.Print
Debug.Print VarPtr(vbNullSt ring)
Debug.Print VarPtr(s)
Debug.Print VarPtr("")
Debug.Print
Debug.Print StrPtr(vbNullSt ring)
Debug.Print StrPtr(s)
Debug.Print StrPtr("")
Debug.Print

t = GetTickCount
For u = 1 To iterations
If InStr(r, vbNullString) <> 0 Then
End If
Next u
Debug.Print "vbnullstri ng " & GetTickCount - t

t = GetTickCount
For u = 1 To iterations
If InStr(r, s) <> 0 Then
End If
Next u
Debug.Print "unitialize d string " & GetTickCount - t

t = GetTickCount
For u = 1 To iterations
If InStr(r, "") <> 0 Then
End If
Next u
Debug.Print "literal " & Chr(34) & Chr(34) & " " & GetTickCount - t

End Sub

******

Another strange thing is that "" and vbnullstring have the same varptr,
which indicates that they are the same thing. But they don't have the
same strptr which indicates that they are not the same thing.

--
Lyle Fairfield

[Danny J. Lesandrini]

Re: Outdated help (feat. Access '97 and VB4)

Here's what I got ...

vbnullstring 109
unitialized string 109
literal "" 109

--
Danny J. Lesandrini
dlesandrini@hot mail.com

http://amazecreations.com/datafast

"Lyle Fairfield" <lylefairfield@ aim.com> wrote ...[color=blue]
>
> Run this in Access 2003. Which is fastest? Which is slowest? If you have time, please report back so that we can
> confirm our results.
>[/color]
....[color=blue]
> --
> Lyle Fairfield[/color]

[David W. Fenton]

Re: Outdated help (feat. Access '97 and VB4)

Lyle Fairfield <lylefairfield@ aim.com> wrote in
news:gFOnf.3642 $ph.2618@read2. cgocable.net:
[color=blue]
> Run this in Access 2003. Which is fastest? Which is slowest?[/color]

We went through this before.

In any event, this particular code isn't actually testing use of ""
vs. vbNullstring in a loop. You would want to compare these two:

Dim strTmp As String

For i = 1 To 10000 Step 1
strTmp = cstr(i) & ""
Next i

Dim strTmp As String

For i = 1 To 10000 Step 1
strTmp = cstr(i) & vbNullstring
Next i

Or calling Nz() with "" as the IfNull argument as opposed to
vbNullstring.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

Re: Outdated help (feat. Access '97 and VB4)

"Danny J. Lesandrini" <dlesandrini@ho tmail.com> wrote in
news:2sudnX03NJ LpgT3enZ2dnUVZ_ sKdnZ2d@giganew s.com:
[color=blue]
> Here's what I got ...
>
> vbnullstring 109
> unitialized string 109
> literal "" 109[/color]

Lyle's tests have zilch to do with the point I made.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Lyle Fairfield]

Re: Outdated help (feat. Access '97 and VB4)

David W. Fenton wrote:[color=blue]
> Lyle Fairfield <lylefairfield@ aim.com> wrote in
> news:gFOnf.3642 $ph.2618@read2. cgocable.net:
>[color=green]
>> Run this in Access 2003. Which is fastest? Which is slowest?[/color]
>
> We went through this before.
>
> In any event, this particular code isn't actually testing use of ""
> vs. vbNullstring in a loop. You would want to compare these two:
>
> Dim strTmp As String
>
> For i = 1 To 10000 Step 1
> strTmp = cstr(i) & ""
> Next i
>
> Dim strTmp As String
>
> For i = 1 To 10000 Step 1
> strTmp = cstr(i) & vbNullstring
> Next i
>
> Or calling Nz() with "" as the IfNull argument as opposed to
> vbNullstring.[/color]

You're right.

Option Explicit

Private Declare Function GetTickCount Lib "kernel32" () As Long
Private Const iterations As Long = 1000000
Sub temp()
Dim q As String
Dim r As String
Dim s As String
Dim t As Long
Dim u As Long
r = " P "

Debug.Print "" = vbNullString
Debug.Print "" = s
Debug.Print s = vbNullString
Debug.Print
Debug.Print VarPtr(vbNullSt ring)
Debug.Print VarPtr(s)
Debug.Print VarPtr("")
Debug.Print
Debug.Print StrPtr(vbNullSt ring)
Debug.Print StrPtr(s)
Debug.Print StrPtr("")
Debug.Print

t = GetTickCount
For u = 1 To iterations
q = Val(Trim(r & vbNullString))
Next u
Debug.Print "vbnullstri ng " & GetTickCount - t

t = GetTickCount
For u = 1 To iterations
q = Val(Trim(r & s))
Next u
Debug.Print "unitialize d string " & GetTickCount - t

t = GetTickCount
For u = 1 To iterations
q = Val(Trim(r & ""))
Next u
Debug.Print "literal " & Chr(34) & Chr(34) & " " & GetTickCount - t

End Sub


--
Lyle Fairfield

[rkc]

Re: Outdated help (feat. Access '97 and VB4)

David W. Fenton wrote:[color=blue]
> "Danny J. Lesandrini" <dlesandrini@ho tmail.com> wrote in
> news:2sudnX03NJ LpgT3enZ2dnUVZ_ sKdnZ2d@giganew s.com:
>
>[color=green]
>>Here's what I got ...
>>
>>vbnullstrin g 109
>>unitialized string 109
>>literal "" 109[/color]
>
>
> Lyle's tests have zilch to do with the point I made.[/color]

The only point you have is readability.
I'd say that's good enough even though the name sucks.

[david epsom dot com dot au]

Re: Outdated help (feat. Access '97 and VB4)

> strange thing is that "" and vbnullstring have the same varptr,[color=blue]
> indicates that they are the same thing. But they don't have the strptr
> which indicates that they are not the same thing.[/color]

That is, they both (at different times) use the same dynamically
allocated memory to point to a string space, but the string space
they point to is different.

The traditional explanation is that vbNullString points to
an existing null string space, but "" points to a dynamically
created null string space.

I can't see it myself: defining "" as vbNullString is such
an obvious step that I find it hard to believe they are
interpreted differently by the interpreter.

(david)


"Lyle Fairfield" <lylefairfield@ aim.com> wrote in message
news:gFOnf.3642 $ph.2618@read2. cgocable.net...[color=blue]
> David W. Fenton wrote:[color=green]
>> "Danny J. Lesandrini" <dlesandrini@ho tmail.com> wrote in
>> news:cZydnQxaUd qdsALeRVn-jg@giganews.com :[color=darkred]
>>> "David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote ...
>>>> And I believe it's always best to use vbNullString in place of an
>>>> empty string.
>>> I've never tried that. (There are many things I've never tried.)
>>> Is it faster or simply more bullet proof?[/color]
>>
>> Well, the way I see it, there are two issues:
>>
>> 1. it's a pre-declared constant. Memory has already been allocated
>> for it. If you use "" inline in code, memory has to be allocated for
>> that. For a single line, well, that's going to be insignificant, but
>> in extensive loops, or in functions used in queries, it could be
>> quite significant. 2. it's easier to read code that uses vbNullString
>> than "", because
>> one might encounter the latter and ask "what happened to the
>> string?" When you use the named constant, you are declaring very
>> precisely with no ambiguity that you're using a zero-length string.[/color]
>
> Run this in Access 2003. Which is fastest? Which is slowest? If you have
> time, please report back so that we can confirm our results.
>
> Private Declare Function GetTickCount Lib "kernel32" () As Long
> Private Const iterations As Long = 1000000
> Sub temp()
> Dim r As String
> Dim s As String
> Dim t As Long
> Dim u As Long
> r = "P P"
>
> Debug.Print "" = vbNullString
> Debug.Print "" = s
> Debug.Print s = vbNullString
> Debug.Print
> Debug.Print VarPtr(vbNullSt ring)
> Debug.Print VarPtr(s)
> Debug.Print VarPtr("")
> Debug.Print
> Debug.Print StrPtr(vbNullSt ring)
> Debug.Print StrPtr(s)
> Debug.Print StrPtr("")
> Debug.Print
>
> t = GetTickCount
> For u = 1 To iterations
> If InStr(r, vbNullString) <> 0 Then
> End If
> Next u
> Debug.Print "vbnullstri ng " & GetTickCount - t
>
> t = GetTickCount
> For u = 1 To iterations
> If InStr(r, s) <> 0 Then
> End If
> Next u
> Debug.Print "unitialize d string " & GetTickCount - t
>
> t = GetTickCount
> For u = 1 To iterations
> If InStr(r, "") <> 0 Then
> End If
> Next u
> Debug.Print "literal " & Chr(34) & Chr(34) & " " & GetTickCount - t
>
> End Sub
>
> ******
>
> Another strange thing is that "" and vbnullstring have the same varptr,
> which indicates that they are the same thing. But they don't have the same
> strptr which indicates that they are not the same thing.
>
> --
> Lyle Fairfield[/color]