2 questions

[Former Member]

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!

[DellSC · Accepted solution]

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.

-Dell

[Former Member · Accepted solution]

You're answers were exactly what I was looking for! I've been trying to figure that out for days! Thank you so much!

[Former Member · Accepted solution]

But I don't know what you mean when you say "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."

Where do I go to make sure it's not in my selection criteria? Because in my results, those reports are filtered out.

The way this is structured is I have a report with field names of: names, other demographics, # of drug tests, interlock installed. I have 6 different tables that linked someone or another.

[DellSC · Accepted solution]

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.

-Dell