Solved

Conditional Data

Posted on 2011-09-25
16
340 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
Comment Utility
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
Comment Utility

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
Comment Utility
rest of the columns and date condition i want same as it is i want
0
 
LVL 29

Expert Comment

by:QPR
Comment Utility
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
Comment Utility

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

didnt i said
<< rest of the columns and date condition i want same as it is i want >>
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility

<<<  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
Comment Utility
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
Comment Utility
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 25

Accepted Solution

by:
Shaun Kline earned 500 total points
Comment Utility
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
Comment Utility

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
Comment Utility
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
Comment Utility
Thanks Shaun_Kline:

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A short article about a problem I had getting the GPS LocationListener working.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

771 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