Re: Help on getting a result in a report

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.

"SFCSCOTT" <ssgheeren_1999@xxxxxxxxx> wrote in message
news:a95e2f5d-dc2a-4b7e-9e90-8eb34411685e@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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

On Jul 28, 11:07 pm, "Allen Browne" <AllenBro...@xxxxxxxxxxxxxx>
wrote:
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.)

"SFCSCOTT" <ssgheeren_1...@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- Hide quoted text -
\

.

Relevant Pages

  • Re: Help on getting a result in a report
    ... So 2% will be treated as greater than 100% because the first character is greater than the first character of 100 ... If you wanted to do this without code, you could use the Switch() function in the Control Source property of Text5. ... FormatCount As Integer) ...
    (microsoft.public.access.reports)
  • Re: Delete query unable to delete records from table. Fix how?
    ... Or are you executing an Append query to add the new records? ... Paste your subquery (I did fix several brackets. ... Switch to design view. ... Switch to datasheet view. ...
    (microsoft.public.access.queries)
  • Re: 100% CPU Util on Simple Update Query
    ... If I read your query correctly, you are updating each column with the ... each column to the value of that column itself if switch is unequal to -1. ... That means you are wasting a lot of time, updating rows with the original ... table scan (if the table has a clustered index, ...
    (microsoft.public.sqlserver.programming)
  • RE: How to update this...
    ... I'm excited that I was close thinking it might be a switch thing. ... > Hi, Bonnie. ... your reporting hours grid doesn't accommodate the numbers between 500 ... > wrong with the query. ...
    (microsoft.public.access.queries)
  • Re: multiple db query - join recordsets?
    ... open your database in Access, create a new query in Design view, switch to ... this query in Access, you need to replace the %'s with *'s. ... Please reply to the newsgroup. ...
    (microsoft.public.inetserver.asp.db)