Re: Help on getting a result in a report

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.)

--
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.

"SFCSCOTT" <ssgheeren_1999@xxxxxxxxx> wrote in message
news:d2c74404-4899-4026-8fdb-a5a433c216fa@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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

.

Relevant Pages

  • Re: Help on getting a result in a report
    ... treated as greater than 100% because the first character is greater than ... If you wanted to do this without code, you could use the Switch() function ... >I have a query built that figures percentage. ... > Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) ...
    (microsoft.public.access.reports)
  • Re: How do I set up two indexes in one document?
    ... I did try using more than one character in the switch, ... ignores anything after the first character, though, so both ... please post all follow-ups to the newsgroup so ... You need to include an \f switch in each index entry field, ...
    (microsoft.public.word.application.errors)
Loading