Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Posts
    16

    Unanswered: Help with Expressions

    Hi Everyone,

    I am attempting to create a report that will return a numerical value from a query when the field is populated and a text string when there is nothing in this field. I have tried doing this with an expression in the report to no avail. What is the syntax for this type of expression?

    I have been using something like this:

    [numeric_value] or “text”

    Where “numeric value” is a field in the query.

    Thanks for your help.

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    You could try: YourReportTextBox = Nz(]numeric_value], ""), just make sure you haven't formatted your box a certain numerical way. I don't think you can format "" numerically. Alternatively you could Nz([numeric_value], 0) and hide the textbox when it is zero using the Detail_Format event.

    HTH
    All code ADO/ADOX unless otherwise specified.
    Mike.

  3. #3
    Join Date
    Sep 2003
    Posts
    16
    Originally posted by HomerBoo
    You could try: YourReportTextBox = Nz(]numeric_value], ""), just make sure you haven't formatted your box a certain numerical way. I don't think you can format "" numerically. Alternatively you could Nz([numeric_value], 0) and hide the textbox when it is zero using the Detail_Format event.

    HTH
    Thanks Mike,

    I have tried both of your suggestions and am now seeing “#Enor” in the report for that value. Any ideas as to what might be causing this? Thanks for your help and sorry about the newbie questions.

  4. #4
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Can you provide a more detailed property listing of your textbox, i.e., the value, format, control source, enabled, etc. Not everything, just the helpful ones.

    BTW, there was a syntax error in my first Nz example, Nz(] instead of Nz([.

    Additionally, I don't know if I was explicit, the control source should be:

    =Nz([numeric_value], "")

    Please note the = sign.

    No worries for the perceived experience level on the question, this is a common question and Nz is not an overly well-known function like IIf.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  5. #5
    Join Date
    Sep 2003
    Posts
    16
    I’m not sure if this is the information you are looking for but here it goes:

    Value = 1-5 or Null
    Format = General Number
    Control Source = “=Nz([Field], “”)

    The “Field” is a field in my query that will have a numeric value from 1-5 or nothing at all.

    I hope this is useful. Thanks again for you help. Let me know if there is any other info you need.

  6. #6
    Join Date
    Sep 2003
    Posts
    7

    Re: Help with Expressions

    Try setting the ControlSource for the text box on the report to something like this:

    =IIf(IsNull([numeric_value]), "Text string", [numeric_value])

    OM Gang


    Originally posted by Crypto
    Hi Everyone,

    I am attempting to create a report that will return a numerical value from a query when the field is populated and a text string when there is nothing in this field. I have tried doing this with an expression in the report to no avail. What is the syntax for this type of expression?

    I have been using something like this:

    [numeric_value] or “text”

    Where “numeric value” is a field in the query.

    Thanks for your help.

  7. #7
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Crypto,

    There may be another problem then. I just created a table with values 1 through 5 and three nulls, then created a form and a report using control source =Nz([Field], "") and it seems to work. If you want to zip-post your db, let me know and I'll take a look at it.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  8. #8
    Join Date
    Sep 2003
    Posts
    16
    Thanks for your help guys. I wish I could send you the DB but the customer is very protective of the data. I will continue to work on this issue and post my findings.

    Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •