2. ASPRunnerPro General questions
  3. Can I hide blank records?
This topic is locked

Can I hide blank records?

 		1/28/2007 6:34:49 PM
ASPRunnerPro General questions
G
 grinningdog author

I've got a form with the fields 'name', 'phone number', and 'comments' visible. How can I show only the records with an entry in the 'comments' field?
For example if I have 250 records but only 125 of them have comments filled in, can I hide the other 125?
I'm guessing an onload event but can't work out the correct syntax. I've had several goes but it either falls over or just ignores me! <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=4489&image=1&table=forumtopics' class='bbc_emoticon' alt=':blink:' />
Bob
J
 		Jane 1/29/2007

Bob,
you can do it editing your SQL query manually on the Edit SQL query tab.

Here is a sample query:

select field1,

field2,

field3

from TableName

where (comments is NULL or comments='')
G
 		grinningdog author 1/29/2007

No, I'm sorry but I just don't get it. I'm going to the Edit SQL tab and selecting the relevant table.
Then I'm adding an SQL query
SELECT (Comment) WHERE (Comment is NULL or Comment=''),
I get an error message about syntax.
Also, is this instead of the line that just reads (Comment), or in addition to it?
J
 		Jane 1/29/2007

Bob,
your query is incorrect.

You don't need to change the query on the Edit SQL query tab, only add where clause at the end of it.
G
 		grinningdog author 1/29/2007

Aha, now I get it and I can adapt this to other places too.
In fact it then showed all the blank records rather than hiding them so I adapted it to read
WHERE (Commentator is NOT NULL or Commentator='') at the end of the last line. Not sure about the last bit though but I'll keep reading the interactive SQL tutorial till it's perfect!
I'm really trying to understand when I would use this type of edit SQL query and when I would use Events.
For example, I got a BeforeAdd event that calculates Age based on date of birth. If I wanted to use that age along with another field, Gender, to assign another field called Category (for example Senior Male) would I use another before add event or edit the SQL query at Step 4?
Regards

Bob
J
 		Jane 1/29/2007

Bob,
use Before record added event for this purpose.

Here is a sample code:

Function BeforeEdit(dict, where)

dict("Category") = dict("Age") & " " & dict("Gender")
BeforeEdit = True

End Function
G
 		grinningdog author 1/29/2007

OK, I really want to try and work this out myself but as long as I'm on the right track with a Before Edit event I'll keep at it.
I actually want to say If Gender = Male and Age is between 15 and 19 then Category equals JM
Followed by If Gender = Female and Age is between 15 and 19 then Category equals JF

Followed by If Gender = Male and Age is between 20 and 39 then Category equals SM
etc. for 8 categories.
I've got the Categories listed in a separate table (tblCategories) if that's needed
G
 		grinningdog author 1/29/2007

I'm obviously too dim-witted! I'm just off on a short holiday so maybe a bit of sunshine will agitate my brain cells. I'll try again in a week
Thanks for all your help so far
Bob
J
 		Jane 1/30/2007

Hi,
here is a sample:

Function BeforeEdit(dict, where)

if dict("Gender")="Male" and dict("Age")>=15 and dict("Age")<=19 then

dict("Category") = "JM"

end if
if dict("Gender")="Female" and dict("Age")>=15 and dict("Age")<=19 then

dict("Category") = "JF"

end if
if dict("Gender")="Male" and dict("Age")>=20 and dict("Age")<=39 then

dict("Category") = "SM"

end if

BeforeEdit = True

End Function
G
 		grinningdog author 2/14/2007

Doh! I was SO close :¬)
Thanks very much again Jane, it now works perfectly
I LOVE this stuff!