Skip to Content
author's profile photo Former Member
Former Member

2 questions

Question 1:

I'm trying to create a formula that will help me say "if there is a {dateinstalled} then 'yes' else 'no' " A problem that I've been having is that when I add DateInstalled to the report the number of records goes from 1257 to 344. I want those numbers that "go away" to remain and say 'no'.

Question 2:

I'm trying to find the sum # of drug tests for each person. For example, if {drugtestdates} then I want to find the sum of all the dates for each {lastname} (person). How do I write a formula for that?

Thank you!

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    Posted on Jan 07, 2014 at 05:38 PM

    Question 1:

    Your formula would look something like this:

    If not IsNull({dateinstalled}) then 'Yes' else 'No'

    Make sure you're not using this in your selection criteria as that will filter out records and you'll see fewer records on the report.

    Question 2:

    Assuming that you actually want to count the number of drug tests, you could try something like this:

    If not IsNull({drugtestdate}) then 1 else 0

    You would then sum this number on your report.

    One caveat on both of these answers - you've not really given us info about how your data is structured which makes it difficult to give you a good response to your questions.


    Add a comment
    10|10000 characters needed characters exceeded

    • How are you joining your tables? I would make sure that the join to the table that has the dates is set as a "Left Outer" join. To do this, go to the Links tab in the Database Expert and do the following:

      1. Select the join to that table

      2. Right-click on the join

      3. Select "Link Options"

      4. Set the link to "Left Outer"

      This will cause the query to include all records, even if there is not a matching record available to link to in this table.


Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.