HELP! Conditional formatting in an access data page, Null values

N

New Guy

I have developed an Access Data Page (access2003) and I need to do
conditional formating in a page depending on the scores from the fields in X
table. I used the script from this MSDN article,



http://msdn2.microsoft.com/en-us/library/aa139969(office.10).aspx#odc_customizecontrols_cf



It works, BUT when there are Null values, I get a "Type mismatch 'Cint'"
error message. I know that this is because it cannot see the Null's as
integers (or so I think). The X table has tons of Null values and it is
supposed to have them. I have tried everything, researched all over the net,
and I cannot find an answer.



Here is my code:



<SCRIPT language=vbscript event=DataPageComplete(dscei) for=MSODSC>
<!--
' Highlight items according to their score.
dim sect
dim dscconst
dim bandHTML

' Check that the event fired for the DataPage in the GST Effects Table
GroupLevel.
If (dscei.DataPage.GroupLevel.RecordSource = "X Table") Then
Set dscconst = MSODSC.Constants
Set sect = dscei.DataPage.FirstSection

' Go through the sections of the event's DataPage object.
Do
' Ignore all sections except the header section.
If (sect.Type = dscconst.sectTypeHeader) Then
Set bandHTML = sect.HTMLContainer

' Change the text IN THE EFFECTS TABLE to
red/orange/yellow/green depending on Effects Assessment scale.
If (Cint(bandHTML.children("XValue").Innertext) <= 20) Then
bandHTML.children("WeightedValue").style.backgroundColor =
"red"
End If
If (Cint(bandHTML.children("XValue").Innertext) >= 21) Then
bandHTML.children("XValue").style.backgroundColor = "#ff9900"
End If
If (Cint(bandHTML.children("XValue").Innertext) >= 50) Then
bandHTML.children("XValue").style.backgroundColor = "#ffff33"
End If
If (Cint(bandHTML.children("XValue").Innertext) >= 75) Then
bandHTML.children("WeightedValue").style.backgroundColor =
"#339933"
End If
End If
Set sect = sect.NextSibling
Loop until (sect is nothing)
End If
-->
</SCRIPT>





I tried using: If
IsNull(bandHTML.children("XValue").Innertext) Then

bandHTML.children("XValue").style.backgroundColor = "#ffffff"



and it does not work, I also used .Value, and NOTHING... I have tried 10
types of combinations to get it to recognize the Nulls and I have not had
luck. I am relatively new at this, I would really appreciate your help!



New Guy


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...8535&dg=microsoft.public.access.modulesdaovba
 
K

Klatuu

Try using the Nz function to resolve this problem:

If (Cint(Nz(bandHTML.children("XValue").Innertext,0)) <= 20) Then

That will change a Null value to 0
 
N

New Guy

I tryed using Nz and it did not work. Perhaps I am not doing this right. Do I
have to use the Nz for each If-Then line? or only one? and, does the Nz
inputs a "0" in the field, or does it just "see it" as a zero?

Is there any other way?

Thank you for your help, I have not found anyone who can fix this problem.
 
K

Klatuu

The Nz will return whatever is in the second argument, in this case 0, when
ever it find a Null in the object identified in the first argument. You have
to enclose each value with an Nz. for example:
Assume x = 5 and y = Null

=X + Y will return Null
= Nz(x + y,0) will return Null - Note the Nz is working on x + y and x and
then y
=Nz(x,0) + Nz(y,0) will return 5
 
N

New Guy

Klatuu,

For some reason, this is not working for me. Is there any other way to
"omit" a null value? The Nz does not seem to place a zero on the data table.
Question again: do I have to place the Nz for every "if-then" statement?

Thank you so much for your help!
 
K

Klatuu

Whether or not it updates the table field with a 0 depends on how you code it.
Yes, you must use the Nz function for each element in the statement.
If you could post the code as you have it, perhaps we can figure out what
you need to do to make it work.
 
N

New Guy

Here is my code exactly:

<SCRIPT language=vbscript event=DataPageComplete(dscei) for=MSODSC>
<!--
' Highlight items according to their score.
dim sect
dim dscconst
dim bandHTML

' Check that the event fired for the DataPage in the GST Effects Table
GroupLevel.
If (dscei.DataPage.GroupLevel.RecordSource = "GST Effects Table") Then
Set dscconst = MSODSC.Constants
Set sect = dscei.DataPage.FirstSection

' Go through the sections of the event's DataPage object.
Do
' Ignore all sections except the header section.
If (sect.Type = dscconst.sectTypeHeader) Then
Set bandHTML = sect.HTMLContainer

' Change the text IN THE EFFECTS TABLE to
red/orange/yellow/green depending on Effects Assessment scale.
If (Cint(Nz(bandHTML.children("WeightedValue").Innertext,0)) <=
20) Then
bandHTML.children("WeightedValue").style.backgroundColor =
"red"
End If
If (Cint(Nz(bandHTML.children("WeightedValue").Innertext,0)) >=
21) Then
bandHTML.children("WeightedValue").style.backgroundColor =
"#ff9900"
End If
If (Cint(Nz(bandHTML.children("WeightedValue").Innertext,0)) >=
50) Then
bandHTML.children("WeightedValue").style.backgroundColor =
"#ffff33"
End If
If (Cint(Nz(bandHTML.children("WeightedValue").Innertext,0)) >=
75) Then
bandHTML.children("WeightedValue").style.backgroundColor =
"#339933"
End If
End If
Set sect = sect.NextSibling
Loop until (sect is nothing)
End If
-->
</SCRIPT>

Thank you again!
 
K

Klatuu

I am not all that familiar with data access pages, but I think that if you
want to replace a Null with a zero, you will have to assign the value. If it
works the same way as a form, this would do it.

bandHTML.children("WeightedValue").Innertext =
Nz(bandHTML.children("WeightedValue").Innertext,0)
 
N

New Guy

Do I type it like this?
If (bandHTML.children("WeightedValue").Innertext =
Nz(bandHTML.children("WeightedValue").Innertext,0))
??
 
K

Klatuu

No, you type it as I wrote it.
What happens is if it is not null, it stays the same. If it is null, it
changes to 0
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access 2003 Data page Question 0
null values 3
VBA in Word Opens Access Object/Form How to get Values Back? 1
2 Comboboxes produce range of values in third combobox 2
Select from a table in an IF statement 3
Conditional Values in Reports 1
Help with code --> Invlaid use of Null???? 2
data access page search code 1

Top