Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Null value - on one selection still needs to print 1

Status
Not open for further replies.

Riverswan

IS-IT--Management
Jul 18, 2011
5
US
I have a VIP report to pull with comments.

There is a vipcode field and a commenttype field. I need three different vipcodes and the comments from three different commenttypes; however even if the commenttype is null and the person has the vipcode I need that person to print on the report.

So vipcode in [V1,V2,V3] and commenttype in [HSP,FO,VIP] and of course null. Tried a if/then/else... no luck.

Thanks in advance- River_k
 
Use a left join FROM the table containing the vipcode field TO the table containing the commenttype field. Then create a formula like this:

if isnull(commenttype) or
not(commenttype in ['HSP','FP','VIP']) then
"" else
{table.comment}

Be sure NOT to add any selection criteria on the comments table in your record selection formula, as that would effectively undo the left join and you would lose VIP table records.

-LB
 
{res.arrivedate} in {?Begin Arrival Date } to {?End Arrival Date } and
{res.status} <> "CXL" and
{res.vipcode} in ["V1", "V2","V3"] and
{res.hotelnum} = {?Hotel Number}and
if isnull{rescomments.commtype}or
not({rescomments.commtype} in ["FO", "HK", "VIP"])then
""else
{rescomments.comments}


Hopefully I am missing something! This returns the error that a keyword "then" is missing - right after the first {rescomments.commtype}
 
Do you have spaces around the 'and's, 'or's and the 'then'. It appears you do not in your example. Also, I believe lbass wanted you to put the 'IF' statement in a formula, not the selection criteria. I believe the formula would be for displaying the data not filtering it.

I hope this helps.
 
if isnull{rescomments.commtype}or not({rescomments.commtype} in ["FO", "HK", "VIP"])then ""else{rescomments.comments}

okay- it is now a formula; however still getting the same error.
:(
 
No, the formula should NOT be included in the record selection formula. Leave the record selection formula like this:

{res.arrivedate} in {?Begin Arrival Date } to {?End Arrival Date } and
{res.status} <> "CXL" and
{res.vipcode} in ["V1", "V2","V3"] and
{res.hotelnum} = {?Hotel Number}

Then use the following formula in your detail section:

if isnull{rescomments.commtype} or
not({rescomments.commtype} in ["FO", "HK", "VIP"]) then
"" else
{rescomments.comments}

-LB
 
Okay- I did do that and create a formula (with no errors) - however it is still not returning records that do not have a commenttype. so if that field is blank and the guest has one of the codes of V!, V2 or V3 it is not printing the information.
 
What kind of link do you have FROM res TO rescomments? Must be a left outer join.If you continue to have trouble, you should post your SQL query (database->show SQL query) into the thread.

-LB
 

SELECT resrooms.roomnum, res.guestname, res.groupcode, res.arrivedate, res.arrivetime, res.expdepartdate, res.vipcode, rescomments.commtype, res.status, rooms.hkstatus, res.hotelnum, rescomments.comments
FROM dev:eci.res res, dev:eci.resrooms resrooms, dev:eci.rooms rooms, dev:eci.rescomments rescomments
WHERE ((res.hotelnum=resrooms.hotelnum) AND (res.guestnum=resrooms.guestnum)) AND ((resrooms.roomnum=rooms.roomnum) AND (resrooms.hotelnum=rooms.hotelnum)) AND ((resrooms.guestnum=rescomments.guestnum) AND (resrooms.hotelnum=rescomments.hotelnum)) AND (res.arrivedate>={d '2011-07-19'} AND res.arrivedate<={d '2011-07-22'}) AND (res.vipcode='V1' OR res.vipcode='V2') AND res.hotelnum=468 AND res.status<>'CXL'
ORDER BY res.arrivedate, res.guestname
 
These show equal joins, not a left outer. Also, the link to resrooms could limit the res table. Go into the database expert, right click on the link from res to rescomments and change the link to a left outer join.

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top