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