Need query to separate 2 entry types in a table field

[Jan Il]

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

 

[Steve Schapel]

Jan,

How do you tell if it is a Check or DBT transaction?

 

[Jan Il]

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

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

 

[Steve Schapel]

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

 

[Jan Il]

Hi Steve

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]

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

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

Jan

 

[Jan Il]

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]

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!

 

[Jan Il]

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]

Jan,

The Case statements will refer to the Option Value of the Option Button
that you select in the Option Group. Enough options for you? Look at
the properties of each of the Option Buttons, and see if the Option
Value property is set to 1, 2, and 3 respectively for the 3 of them.

 

[Jeff Conrad]

Hi Jan,

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

Looks to me like you are missing the all important line of code to tell
Access to requery the form.

Add this line right below the End Select:

Me.Form.Requery

 

[Steve Schapel]

Interesting thought, Jeff. But... why?

 

[Jan Il]

Hi Steve

Jan,

The Case statements will refer to the Option Value of the Option Button
that you select in the Option Group. Enough options for you? Look at
the properties of each of the Option Buttons, and see if the Option
Value property is set to 1, 2, and 3 respectively for the 3 of them.

Yes, I did give each of the buttons a 1, 2 or 3 Value, respectively, as I
assumed that was the intended setup for the Case statement.

However, I *think* the reason why it is not opening or displaying anything,
is that I understood it was to go on the Filter form, and there is nothing
to be displayed on the Filter form. It's just the form where all the
sorting is done and then the Record form is opened to displayed the various
records as sorted from the Filter form. Should this Option Group frame be
on the Record form in order to display the relative records accordingly?

Jan

 

[Jan Il]

Hey Jeff!

Hi Jan,


Looks to me like you are missing the all important line of code to tell
Access to requery the form.

Add this line right below the End Select:

Me.Form.Requery

'k...I put this in and tried it, but, I'm still not getting any response
from the buttons. I think, as I mentioned to Steve in my reply to him, that
I may have it on the wrong form, and I need to do this on the record form,
or....maybe I need to tell it to open the Record form to display the
relative data for each button. Either that, or I've still done something
correctly.

Thank you for the additional help....I really appreciate it.

Jan

 

[Tom Ellison]

Changing the RecordSource of a form always causes a Requery. Doing it
again is just a waste of time.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Steve Schapel]

Jan,

I know you have been using these terms all the way through, and I have
so far not questioned. But I have no idea at all what you mean by a
Record Form and a Filter Form. I have re-read all your posts, including
the last one, and I'm afraid I still can't imagine what you might be
doing. At some point, it might become relevant for me to know, I'm not
sure.

However, the method we have been discussing assumes that the Option
Group is on the form that is displaying the records that you are
manipulating. If, for some reason, you want the buttons to be on one
form, and manipulate the records shown on another form, well... this
would be unusual, but is certainly possible.

 

[Jan Il]

Hi Steve

Jan,

I know you have been using these terms all the way through, and I have
so far not questioned. But I have no idea at all what you mean by a
Record Form and a Filter Form. I have re-read all your posts, including
the last one, and I'm afraid I still can't imagine what you might be
doing. At some point, it might become relevant for me to know, I'm not
sure.

The Record Form is a form on which the selected data is displayed after is
has been sorted/specified on the Filter Form. The Filter Form is the form
where there's a series of combo boxes that have a list of data to select
from, such as, Transactions, Transaction types, etc., and associated command
buttons with code that calls the Record form to open and display only the
specific record information selected from the Filter Form controls. Such
as; if I select Auto Expense from the list of Transaction types in a combo
box on the Filter Form, then click the command button next to it, the Record
Form is then opened only displaying all of the Auto Expenses entered.

The Record Form does nothing but display the data called from the activities
that takes place on the Filter form. Example: here's the code behind one
of the command buttons for one of the combo boxes on the Filter Form:

Private Sub cmdCheckNo_Click()
On Error GoTo Err_cmdCheckNo_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCheckRegister"

stLinkCriteria = "[CheckNo]=" & "'" & Me![cmbCheckNo] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdCheckNo_Click:
Exit Sub

Err_cmdCheckNo_Click:
MsgBox Err.Description
Resume Exit_cmdCheckNo_Click

End Sub

Hopefully, this will help clarify what type of form I mean by 'Filter Form.'
I have seen such filtering/sorting forms in various posts in the ng's called
by many different terms or names (driver form, for one), but, I am not
familiar with any one particular term for this kind of form. However, if
there is a specific name for it, please let me know and I'll be very happy
to use it in order to eliminate confusion for others as well. I just call
it a Filter Form because...... well.... that's what it does. )

However, the method we have been discussing assumes that the Option
Group is on the form that is displaying the records that you are
manipulating. If, for some reason, you want the buttons to be on one
form, and manipulate the records shown on another form, well... this
would be unusual, but is certainly possible.

If possible, I would like to keep all the record sorting activities on the
sorting form (Filter Form), so that there will only be the specific data
records displayed on the Record Form, and no other activities required other
than scrolling to review the information. All the users need to do is
review the information, they don't need to do anything with it.

I really do like the look and function of the Option Group, and I'm sure
this will work adequately, if there is a way to code it so that when you
click one of the buttons, the Record Form opens, and just the data for that
button is displayed. As there are only these 3 functions necessary for this
purpose, this would be a very quick and simple way to handle the sorting.
However, is this type of function possible using the Option Group?

Thank you so much for your time and patience, Steve, it's very much
appreciated.

Jan

 

[Jeff Conrad]

My newsreader has lost the beginning messages of this thread so I'm acting on half the information,
but from what I saw it looked like the record source of the form was being changed in code and Jan
said, "Nothing happened."

I have seen on occasion where changing the record source of the form in code does not *always* cause
the form in question to be requeried. I cannot remember the exact circumstances and/or Access
version involved, but you'll just have to trust me. So I thought maybe a Requery line would help.

I'll bow out now.

Only other thing I can think of is that the property sheet does not have [Event Procedure] listed
for the After Update of the control in question so Access does not do anything. Just a random
thought.

 

[Jan Il]

Hi Jeff

My newsreader has lost the beginning messages of this thread so I'm acting on half the information,
but from what I saw it looked like the record source of the form was being changed in code and Jan
said, "Nothing happened."

I have seen on occasion where changing the record source of the form in code does not *always* cause
the form in question to be requeried. I cannot remember the exact circumstances and/or Access
version involved, but you'll just have to trust me. So I thought maybe a Requery line would help.

I'll bow out now.

Only other thing I can think of is that the property sheet does not have [Event Procedure] listed
for the After Update of the control in question so Access does not do anything. Just a random
thought.

I think the problem here is, that I was talking about the Filter Form, not
the Record or Data Form, which you two must have been thinking I was talking
about. Thus, the Option Group as presented by Steve may have worked had I
applied it to the Record Form. However, I have explained the situation to
Steve in my other reply, and would rather keep the sorting activities just
on the Filter (?) Form, and not the Record or data form, if possible. This
is in order to keep the Record form merely a data review only source, so
that the can only scroll to review the data and nothing more. They will not
have to do anything with the data other than to review it for data
confirmation.

I really do appreciate your time and input any time.

Jan

BTW - 177 days>3 hrs>4 mins>27 sec...... and counting........ ;o)