Solved

Conditional Data

Posted on 2011-09-25
16
343 Views
Last Modified: 2012-05-12
Hi
i am using following query to get result and its working fine.
but now i want to add one more field ( BD.TotToPay AS TTbb) But condition is if (BD.Pymt_mode = TBB)
How can i add this condition filed in attached same query.
0
Comment
Question by:crystal_Tech
16 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 36596858
rs.Open "Select CM.Cash_MemoNo AS CM_No, CM.CM_Date as CM_Date, BD.BDate as BDate,BD.From_City as Bkg_Off, CM.LRNo, BD.Article as Art, BD.Weight, BD.TotToPay as Topay, TotPaid as Paid, CM.Acc_Typ AS Acc, CM.CNee, CM.Octroi, CM.Demerage as Dem, CM.Hamali as Labour, CM.DDCharge as D_Del, CM.OctSC, CM.InsuCharge as R_Charge, CM.ServiceTax as STax, CM.CM_Total as Total, BD.TotToPay AS TTbb FROM CashMemo AS CM, Bilty_Detail as BD where CM.CM_Date between CDATE('" & DTPickerFrom.Value & "') and CDATE('" & DTPickerTo.Value & "') and BD.BiltyNo = CM.LRNo and BD.Pymt_mode = TBB Order by CM.Cash_MemoNo", con, adOpenKeyset, adLockOptimistic
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36596867

Sorry QPR
it will give me all record if Pymt_mode = TBB  
right..?
if yes then i dont want it
 i just want only one column result if Pymt_mode = TBB
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36596869
rest of the columns and date condition i want same as it is i want
0
 
LVL 29

Expert Comment

by:QPR
ID: 36596884
No it will give you any records that = TBB AND fit all the other criteria already specified in the WHERE part of your code.

AND means must satisfy all conditions
OR means must satisfy at least one condition.

If you only have one row in the table where pymt_mode = TBB then this will be returned...but if that row does not fit with the other parts of the WHERE then zero rows will be returned.

It sounds like you may be trying to do 2 different things using the same code.
You might be able to use an outer join or a union (using NULLS to make up the balance of columns) but without knowing the exact problem it's difficult to guess
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36596926

there is three columns in the table ( BD )
1 - Pymt_mode
2 - TotTOPAY
3 - TotPAID

If in Pymt_mode = TOPAY then TotPAID = 0 and some value is in TotTopay
if in Pymt_mode = PAID then TotTOPAY = 0 and some value is in TotPAID
BUT
if Pymt_mode = TBB then TotPAID = 0 and some value is in TotTopay

There is no Third Column for TotTBB
TBB Value also i am inserting in TotTopay Column

i want all the records between date, but only one column (TTbb) i want if there is Pymt_mode = 'TBB'
i want to use this condition just for only on column.


0
 
LVL 29

Expert Comment

by:QPR
ID: 36596976
You say only 1 column but you have many many colulmns in your select statement.
By adding criteria for Pymt_mode you risk losing other records because you are using AND.
If you want to return a value (not from the table but one you set based on records) then you might want to look at using a select case in your code
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36597075

didnt i said
<< rest of the columns and date condition i want same as it is i want >>
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Comment

by:crystal_Tech
ID: 36601592

<<<  It sounds like you may be trying to do 2 different things using the same code.
You might be able to use an outer join or a union (using NULLS to make up the balance of columns) but >>>

yes i am trying to do  2 different things using the same code.
0
 
LVL 21

Expert Comment

