Help on getting a result in a report

  • Thread starter Thread starter SFCSCOTT
  • Start date Start date
S

SFCSCOTT

I have a query built that figures percentage. I want my report to
print out P1 if between 90 and 100 percent, or P2 if between 89 and 80
percent, or P3 if between 80 and lower. I get a compile error on this
VB code. Percent available is the Field Name from the query. What am
I doing wrong? Please help!




Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Select Case Percent Available
Case " ", ">=90%"
Let [Text5] = "P1"
Case ">80%<90"
Let [Text5] = "P2"
Case Else
Let [Text5] = "P3"
End If
End Sub
 
You must use square brackets around the name if it contains spaces:

Is [Percent Available] a Number field or a Text field? If Text, it will be
evaluated character-by-character rather than by numeric value. So 2% will be
treated as greater than 100% because the first character (2) is greater than
the first character of 100 (1.)

If it is a Number field, what is its Field Size property when you look at it
in table design? If Double, formatted as Percent, then 90% will actually be
the value 0.90 (since 100% = 1.) The example code below assumes this is what
you have. If it is actually an integer type, the code needs changing.

Assuming it's a Number field, the case values need to be numeric too. Select
Case will choose the first one that matches and ignore the others, so you
don't need to exclude the > 0.9 again in the 2nd case.

Try:
Select Case Me.[Percent Available]
Case >= 0.9
Me.[Text5] = "P1"
Case >= 0.8
Me.[Text5] = "P2"
Case >= 0
Me.[Text5] = "P3"
Case Else
Me.[Text5] = "N/A"
End If
End Sub

The Case Else here handles the Null, since it doesn't match any of the other
values.

If you wanted to do this without code, you could use the Switch() function
in the Control Source property of Text5. The expression would be something
like this (all on one line):
=Switch([Percent Available] >= 0.9, "P1",
[Percent Available] >= 0.8, "P2",
[Percent Available] >= 0, "P3", True, "N/A")

The Switch() looks at each expression to see if it's true, and prints the
appropriate value. The final expression-pair always evalutes to True (since
True is always True), and so is used if all the other cases don't match (as
happens with Null.)
 
You must use square brackets around the name if it contains spaces:

Is [Percent Available] a Number field or a Text field? If Text, it will be
evaluated character-by-character rather than by numeric value. So 2% willbe
treated as greater than 100% because the first character (2) is greater than
the first character of 100 (1.)

If it is a Number field, what is its Field Size property when you look atit
in table design? If Double, formatted as Percent, then 90% will actually be
the value 0.90 (since 100% = 1.) The example code below assumes this iswhat
you have. If it is actually an integer type, the code needs changing.

Assuming it's a Number field, the case values need to be numeric too. Select
Case will choose the first one that matches and ignore the others, so you
don't need to exclude the > 0.9 again in the 2nd case.

Try:
    Select Case Me.[Percent Available]
        Case >= 0.9
            Me.[Text5] = "P1"
        Case >= 0.8
            Me.[Text5] = "P2"
        Case >= 0
            Me.[Text5] = "P3"
        Case Else
            Me.[Text5] = "N/A"
    End If
End Sub

The Case Else here handles the Null, since it doesn't match any of the other
values.

If you wanted to do this without code, you could use the Switch() function
in the Control Source property of Text5. The expression would be something
like this (all on one line):
=Switch([Percent Available] >= 0.9, "P1",
    [Percent Available] >= 0.8, "P2",
    [Percent Available] >= 0, "P3", True, "N/A")

The Switch() looks at each expression to see if it's true, and prints the
appropriate value. The final expression-pair always evalutes to True (since
True is always True), and so is used if all the other cases don't match (as
happens with Null.)

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.




I have a query built that figures percentage.  I want my report to
print out P1 if between 90 and 100 percent, or P2 if between 89 and 80
percent, or P3 if between 80 and lower.  I get a compile error on this
VB code.  Percent available is the Field Name from the query.  Whatam
I doing wrong?   Please help!
Click to expand...
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
   Select Case Percent Available
       Case " ", ">=90%"
           Let [Text5] = "P1"
       Case ">80%<90"
           Let [Text5] = "P2"
       Case Else
           Let [Text5] = "P3"
   End If
End Sub- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

I got another compile error. Percent Available comes out of a query
not a table. I used the Switch() and all it printed out was #NA.
what am I doing wrong. Thanks for the help.
 
Perhaps Access is not understanding the [Percent Available] field as
numeric?

What is the expression in your query?

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

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

: I got another compile error. Percent Available comes out of a query
: not a table. I used the Switch() and all it printed out was #NA.
: What am I doing wrong. Thanks for the help.
Click to expand...

You must use square brackets around the name if it contains spaces:

