What's wrong with this expression?

  • Thread starter Thread starter Jeff Heikkinen
  • Start date Start date
J

Jeff Heikkinen

What's wrong with this expression? It looks fine to me, and it
definitely has the same number of '(' and ')' characters, yet Access
tells me it has an extra ')'.

IIf ( IsNull ( [Types] ) , Null , "[" & [Types] & "]" )

(The intention: if the Types field in the source table has nothing, it
prints nothing, otherwise it prints the Types in square brackets).
 
Jeff Heikkinen, worshipped by llamas the world over, wrote...
What's wrong with this expression? It looks fine to me, and it
definitely has the same number of '(' and ')' characters, yet Access
tells me it has an extra ')'.

IIf ( IsNull ( [Types] ) , Null , "[" & [Types] & "]" )

(The intention: if the Types field in the source table has nothing, it
prints nothing, otherwise it prints the Types in square brackets).
Click to expand...


Sorry, just realized it's not clear what this has to do with reports. I
got the error when trying to design a report; besides which it wasn't
clear to me which newsgroup this *is* on topic for.
 
Did you include the equal sign at the start?

Did you make sure this control does not have the same name as one of the
fields (such as Types)?

Try:
="[" + [Types] + "]"
or - if Types is a Number type field:
="[" + Str([Types]) + "]"

There is a subtle difference between & and + as concatenation operators:
"A" & Null => "A"
"A" + Null => Null

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Jeff Heikkinen said:
Jeff Heikkinen, worshipped by llamas the world over, wrote...
What's wrong with this expression? It looks fine to me, and it
definitely has the same number of '(' and ')' characters, yet Access
tells me it has an extra ')'.

IIf ( IsNull ( [Types] ) , Null , "[" & [Types] & "]" )

(The intention: if the Types field in the source table has nothing, it
prints nothing, otherwise it prints the Types in square brackets).
Click to expand...


Sorry, just realized it's not clear what this has to do with reports. I
got the error when trying to design a report; besides which it wasn't
clear to me which newsgroup this *is* on topic for.
Click to expand...
 
Allen Browne, worshipped by llamas the world over, wrote...
Did you include the equal sign at the start?

Did you make sure this control does not have the same name as one of the
fields (such as Types)?
Click to expand...

I'm not sure what you mean. It's *SUPPOSED* to be the name of one of
the fields. I just took what the Expression Builder gave me; I took
that right off the list of fields it offered and that's what it typed
for me. I just assumed the tool built right into the program would have
the right syntax programmed into it...
Try:
="[" + [Types] + "]"
or - if Types is a Number type field:
="[" + Str([Types]) + "]"

There is a subtle difference between & and + as concatenation operators:
"A" & Null => "A"
"A" + Null => Null
Click to expand...

I still get "#Error" for every single instance of the expression, even
with that change.
 
I am getting a similar problem. See my formulas below. (And, yes, I did
remember to put the equal sign in.) I just attempt to change the name of
one of the controls from "strContactPhone" to "textContactPhone". (The
prefixes are a habit that I picked up writing MFC code.) The field names in
the second expression did not conflict as I combined those fields.