by:developmentguru
ID: 36714409
I would need to know which database you are querying (they all have slightly different capabilities, some drastically different).  I would also need to see your original query (I didn't see it in your post).

When you query a database you will always have the same number of fields in all records.  If you want to have a new field in the result that only shows up under a certain condition then some rows would have the field and some would not.  This is NOT the way a database query works.  The closest you would normally be able to get to that behavior is to always have the new field, but leave it NULL if the condition is not met.
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36715580
developmentguru:
Thanks for Reply Sir

<< I would need to know which database >>
MS Access

<< I would also need to see your original query >>
this is my original code.
 
If rs.State = 1 Then rs.Close
        rs.Open "Select CM.Cash_MemoNo AS CM_No, CM.CM_Date as CM_Date, BD.BDate as BDate,BD.From_City as Bkg_Off, CM.LRNo, BD.Article as Art, BD.Weight, BD.TotToPay as Topay, TotPaid as Paid, CM.Acc_Typ AS Acc, CM.CNee, CM.Octroi, CM.Demerage as Dem, CM.Hamali as Labour, CM.DDCharge as D_Del, CM.OctSC, CM.InsuCharge as R_Charge, CM.ServiceTax as STax, CM.CM_Total as Total FROM CashMemo AS CM, Bilty_Detail as BD where CM.CM_Date between CDATE('" & DTPickerFrom.Value & "') and CDATE('" & DTPickerTo.Value & "') and BD.BiltyNo = CM.LRNo Order by CM.Cash_MemoNo", con, adOpenKeyset, adLockOptimistic
'        rs.Open "Select CM.Cash_MemoNo AS CM_No, CM.CM_Date as CM_Date, BD.BDate as BDate,BD.From_City as Bkg_Off, CM.LRNo, BD.Article as Art, BD.Weight, BD.TotToPay as Topay, TotPaid as Paid, CM.Acc_Typ AS Acc, CM.CNee, CM.Octroi, CM.Demerage as Dem, CM.Hamali as Labour, CM.DDCharge as D_Del, CM.OctSC, CM.InsuCharge as R_Charge, CM.ServiceTax as STax, CM.CM_Total as Total, BD.TotToPay as TTbb FROM CashMemo AS CM, Bilty_Detail as BD where CM.CM_Date between CDATE('" & DTPickerFrom.Value & "') and CDATE('" & DTPickerTo.Value & "') and BD.BiltyNo = CM.LRNo and BD.Pymt_mode = 'TBB' Order by CM.Cash_MemoNo", con, adOpenKeyset, adLockOptimistic
        If rs.RecordCount > 0 Then
        Set DataGrid1.DataSource = rs

        End If
    End If
End If

Open in new window


<<The closest you would normally be able to get to that behavior is to always have the new field, but leave it NULL if the condition is not met. >>

In BD table there is three columns only, TotTOPAY,   TotPAID, and Pymt_mode.
Type of these two columns TotTopay and TotPAID is Number and Pymt_mode is Text.
in Pymt_mode have only four different data, "TOPAY" "PAID" "TBB" and "FOC"

currently i am using same above query its working fine but the requirement is to add new column but with same result.in datagrid

what i want in that new column is
with same result i want data in new column is if Pymt_mode = "TBB" then whatever value is in TotTOPAY field is display in new column.

i would prefer "0" IF there is no data.

hope i explained my problem well.

Regards

 
0
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 500 total points
ID: 36716359
Using MS Access, the IF conditional statement would allow you to complete your requirements:
..., IF(Pymt_mode = "TBB", TotTOPAY, 0) <column alias> FROM ...
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36718921

run time error
Undefined function 'IF' in expression

        rs.Open "Select CM.Cash_MemoNo AS CM_No, CM.CM_Date as CM_Date, BD.BDate as BDate,BD.From_City as Bkg_Off, CM.LRNo, BD.Article as Art, BD.Weight, BD.TotToPay as Topay, TotPaid as Paid, IF(Pymt_mode = 'TBB', TotTOPAY, 0) as TotTBB, CM.Acc_Typ AS Acc, CM.CNee, CM.Octroi, CM.Demerage as Dem, CM.Hamali as Labour, CM.DDCharge as D_Del, CM.OctSC, CM.InsuCharge as R_Charge, CM.ServiceTax as STax, CM.CM_Total as Total FROM CashMemo AS CM, Bilty_Detail as BD where CM.CM_Date between CDATE('" & DTPickerFrom.Value & "') and CDATE('" & DTPickerTo.Value & "') and BD.BiltyNo = CM.LRNo Order by CM.Cash_MemoNo", con, adOpenKeyset, adLockOptimistic
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36723592
except IF
I tried IIF and its working

Thanks Shaun_Kline: Thank you very much, you solved my problem. :-)

0
 
LVL 1

Author Closing Comment

by:crystal_Tech
ID: 36723937
Thanks Shaun_Kline:

I just replaced IF to IIF
and its working ..
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This is about my first experience with programming Arduino.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now