Is [Percent Available] a Number field or a Text field? If Text, it will be
evaluated character-by-character rather than by numeric value. So 2% will
be
treated as greater than 100% because the first character (2) is greater
than
the first character of 100 (1.)

If it is a Number field, what is its Field Size property when you look at
it
in table design? If Double, formatted as Percent, then 90% will actually
be
the value 0.90 (since 100% = 1.) The example code below assumes this is
what
you have. If it is actually an integer type, the code needs changing.

Assuming it's a Number field, the case values need to be numeric too.
Select
Case will choose the first one that matches and ignore the others, so you
don't need to exclude the > 0.9 again in the 2nd case.

Try:
Select Case Me.[Percent Available]
Case >= 0.9
Me.[Text5] = "P1"
Case >= 0.8
Me.[Text5] = "P2"
Case >= 0
Me.[Text5] = "P3"
Case Else
Me.[Text5] = "N/A"
End If
End Sub

The Case Else here handles the Null, since it doesn't match any of the
other
values.

If you wanted to do this without code, you could use the Switch() function
in the Control Source property of Text5. The expression would be something
like this (all on one line):
=Switch([Percent Available] >= 0.9, "P1",
[Percent Available] >= 0.8, "P2",
[Percent Available] >= 0, "P3", True, "N/A")

The Switch() looks at each expression to see if it's true, and prints the
appropriate value. The final expression-pair always evalutes to True
(since
True is always True), and so is used if all the other cases don't match
(as
happens with Null.)




I have a query built that figures percentage. I want my report to
print out P1 if between 90 and 100 percent, or P2 if between 89 and 80
percent, or P3 if between 80 and lower. I get a compile error on this
VB code. Percent available is the Field Name from the query. What am
I doing wrong? Please help!
Click to expand...
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Select Case Percent Available
Case " ", ">=90%"
Let [Text5] = "P1"
Case ">80%<90"
Let [Text5] = "P2"
Case Else
Let [Text5] = "P3"
End If
End Sub- Hide quoted text -
Click to expand...
Click to expand...
\
 
Perhaps Access is not understanding the [Percent Available] field as
numeric?

What is the expression in your query?

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


: I got another compile error.  Percent Available comes out of a query
: not a table.  I used the Switch() and all it printed out was #NA.
: What am I doing wrong.  Thanks for the help.

You must use square brackets around the name if it contains spaces:
Click to expand...
Is [Percent Available] a Number field or a Text field? If Text, it willbe
evaluated character-by-character rather than by numeric value. So 2% will
be
treated as greater than 100% because the first character (2) is greater
than
the first character of 100 (1.)
Click to expand...
If it is a Number field, what is its Field Size property when you look at
it
in table design? If Double, formatted as Percent, then 90% will actually
be
the value 0.90 (since 100% = 1.) The example code below assumes this is
what
you have. If it is actually an integer type, the code needs changing.
Click to expand...
Assuming it's a Number field, the case values need to be numeric too.
Select
Case will choose the first one that matches and ignore the others, so you
don't need to exclude the > 0.9 again in the 2nd case.
Click to expand...
Try:
Select Case Me.[Percent Available]
Case >= 0.9
Me.[Text5] = "P1"
Case >= 0.8
Me.[Text5] = "P2"
Case >= 0
Me.[Text5] = "P3"
Case Else
Me.[Text5] = "N/A"
End If
End Sub
Click to expand...
The Case Else here handles the Null, since it doesn't match any of the
other
values.
Click to expand...
If you wanted to do this without code, you could use the Switch() function
in the Control Source property of Text5. The expression would be something
like this (all on one line):
=Switch([Percent Available] >= 0.9, "P1",
[Percent Available] >= 0.8, "P2",
[Percent Available] >= 0, "P3", True, "N/A")
Click to expand...
The Switch() looks at each expression to see if it's true, and prints the
appropriate value. The final expression-pair always evalutes to True
(since
True is always True), and so is used if all the other cases don't match
(as
happens with Null.)
Click to expand...
news:[email protected]...
Click to expand...
I have a query built that figures percentage. I want my report to
print out P1 if between 90 and 100 percent, or P2 if between 89 and 80
percent, or P3 if between 80 and lower. I get a compile error on this
VB code. Percent available is the Field Name from the query. What am
I doing wrong? Please help!
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Select Case Percent Available
Case " ", ">=90%"
Let [Text5] = "P1"
Case ">80%<90"
Let [Text5] = "P2"
Case Else
Let [Text5] = "P3"
End If
End Sub- Hide quoted text -
Click to expand...
Click to expand...

\- Hide quoted text -

- Show quoted text -
Click to expand...

I GOT IT! It worked. Thank you very much for your help.
 
Back
Top