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