Guid's and Nullness

[Roland Alden]

I have the following line of code in a Form_Current:

If Form.[dog] <> Null Then
Let [name-call].Text = ""
End If

[dog] is a control bound to a Guid in the underlying table. If the Guid is
in fact Null the code works and the Let [name-call].Text = "" branch will
execute. However, if [dog] holds some legit value I get an error 13 "type
mismatch".

I notice that If Form.[dog] Is Null will always fail with an "Object
Required" error. This leads me to believe that somehow the test for nullness
is being performed by the underlying "object" which, if null, is not there.
Tricks like using If Form.[dog].Value <> Null don't work either (it still
produces a type mismatch in the non null case).

Efforts to use StringFromGUID fail as well.

What am I missing? My objective is to set some other control [name-call] to
blank if this field is non-Null.

Another objective is to have that happen on the form only; I don't want the
blank values to be written to the underlying database. I made [name-call] be
a "Locked" field for "display only" but apparently this does not apply to VB
code since if I programatically Let [name-call].Text = "foo" then the record
will be marked dirty and get written. I just want the screen to be
uncluttered in this case so want my changes to not affect the underlying
data.

 

[Roland Alden]

I should add that I am open to solving this problem in SQL with a column
expression that will test the uniqueidentifier for nullness and return
either the dog's name or an empty or null string to use in beautifying my
form. However, I am having just as much trouble in SQL with Guids. For
example

[name] + ISNULL(uid,'')

doesn't work because if uid is not null it is treated as a 16 byte thing and
+ is interpreted as addition on a big number instead of string
concatenation.

What I need is some way, in SQL, to test for nullness of a Guid which can
then be factored into a string expression to either select a string from
another column who's type is unambiguously string, or substitute an empty
string as the case may be. Ideally this would be an expression I can use in
the query and not have to move out into a stored procedure.

 

[Rob Oldfield]

Case? Convert? If not, then you're going to be best off asking the
question in an SQL group.

 

[david epsom dot com dot au]

In VBA, code like this:

If IsNull(Form.[dog]) Then

Alternatively, use the string concatenation operator
to force string conversion before testing:

If (Me.dog & "" = "") Then

In VBA, the IS operator takes an Object subject and
target: it is not equivalent to the SQL IS keyword.

If (obj IS other_obj) Then

Form is an object: Form.dog and Null are not objects,
which is why you received the "Object Required" error.


This line is not normally valid in a Microsoft Access
Form Current Event:

Let [name-call].Text = ""

In Microsoft Access, the .Text property is only available
when a control has focus. The .Text property refers to the
window display text of the control: when the control does
not have focus, the window display text is always constrained
to be the .Value of the control. The .Value of the control
can be changed at any time while the form is loaded.
Explicit use of the Let keyword is a matter of style, but it
is usually omitted.



(david)

 

[Roland Alden]

Thank-you. This sort of works but there is a new problem:

Private Sub Form_Load()
If IsNull(Form.[uid]) = False Then
[name-call].ControlSource = ""
End If
End Sub

This evaluates [uid] nicely and I "discovered" by trial and error that
[name-call].ControlSource = "" will cause the [name-call] control to display
nothing instead of the value from the underlying table and this prevents the
value from being written back to the database as well.

However, it seems this whole thing only gets executed once, not once per
row. This is a Sheet View by the way. This is, of course, Not What We Want


I ran into this somewhere else too so perhaps someone can tell me what
events are called for each row of a sheet or continuous forms page where the
controls context of each execution is not the one underlying model control
but rather the row-instance of that control?

 

[david epsom dot com dot au]

Use '&' for concatenation instead of '+'

'+' is the old style BASIC concatenation operator: it
propagates nulls and does implicit type conversion.
'&' just does concatenation.
[name] & [uid]

In Access, use nz instead of ISNULL
[name] + nz([uid],"")

In Jet SQL, not using Access, use IIF instead of ISNULL
[name] + IIF(isnull([uid]),"",[uid])

In standard SQL, to avoid using functions like ISNULL,
create separate SELECT queries with IS NULL and NOT IS NULL
criteria, and UNION the two queries.

Also, GUID's are more trouble than they are worth in Access.
They are used for replication, but any other use should be
avoided. What are you trying to do?


(david)