Data Connectivity
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Data Connectivity
Message Icon Topic: Help with query Post Reply Post New Topic
Author Message
cyber-guys
Groupie
Groupie


Joined: 02 May 2008
Online Status: Offline
Posts: 42
Quote cyber-guys Replybullet Topic: Help with query
    Posted: 16 Jan 2013 at 4:50am
I need to access AP sales by customer from a database using 3 tables:
tickets
paymentmethod
customers
 
The problem is that the program that generates the data does not assign a payment type for any sales ticket with zero balance.  The payment methods are cash, check, AP  (paymentmode = 1,2,3)
 
I would like to pull up all tickets for a specific customer where paymentmethod.mode=3 OR tickets.amount=0
 
another way is
paymentmethod.mode=3 OR isnull(paymentmethod.mode)
 
Thanks in advance,
JimDead
IP IP Logged
cyber-guys
Groupie
Groupie


Joined: 02 May 2008
Online Status: Offline
Posts: 42
Quote cyber-guys Replybullet Posted: 16 Jan 2013 at 5:23am
I tried this:
customers.custnum=123 and
(paymentmethod.mode=3 or isnull(paymentmethod.mode))
 
it only returns the mode=3 items - if I just use the isnull() it returns the null items - I just can't figure how to use the OR statement
 
HELP!!!!!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 16 Jan 2013 at 8:16am
use the isnull condition first or flip the drop dpwn option in the formula editor to "use default values" for nulls.
 
(isnull(paymentmethod.mode) or paymentmethod.mode=3)
and
customers.custnum=123
IP IP Logged
cyber-guys
Groupie
Groupie


Joined: 02 May 2008
Online Status: Offline
Posts: 42
Quote cyber-guys Replybullet Posted: 16 Jan 2013 at 9:13am
I tried both with no luck - I did get it to work with the following:
 
(isnull({PaymentMode.TicketPaymentID})=True or {PaymentMode.Description}='AP')
 
Thanks for your help,
JimBig%20smileBig%20smileBig%20smile
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 16 Jan 2013 at 9:48am

fyi- i almost always find it easier to flip the option in the formula editor to make it a non-issue.

depending on how you have crystal set to handle nulls (in multiple places in the software) it will stop evaluating when it hits the null which is not the same as returning a false and moving to the next part of the formula.
 
I am just letting you know because at one time I thought I had it sorted using the "=true" option but it was really about the order of the statement. The "=true" is redundant and in my experience not the fix.
HTH
IP IP Logged
cyber-guys
Groupie
Groupie


Joined: 02 May 2008
Online Status: Offline
Posts: 42
Quote cyber-guys Replybullet Posted: 17 Jan 2013 at 1:35am
I'll keep that in mind

Thanks,
Jim
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.