=IIf(IsEmpty([textContactPhone]),[strMainPhone],[textContactPhone])
=[strAddress1]+", "+IIf(IsEmpty([strAddress2]),[strAddress2]+",
","")+[strCity]+", "+[strState]+" "+[strZip]
 
I should have mentioned that I am using Access 2002.
----------
Will Pittenger
E-Mail: mailto:[email protected]
All mail filtered by Qurb (www.qurb.com)
Will Pittenger said:
I am getting a similar problem. See my formulas below. (And, yes, I did
remember to put the equal sign in.) I just attempt to change the name of
one of the controls from "strContactPhone" to "textContactPhone". (The
prefixes are a habit that I picked up writing MFC code.) The field names
in the second expression did not conflict as I combined those fields.

=IIf(IsEmpty([textContactPhone]),[strMainPhone],[textContactPhone])
=[strAddress1]+", "+IIf(IsEmpty([strAddress2]),[strAddress2]+",
","")+[strCity]+", "+[strState]+" "+[strZip]

----------
Will Pittenger
E-Mail: mailto:[email protected]
All mail filtered by Qurb (www.qurb.com)
Jeff Heikkinen said:
What's wrong with this expression? It looks fine to me, and it
definitely has the same number of '(' and ')' characters, yet Access
tells me it has an extra ')'.

IIf ( IsNull ( [Types] ) , Null , "[" & [Types] & "]" )

(The intention: if the Types field in the source table has nothing, it
prints nothing, otherwise it prints the Types in square brackets).
Click to expand...
Click to expand...
 
Open the report in design view.
Right-click the text box, and choose Properties.
On the Other tab of the Properties box, you will see the Name property.

If the text box is bound to an expresssion (i.e. its Control Source starts
with "equals"), its Name property must not be the same as the name of any of
the fields in the report's RecordSource. Access gets confused if it has the
same Name as a field, but is bound to somehing else.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Jeff Heikkinen said:
Allen Browne, worshipped by llamas the world over, wrote...
Did you include the equal sign at the start?

Did you make sure this control does not have the same name as one of the
fields (such as Types)?
Click to expand...

I'm not sure what you mean. It's *SUPPOSED* to be the name of one of
the fields. I just took what the Expression Builder gave me; I took
that right off the list of fields it offered and that's what it typed
for me. I just assumed the tool built right into the program would have
the right syntax programmed into it...
Try:
="[" + [Types] + "]"
or - if Types is a Number type field:
="[" + Str([Types]) + "]"

There is a subtle difference between & and + as concatenation operators:
"A" & Null => "A"
"A" + Null => Null
Click to expand...

I still get "#Error" for every single instance of the expression, even
with that change.
Click to expand...
 
I also should mention that only the phone expression resulted in #Error.
The Address expression works, but acts like the strAddress2 field is always
blank or null. After my first post, I noticed another thread where a user
was getting back #Error from an IIF expression. That answer said to ensure
that all fields in the expression were represented by a control. So I
created new controls (and cluttered the layout up) for the phone. That
expression now returns only empty strings. The other expression was working
even with out the fields -- until it referenced strAddress2.

BTW: Even though the first expression refers to textContactPhone, all my
tests were with it reading "strContactPhone".
----------
Will Pittenger
E-Mail: mailto:[email protected]
All mail filtered by Qurb (www.qurb.com)
Will Pittenger said:
I should have mentioned that I am using Access 2002.
----------
Will Pittenger
E-Mail: mailto:[email protected]
All mail filtered by Qurb (www.qurb.com)
Will Pittenger said:
I am getting a similar problem. See my formulas below. (And, yes, I did
remember to put the equal sign in.) I just attempt to change the name of
one of the controls from "strContactPhone" to "textContactPhone". (The
prefixes are a habit that I picked up writing MFC code.) The field names
in the second expression did not conflict as I combined those fields.

=IIf(IsEmpty([textContactPhone]),[strMainPhone],[textContactPhone])
=[strAddress1]+", "+IIf(IsEmpty([strAddress2]),[strAddress2]+",
","")+[strCity]+", "+[strState]+" "+[strZip]

----------
Will Pittenger
E-Mail: mailto:[email protected]
All mail filtered by Qurb (www.qurb.com)
Jeff Heikkinen said:
What's wrong with this expression? It looks fine to me, and it
definitely has the same number of '(' and ')' characters, yet Access
tells me it has an extra ')'.

IIf ( IsNull ( [Types] ) , Null , "[" & [Types] & "]" )

(The intention: if the Types field in the source table has nothing, it
prints nothing, otherwise it prints the Types in square brackets).
Click to expand...
Click to expand...
Click to expand...
 
Is it possible to have a text box shaped like a rectangle with a corner cut
out of it? I have a field where the rectangle is just big enough for two
lines. However, that includes the space for the field label. If I position
the label over the text box, will that force the text in the text box over?
----------
Will Pittenger
E-Mail: mailto:[email protected]
All mail filtered by Qurb (www.qurb.com)
Will Pittenger said:
I should have mentioned that I am using Access 2002.
----------
Will Pittenger
E-Mail: mailto:[email protected]
All mail filtered by Qurb (www.qurb.com)
Will Pittenger said:
I am getting a similar problem. See my formulas below. (And, yes, I did
remember to put the equal sign in.) I just attempt to change the name of
one of the controls from "strContactPhone" to "textContactPhone". (The
prefixes are a habit that I picked up writing MFC code.) The field names
in the second expression did not conflict as I combined those fields.

=IIf(IsEmpty([textContactPhone]),[strMainPhone],[textContactPhone])
=[strAddress1]+", "+IIf(IsEmpty([strAddress2]),[strAddress2]+",
","")+[strCity]+", "+[strState]+" "+[strZip]

----------
Will Pittenger
E-Mail: mailto:[email protected]
All mail filtered by Qurb (www.qurb.com)
Jeff Heikkinen said:
What's wrong with this expression? It looks fine to me, and it
definitely has the same number of '(' and ')' characters, yet Access
tells me it has an extra ')'.

IIf ( IsNull ( [Types] ) , Null , "[" & [Types] & "]" )

(The intention: if the Types field in the source table has nothing, it
prints nothing, otherwise it prints the Types in square brackets).
Click to expand...
Click to expand...
Click to expand...
 
Allen Browne, worshipped by llamas the world over, wrote...
Open the report in design view.
Right-click the text box, and choose Properties.
On the Other tab of the Properties box, you will see the Name property.

If the text box is bound to an expresssion (i.e. its Control Source starts
with "equals"), its Name property must not be the same as the name of any of
the fields in the report's RecordSource. Access gets confused if it has the
same Name as a field, but is bound to somehing else.
Click to expand...

I see. Good suggestion, but it didn't help in my case. What did was
using "IsEmpty" instead of "IsNull" <embarassed>

I'm having a few other issues, but I'll either figure them out on my own
or post them elsewhere.
 
Thanks for reply. We always like to know what the solution was.

A field will be Null, not Empty.
Empty is the value that a Variant is initialized to.
Guess Types must be receiving its value from an unassigned variant.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

"Jeff Heikkinen" replied:
 
If a text field allows both null and zero length strings, what should we
test for when the value is blank on screen?
 
Allen Browne, worshipped by llamas the world over, wrote...
Thanks for reply. We always like to know what the solution was.

A field will be Null, not Empty.
Empty is the value that a Variant is initialized to.
Guess Types must be receiving its value from an unassigned variant.
Click to expand...

Actually, I'm not sure anymore *what* worked, but something did. It's
using "IsNull" again - not sure how that happened - and otherwise has
the EXACT same expression as before, but now it's evaluating correctly.
Before, both IsNull(blah) and IsEmpty(blah) were coming out false every
time in some fields and giving errors in others. I resized and
rearranged some boxes, and lo and behold, all these expressions started
behaving the way I expected. Weird!
 
Hi Will

If a text field has its Allow Zero Length (AZL) property set to Yes and its
Required property set to No, then you will need to test for both.

In the Criteria row of query design, that would be
Is Null Or ""

In a SQL statement:
"SELECT ... WHERE (MyField Is Null) OR (MyField = """");"

In VBA code, there are several variations such as:
If Len(Nz(MyField, vbNullString)) = 0 Then
If MyField & vbNullString = vbNullString Then

Of course the underlying question would be why you want to set AZL on in the
first place. The cases where this would be a useful thing to do represent
less than 1% of your fields.
 
Would I count the code in my forms and reports as following your VBA sample?
I did not create an application to go with them. As to why, that is
Access's default for text fields.
 
Everywhere - whether counting, specifying criteria, matching, filtering, ...
you have to handle both types (null and zero-length string) unless you close
the door to this.

Up until 2 versions ago, all versions of Access had AZL defaulting to No.
Now the default is inconsistent: you get Yes through the interface, but
different results if you create a field through DAO, ADOX, DDL, etc.

What we do is run the code below. It goes through all the non-system tables
in your database, and sets AllowZeroLength to No for all fields.

Function FixZLS()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property
Const conPropName = "AllowZeroLength"
Const conPropValue = False

Set db = CurrentDb()
For Each tdf In db.TableDefs
If (tdf.Attributes And dbSystemObject) = 0 Then
Debug.Print tdf.Name
For Each fld In tdf.Fields
If fld.Properties(conPropName) Then
Debug.Print tdf.Name & "." & fld.Name
fld.Properties(conPropName) = conPropValue
End If
Next
End If
Next

Set prp = Nothing
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Function
 
Where do you put this code? I have no application. Just 1 form and 2
reports. Also, will changing the ZLS take care of the records that have
those fields set to empty strings?
 
The code will not handle existing data. To do that, you would need to run an
Update query on every text field of every table, to replace ZLS with Null.

If you want to try it anyway:
1. Click the Modules tab of the Database window.

2. Click New. Access opens a new code window.

3. Paste in the code.

4. Choose Compile on the Debug menu to make sure Access understands the
code. If it does not, check your references as explained here:
http://members.iinet.net.au/~allenbrowne/ser-38.html

5. Press Ctrl+G to open the Immediate Window.

6. In the Immediate window, enter:
? FixZLS()
 
I did the update once for each field. I could not get the FixZLS to compile
(DAO was not declared). So I just changed the ZLS option manually as it was
only one table with just a few fields. No effect.
 
Forget the previous post. I was still using IsEmpty instead of IsNull.
Thank you for your help.
 
Back
Top