If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need query to separate 2 entry types in a table field
Hi all Access 2002 XP - Windows 2000 SP4
I now have a field in a table that records both Checks and Debit Card transactions. I would like to be able to sort the two types of transactions to be displayed on a form, using a filter form. I need to separate the two types of transactions; i.e., Check and DBT from the same CheckDBT field. I've been working on this for a while, and trying to use different criteria for the query, but, I'm not having any luck separating the two transaction types to sort by and display as a separate transaction on the record form. Do I need to use a separate query for each transaction type using specific criteria? Or...is there a way I can set criteria in the existing query to do any 'either/or' from the filter form control? I now have a control to enter a Check no and call it up in the record form, and this does work fine. But, I also want to call up the debit card transactions to review by date period, or all of them at once. I have a control and command button created for this on the filter form, however, I am not sure how, or where, the query or code should be entered to separate the DBT entries from the Check Numbers that are in the same field. Here is the SQL for the current query for the record form. PARAMETERS [Forms]![frmCheckingRecFilter]![TxtDate1] DateTime, [Forms]![frmCheckingRecFilter]![TxtDate2] DateTime; SELECT T.BeginBal, T.CheckNo, T.TransactionDate, T.Transaction, T.CheckDBTAmt, T.DepositAmt, T.TransactionType, T.Comment, (SELECT SUM(Nz(DepositAmt, 0) - Nz(CheckDBTAmt, 0) + Nz(BeginBal,0)) FROM MyCheckRegister T1 WHERE T1.TransactionDate = T.TransactionDate) AS RunningBalance FROM MyCheckRegister AS T WHERE (((T.TransactionDate) Between [Forms]![frmCheckingRecFilter]![TxtDate1] And [Forms]![frmCheckingRecFilter]![TxtDate2])) OR ((([Forms]![frmCheckingRecFilter]![TxtDate1]) Is Null)) OR ((([Forms]![frmCheckingRecFilter]![TxtDate2]) Is Null)) ORDER BY T.CheckNo, T.TransactionDate; I would truly appreciate any suggestions on the best course of action. Query...or code for form controls? Jan |
#2
|
|||
|
|||
Jan,
How do you tell if it is a Check or DBT transaction? -- Steve Schapel, Microsoft Access MVP Jan Il wrote: Hi all Access 2002 XP - Windows 2000 SP4 I now have a field in a table that records both Checks and Debit Card transactions. I would like to be able to sort the two types of transactions to be displayed on a form, using a filter form. I need to separate the two types of transactions; i.e., Check and DBT from the same CheckDBT field. I've been working on this for a while, and trying to use different criteria for the query, but, I'm not having any luck separating the two transaction types to sort by and display as a separate transaction on the record form. Do I need to use a separate query for each transaction type using specific criteria? Or...is there a way I can set criteria in the existing query to do any 'either/or' from the filter form control? I now have a control to enter a Check no and call it up in the record form, and this does work fine. But, I also want to call up the debit card transactions to review by date period, or all of them at once. I have a control and command button created for this on the filter form, however, I am not sure how, or where, the query or code should be entered to separate the DBT entries from the Check Numbers that are in the same field. Here is the SQL for the current query for the record form. PARAMETERS [Forms]![frmCheckingRecFilter]![TxtDate1] DateTime, [Forms]![frmCheckingRecFilter]![TxtDate2] DateTime; SELECT T.BeginBal, T.CheckNo, T.TransactionDate, T.Transaction, T.CheckDBTAmt, T.DepositAmt, T.TransactionType, T.Comment, (SELECT SUM(Nz(DepositAmt, 0) - Nz(CheckDBTAmt, 0) + Nz(BeginBal,0)) FROM MyCheckRegister T1 WHERE T1.TransactionDate = T.TransactionDate) AS RunningBalance FROM MyCheckRegister AS T WHERE (((T.TransactionDate) Between [Forms]![frmCheckingRecFilter]![TxtDate1] And [Forms]![frmCheckingRecFilter]![TxtDate2])) OR ((([Forms]![frmCheckingRecFilter]![TxtDate1]) Is Null)) OR ((([Forms]![frmCheckingRecFilter]![TxtDate2]) Is Null)) ORDER BY T.CheckNo, T.TransactionDate; I would truly appreciate any suggestions on the best course of action. Query...or code for form controls? Jan |
#3
|
|||
|
|||
Hi Steve :-)
Jan, How do you tell if it is a Check or DBT transaction? In the table field I can enter DBT for the debit card pruchase, or the check number, i.e., 1002 or DBT. In the Entry form, when the DBT is entered into the CheckNo control, the code in the dorms module adds a number to it so that it is then a sortable AlphaNumerical entry, such as DBT00001. Thus, it can separate the DBT transaction entries from the Check numbers and make them also sortable numerically. Here is the code for the form module. Option Explicit Private Function NextDBTNumber() As String ' This function finds the highest "DBT" check number currently on ' file and adds 1 to it to get a new DBT number. Dim strMaxNum As String strMaxNum = vbNullString & _ DMax("CheckNo", "MyCheckRegister", _ "CheckNo Like 'DBT*'") If Len(strMaxNum) = 0 Then NextDBTNumber = "DBT000001" Else NextDBTNumber = _ "DBT" & Format(1 + CLng(Mid(strMaxNum, 4)), "000000") End If End Function And here is the code for the txtCheckNo control on the form: Private Sub txtCheckNo_AfterUpdate() With Me!txtCheckNo If .Value = "DBT" _ And IsNull(.OldValue) _ Then .Value = NextDBTNumber() End If End With End Sub So, what I need to do is to be able to sort just the DBTxxx entries separate from the Check number entries in this field using a combo box (cmbDBTExpense) and command button (cmdDBTExpense) that will display just the DBT transactions in the record form. This way I can call up individual transactions by date from the filter form, or all of the checks, or all of the DBT transactions separately from the filter form controls. Thank you very much for your time and assistance, I truly appreciate it. Jan Smiles are meant to be shared, that's why they're so contagious. -- Steve Schapel, Microsoft Access MVP Jan Il wrote: Hi all Access 2002 XP - Windows 2000 SP4 I now have a field in a table that records both Checks and Debit Card transactions. I would like to be able to sort the two types of transactions to be displayed on a form, using a filter form. I need to separate the two types of transactions; i.e., Check and DBT from the same CheckDBT field. I've been working on this for a while, and trying to use different criteria for the query, but, I'm not having any luck separating the two transaction types to sort by and display as a separate transaction on the record form. Do I need to use a separate query for each transaction type using specific criteria? Or...is there a way I can set criteria in the existing query to do any 'either/or' from the filter form control? I now have a control to enter a Check no and call it up in the record form, and this does work fine. But, I also want to call up the debit card transactions to review by date period, or all of them at once. I have a control and command button created for this on the filter form, however, I am not sure how, or where, the query or code should be entered to separate the DBT entries from the Check Numbers that are in the same field. Here is the SQL for the current query for the record form. PARAMETERS [Forms]![frmCheckingRecFilter]![TxtDate1] DateTime, [Forms]![frmCheckingRecFilter]![TxtDate2] DateTime; SELECT T.BeginBal, T.CheckNo, T.TransactionDate, T.Transaction, T.CheckDBTAmt, T.DepositAmt, T.TransactionType, T.Comment, (SELECT SUM(Nz(DepositAmt, 0) - Nz(CheckDBTAmt, 0) + Nz(BeginBal,0)) FROM MyCheckRegister T1 WHERE T1.TransactionDate = T.TransactionDate) AS RunningBalance FROM MyCheckRegister AS T WHERE (((T.TransactionDate) Between [Forms]![frmCheckingRecFilter]![TxtDate1] And [Forms]![frmCheckingRecFilter]![TxtDate2])) OR ((([Forms]![frmCheckingRecFilter]![TxtDate1]) Is Null)) OR ((([Forms]![frmCheckingRecFilter]![TxtDate2]) Is Null)) ORDER BY T.CheckNo, T.TransactionDate; I would truly appreciate any suggestions on the best course of action. Query...or code for form controls? Jan |
#4
|
|||
|
|||
Jan
There would probably be a number of ideas of tackling this sort of stuff. One would be to make an Option Group with 3 toggle buttons labelled Checks, DBTs, and All. On the after Update event of this Option Group, you could use code which either toggles the Record Source of the form, or the Filter. For example... Select Case Me.YourOptionGroup Case 1 Me.RecordSource = "SELECT * FROM YourQuery WHERE txtCheckNo Not Like 'DBT*'" Case 1 Me.RecordSource = "SELECT * FROM YourQuery WHERE txtCheckNo Like 'DBT*'" Case 1 Me.RecordSource = "YourQuery" End Select or... Select Case Me.YourOptionGroup Case 1 Me.Filter = "txtCheckNo Not Like 'DBT*'" Me.FilterOn = True Case 1 Me.Filter = "txtCheckNo Like 'DBT*'" Me.FilterOn = True Case 1 Me.FilterOn = False End Select -- Steve Schapel, Microsoft Access MVP Jan Il wrote: Hi Steve :-) Jan, How do you tell if it is a Check or DBT transaction? In the table field I can enter DBT for the debit card pruchase, or the check number, i.e., 1002 or DBT. In the Entry form, when the DBT is entered into the CheckNo control, the code in the dorms module adds a number to it so that it is then a sortable AlphaNumerical entry, such as DBT00001. Thus, it can separate the DBT transaction entries from the Check numbers and make them also sortable numerically. Here is the code for the form module. Option Explicit Private Function NextDBTNumber() As String ' This function finds the highest "DBT" check number currently on ' file and adds 1 to it to get a new DBT number. Dim strMaxNum As String strMaxNum = vbNullString & _ DMax("CheckNo", "MyCheckRegister", _ "CheckNo Like 'DBT*'") If Len(strMaxNum) = 0 Then NextDBTNumber = "DBT000001" Else NextDBTNumber = _ "DBT" & Format(1 + CLng(Mid(strMaxNum, 4)), "000000") End If End Function And here is the code for the txtCheckNo control on the form: Private Sub txtCheckNo_AfterUpdate() With Me!txtCheckNo If .Value = "DBT" _ And IsNull(.OldValue) _ Then .Value = NextDBTNumber() End If End With End Sub So, what I need to do is to be able to sort just the DBTxxx entries separate from the Check number entries in this field using a combo box (cmbDBTExpense) and command button (cmdDBTExpense) that will display just the DBT transactions in the record form. This way I can call up individual transactions by date from the filter form, or all of the checks, or all of the DBT transactions separately from the filter form controls. Thank you very much for your time and assistance, I truly appreciate it. Jan Smiles are meant to be shared, that's why they're so contagious. |
#5
|
|||
|
|||
Hi Steve :-)
"Steve Schapel" wrote in message ... Jan There would probably be a number of ideas of tackling this sort of stuff. One would be to make an Option Group with 3 toggle buttons labelled Checks, DBTs, and All. On the after Update event of this Option Group, you could use code which either toggles the Record Source of the form, or the Filter. For example... Select Case Me.YourOptionGroup Case 1 Me.RecordSource = "SELECT * FROM YourQuery WHERE txtCheckNo Not Like 'DBT*'" Case 1 Me.RecordSource = "SELECT * FROM YourQuery WHERE txtCheckNo Like 'DBT*'" Case 1 Me.RecordSource = "YourQuery" End Select or... Select Case Me.YourOptionGroup Case 1 Me.Filter = "txtCheckNo Not Like 'DBT*'" Me.FilterOn = True Case 1 Me.Filter = "txtCheckNo Like 'DBT*'" Me.FilterOn = True Case 1 Me.FilterOn = False End Select Thank you very much for the information. I'll give these a go and see how I get along with them. I'll check back with you. :-) Jan Smiles are meant to be shared, that's why they're so contagious. -- Steve Schapel, Microsoft Access MVP Jan Il wrote: Hi Steve :-) Jan, How do you tell if it is a Check or DBT transaction? In the table field I can enter DBT for the debit card pruchase, or the check number, i.e., 1002 or DBT. In the Entry form, when the DBT is entered into the CheckNo control, the code in the dorms module adds a number to it so that it is then a sortable AlphaNumerical entry, such as DBT00001. Thus, it can separate the DBT transaction entries from the Check numbers and make them also sortable numerically. Here is the code for the form module. Option Explicit Private Function NextDBTNumber() As String ' This function finds the highest "DBT" check number currently on ' file and adds 1 to it to get a new DBT number. Dim strMaxNum As String strMaxNum = vbNullString & _ DMax("CheckNo", "MyCheckRegister", _ "CheckNo Like 'DBT*'") If Len(strMaxNum) = 0 Then NextDBTNumber = "DBT000001" Else NextDBTNumber = _ "DBT" & Format(1 + CLng(Mid(strMaxNum, 4)), "000000") End If End Function And here is the code for the txtCheckNo control on the form: Private Sub txtCheckNo_AfterUpdate() With Me!txtCheckNo If .Value = "DBT" _ And IsNull(.OldValue) _ Then .Value = NextDBTNumber() End If End With End Sub So, what I need to do is to be able to sort just the DBTxxx entries separate from the Check number entries in this field using a combo box (cmbDBTExpense) and command button (cmdDBTExpense) that will display just the DBT transactions in the record form. This way I can call up individual transactions by date from the filter form, or all of the checks, or all of the DBT transactions separately from the filter form controls. Thank you very much for your time and assistance, I truly appreciate it. Jan Smiles are meant to be shared, that's why they're so contagious. |
#6
|
|||
|
|||
Jan,
Sorry, got bitten by the "incomplete copy/paste bug"!! "Case 1" certainly gets too much attention! This should be... Select Case Me.YourOptionGroup Case 1 Me.RecordSource = "SELECT * FROM YourQuery WHERE txtCheckNo Not Like 'DBT*'" Case 2 Me.RecordSource = "SELECT * FROM YourQuery WHERE txtCheckNo Like 'DBT*'" Case 3 Me.RecordSource = "YourQuery" End Select or... Select Case Me.YourOptionGroup Case 1 Me.Filter = "txtCheckNo Not Like 'DBT*'" Me.FilterOn = True Case 2 Me.Filter = "txtCheckNo Like 'DBT*'" Me.FilterOn = True Case 3 Me.FilterOn = False End Select -- Steve Schapel, Microsoft Access MVP Jan Il wrote: Hi Steve :-) Thank you very much for the information. I'll give these a go and see how I get along with them. I'll check back with you. :-) Jan |
#7
|
|||
|
|||
Hi Steve :-)
Yup! BTDT... ;o)) I kinda thought that might be how it should go.....g Jan, Sorry, got bitten by the "incomplete copy/paste bug"!! "Case 1" certainly gets too much attention! This should be... Select Case Me.YourOptionGroup Case 1 Me.RecordSource = "SELECT * FROM YourQuery WHERE txtCheckNo Not Like 'DBT*'" Case 2 Me.RecordSource = "SELECT * FROM YourQuery WHERE txtCheckNo Like 'DBT*'" Case 3 Me.RecordSource = "YourQuery" End Select or... Select Case Me.YourOptionGroup Case 1 Me.Filter = "txtCheckNo Not Like 'DBT*'" Me.FilterOn = True Case 2 Me.Filter = "txtCheckNo Like 'DBT*'" Me.FilterOn = True Case 3 Me.FilterOn = False End Select Jan Jan Il wrote: Hi Steve :-) Thank you very much for the information. I'll give these a go and see how I get along with them. I'll check back with you. :-) Jan |
#8
|
|||
|
|||
Hi Steve :-)
Jan, Sorry, got bitten by the "incomplete copy/paste bug"!! "Case 1" certainly gets too much attention! This should be... Select Case Me.YourOptionGroup Case 1 Me.RecordSource = "SELECT * FROM YourQuery WHERE txtCheckNo Not Like 'DBT*'" Case 2 Me.RecordSource = "SELECT * FROM YourQuery WHERE txtCheckNo Like 'DBT*'" Case 3 Me.RecordSource = "YourQuery" End Select or... Select Case Me.YourOptionGroup Case 1 Me.Filter = "txtCheckNo Not Like 'DBT*'" Me.FilterOn = True Case 2 Me.Filter = "txtCheckNo Like 'DBT*'" Me.FilterOn = True Case 3 Me.FilterOn = False End Select I hope you will be a bit patient with me on this, as I have never done one of these Option Group thingies before, so I'm purely green as a new lemon. I have created the Option Group using the Wizard, and have the label names Checks, DBTs and All according to your instructions. Now...here is where I am not sure as to how this code should go into the After Update of this Option Group frame. I have entered all 3 of the cases as follows: Private Sub fmeOptionGrp_AfterUpdate() Select Case Me.fmeOptionGrp Case 1 Me.RecordSource = "SELECT * FROM qryCkRegisterDan WHERE txtCheckNo Not " Like 'DBT*'" Case 2 Me.RecordSource = "SELECT * FROM qryCkRegisterDan WHERE txtCheckNo " Like 'DBT*'" Case 3 Me.RecordSource = "qryCkRegisterDan" End Select End Sub The debugger seems to balk at this part of the code in the Case 1 Not " Like 'DBT*'" and the Case 2 " Like 'DBT*'" I am sure I have not entered the correctly, and I do apologize, BUT...it is a starting point. In the query, there is no txtCheckNo, that is the name of the control on the record form. In the query, the field is named CheckNo. So, I am a bit confused with this part of the codes. Also, should there be only one of the cases entered, as you have perhaps given them as an 'either/or type suggestion? I have used the first group of your suggestion to find the footing of how this should work. I truly do appreciate your help and patience. Jan -- Steve Schapel, Microsoft Access MVP Jan Il wrote: Hi Steve :-) Thank you very much for the information. I'll give these a go and see how I get along with them. I'll check back with you. :-) Jan |
#9
|
|||
|
|||
Jan,
You will see in my example... Me.RecordSource = "SELECT * FROM YourQuery WHERE txtCheckNo Not Like 'DBT*'" .... and then, in you code, you have thrown in a rogue " between the Not and the Like, as in... Me.RecordSource = "SELECT * FROM qryCkRegisterDan WHERE txtCheckNo Not " Like 'DBT*'" And yes, if you have named the form control differently from the field, you should use the field name, so... Me.RecordSource = "SELECT * FROM qryCkRegisterDan WHERE CheckNo Not Like 'DBT*'" Sounds like you have put Option Buttons in the Option Group, whereas I suggested Toggle Buttons... but that's just an aesthetic thing. So, see you you go now! -- Steve Schapel, Microsoft Access MVP Jan Il wrote: Hi Steve :-) I hope you will be a bit patient with me on this, as I have never done one of these Option Group thingies before, so I'm purely green as a new lemon. I have created the Option Group using the Wizard, and have the label names Checks, DBTs and All according to your instructions. Now...here is where I am not sure as to how this code should go into the After Update of this Option Group frame. I have entered all 3 of the cases as follows: Private Sub fmeOptionGrp_AfterUpdate() Select Case Me.fmeOptionGrp Case 1 Me.RecordSource = "SELECT * FROM qryCkRegisterDan WHERE txtCheckNo Not " Like 'DBT*'" Case 2 Me.RecordSource = "SELECT * FROM qryCkRegisterDan WHERE txtCheckNo " Like 'DBT*'" Case 3 Me.RecordSource = "qryCkRegisterDan" End Select End Sub The debugger seems to balk at this part of the code in the Case 1 Not " Like 'DBT*'" and the Case 2 " Like 'DBT*'" I am sure I have not entered the correctly, and I do apologize, BUT...it is a starting point. In the query, there is no txtCheckNo, that is the name of the control on the record form. In the query, the field is named CheckNo. So, I am a bit confused with this part of the codes. Also, should there be only one of the cases entered, as you have perhaps given them as an 'either/or type suggestion? I have used the first group of your suggestion to find the footing of how this should work. I truly do appreciate your help and patience. Jan |
#10
|
|||
|
|||
Hi Steve :-)
Jan, You will see in my example... Me.RecordSource = "SELECT * FROM YourQuery WHERE txtCheckNo Not Like 'DBT*'" ... and then, in you code, you have thrown in a rogue " between the Not and the Like, as in... Me.RecordSource = "SELECT * FROM qryCkRegisterDan WHERE txtCheckNo Not " Like 'DBT*'" And yes, if you have named the form control differently from the field, you should use the field name, so... Me.RecordSource = "SELECT * FROM qryCkRegisterDan WHERE CheckNo Not Like 'DBT*'" Sounds like you have put Option Buttons in the Option Group, whereas I suggested Toggle Buttons... but that's just an aesthetic thing. So, see you you go now! 'k..!! Now here's what I now have, and the Debugger's happy with it. Private Sub fmeOptionGrp_AfterUpdate() Select Case Me.fmeOptionGrp Case 1 Me.RecordSource = "SELECT * FROM qryCkRegisterDan WHERE CheckNo Not Like 'DBT*'" Case 2 Me.RecordSource = "SELECT * FROM qryCkRegisterDan WHERE CheckNo Like 'DBT*'" Case 3 Me.RecordSource = "qryCkRegisterDan" End Select End Sub YES!!! But.....ahmmm......nothing happens when I click the buttons. (?..?) It looks really nice though. :-) What might I have left out? Do I need a command button to go with? Jan -- Steve Schapel, Microsoft Access MVP Jan Il wrote: Hi Steve :-) I hope you will be a bit patient with me on this, as I have never done one of these Option Group thingies before, so I'm purely green as a new lemon. I have created the Option Group using the Wizard, and have the label names Checks, DBTs and All according to your instructions. Now...here is where I am not sure as to how this code should go into the After Update of this Option Group frame. I have entered all 3 of the cases as follows: Private Sub fmeOptionGrp_AfterUpdate() Select Case Me.fmeOptionGrp Case 1 Me.RecordSource = "SELECT * FROM qryCkRegisterDan WHERE txtCheckNo Not " Like 'DBT*'" Case 2 Me.RecordSource = "SELECT * FROM qryCkRegisterDan WHERE txtCheckNo " Like 'DBT*'" Case 3 Me.RecordSource = "qryCkRegisterDan" End Select End Sub The debugger seems to balk at this part of the code in the Case 1 Not " Like 'DBT*'" and the Case 2 " Like 'DBT*'" I am sure I have not entered the correctly, and I do apologize, BUT...it is a starting point. In the query, there is no txtCheckNo, that is the name of the control on the record form. In the query, the field is named CheckNo. So, I am a bit confused with this part of the codes. Also, should there be only one of the cases entered, as you have perhaps given them as an 'either/or type suggestion? I have used the first group of your suggestion to find the footing of how this should work. I truly do appreciate your help and patience. Jan |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Still Hoping for help with a Query problem | Don Sealer | Using Forms | 15 | November 13th, 2004 06:24 AM |
Union Query Not Returning A Value | Jeff G | Running & Setting Up Queries | 2 | October 19th, 2004 05:47 PM |
query a number stored as text | Lee | Running & Setting Up Queries | 19 | October 13th, 2004 04:10 AM |
Using Start Date and End Date in Query | ChuckW | Running & Setting Up Queries | 5 | July 20th, 2004 02:52 AM |
I can see the data but... | David F-B | General Discussion | 3 | June 24th, 2004 06:15 AM |