J
Jan Il
Hi all - W2K, Access 2002 XP
I have a filter form on which I have a combo box which is to list the item
DBT. I have a Command button for it that is to call up all the DBT records.
However, I also have text box named txtDage1 and one named txtDate2, which I
use to enter a date From - To period to review data for the various
selctions from the form and displayed on the Record Form.
What I need is to be able to click the command button, and have it open the
Record form and display only the DBT records for the time period in the
date1 and 2 boxes.
Here is the problem in getting the right coding to do this correctly. I
have a table field in which I enter both the debit card transactions which
are sequentially numbered automatically, and the check numbers. Thus, this
field can look like:
1001
1002
DBT00001
1003
1004
DBT00002
Now...what has to happen is to somehow separate the DBT entries from the
check number entries, so that I can call up only the DBT entries. I have
tried to do this by creating a query to separate the DBT from the check
numbers and their dates, but, so far I can't find the right combination. I
have a current query that I can use for the most part, but, this one part
here is a bit difficult to determine the correct code for.
I have an Option Group on another form that does separate the DBT
transactions, but, I don't think I can use that with the From-To date
process as well. Here is the code that I have for the OG, so you can see how
it separates out the DBT entries for it's purpose.
***************Start code********************
Private Sub fmeOptionGrp_AfterUpdate()
DoCmd.OpenForm "frmCheckRegister"
With Forms!frmCheckRegister
Select Case Me.fmeOptionGrp
Case 1
.RecordSource = "SELECT * FROM qryCkRegisterDan WHERE CheckNo Not
Like 'DBT*'"
Case 2
.RecordSource = "SELECT * FROM qryCkRegisterDan WHERE CheckNo Like
'DBT*'"
Case 3
.RecordSource = "qryCkRegisterDan"
End Select
End With
End Sub
********************End code*******************
I have not been to figure out how to get the DBT entries separated from the
table field so that they can be dealt with separately. Here is what I have
tried to do, but, of course, it is not working, as there is currently no DBT
'field' for it to find.
What do I need to add to the code to tell it to look for only the DBT
entries in the table. Or, if I need a query to sort the DBT entry out, how
do I set that up? I've been trying to create a query to separate the DBT's.
I have tried a LookUp, DLookUp, LookDown, ULookUp, A Select, Elect, Pick,
Choose, Call, Name, Callitaname, I'vecalleditaname, Unameit, Like, Unlike,
Dislike, Ulike, UpDate, After Update, BeforeUpdate, PreNuptial,
DivorceCourt, Where, What, When, and WhoCares...but, nothing has worked thus
far.
I need to be able to call up these DBT type transactions using the command
button and the From date (txtDate1) and To date (txtDate2) so that these
transavtions can be reviewed during a specific time period, such as From
11/01/04 To 11/25/04, and any DBT transactions entered will be displayed on
the opened 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.TransactionDate DESC;
I would truly appreciate any suggestions on the proper method of pulling out
this entry data. Here is the SQL for the current query. Is there some way
that I can use this query to do this, or do I need to create a new query.
Jan
Smiles are meant to be shared,
that's why they're so contagious.
I have a filter form on which I have a combo box which is to list the item
DBT. I have a Command button for it that is to call up all the DBT records.
However, I also have text box named txtDage1 and one named txtDate2, which I
use to enter a date From - To period to review data for the various
selctions from the form and displayed on the Record Form.
What I need is to be able to click the command button, and have it open the
Record form and display only the DBT records for the time period in the
date1 and 2 boxes.
Here is the problem in getting the right coding to do this correctly. I
have a table field in which I enter both the debit card transactions which
are sequentially numbered automatically, and the check numbers. Thus, this
field can look like:
1001
1002
DBT00001
1003
1004
DBT00002
Now...what has to happen is to somehow separate the DBT entries from the
check number entries, so that I can call up only the DBT entries. I have
tried to do this by creating a query to separate the DBT from the check
numbers and their dates, but, so far I can't find the right combination. I
have a current query that I can use for the most part, but, this one part
here is a bit difficult to determine the correct code for.
I have an Option Group on another form that does separate the DBT
transactions, but, I don't think I can use that with the From-To date
process as well. Here is the code that I have for the OG, so you can see how
it separates out the DBT entries for it's purpose.
***************Start code********************
Private Sub fmeOptionGrp_AfterUpdate()
DoCmd.OpenForm "frmCheckRegister"
With Forms!frmCheckRegister
Select Case Me.fmeOptionGrp
Case 1
.RecordSource = "SELECT * FROM qryCkRegisterDan WHERE CheckNo Not
Like 'DBT*'"
Case 2
.RecordSource = "SELECT * FROM qryCkRegisterDan WHERE CheckNo Like
'DBT*'"
Case 3
.RecordSource = "qryCkRegisterDan"
End Select
End With
End Sub
********************End code*******************
I have not been to figure out how to get the DBT entries separated from the
table field so that they can be dealt with separately. Here is what I have
tried to do, but, of course, it is not working, as there is currently no DBT
'field' for it to find.
What do I need to add to the code to tell it to look for only the DBT
entries in the table. Or, if I need a query to sort the DBT entry out, how
do I set that up? I've been trying to create a query to separate the DBT's.
I have tried a LookUp, DLookUp, LookDown, ULookUp, A Select, Elect, Pick,
Choose, Call, Name, Callitaname, I'vecalleditaname, Unameit, Like, Unlike,
Dislike, Ulike, UpDate, After Update, BeforeUpdate, PreNuptial,
DivorceCourt, Where, What, When, and WhoCares...but, nothing has worked thus
far.
I need to be able to call up these DBT type transactions using the command
button and the From date (txtDate1) and To date (txtDate2) so that these
transavtions can be reviewed during a specific time period, such as From
11/01/04 To 11/25/04, and any DBT transactions entered will be displayed on
the opened 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.TransactionDate DESC;
I would truly appreciate any suggestions on the proper method of pulling out
this entry data. Here is the SQL for the current query. Is there some way
that I can use this query to do this, or do I need to create a new query.
Jan
Smiles are meant to be shared,
that's why they're so contagious.