Solved

Conditional Data

Posted on 2011-09-25
16
349 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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
 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

621 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