checking acct form... help with filter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

good day all,

Currently I have two different forms setup along with two different
queries based on my tblcheckingaccout

The reason being is that I have :

frmchecking (continuos form) - qrycheckingaccount - contains only those
records that have not be reconciled (ckbox reconciled is false)

frmcheckingfullrpting (continuos form) - qrycheckingaccountfullrpting
(contains all records)

both the forms have the same layout and fields:

ID
Reconciled
Description
Debit
Credit

there is also a frmcheckingAdd/Edit that has account type, reference,
charge catagory and I open it based on the ID from main form to this form.

What I am wanting to do is have one form, that would default and open up
to all records, but have button (checking acct) and only those records that
matched the following criteria (reconciled = false, account type = checking)
are true.

How would I go about doing this?

Any suggestions?

Thanks,

Brook
 
What do you mean? that is the point of my question...

how do I set up a filter based on two properties


Brook
 
Brook,

Here is a quick example for you that will use the same
button to apply filter and change the button caption as
needed:


If Me.FilterOn = True Then
Me.FilterOn = False
Me!YourCommandButtonName.Caption = "Unreconciled Checks
Only"
Else
Me.Filter = "[Reconciled] = True AND [Account Type] =
'Checking'"
Me.FilterOn = True
Me!YourCommandButtonName.Caption = "Show All Entries"
End If

You will need to modify this for your command button name

HTH
 
Gary,

thnaks for the post, I added the code but am getting a run-time error "2448".

You cannot assign a value to this object.

When I click the debug button it goes to this portion of code:

Me.Filter = "[Reconciled] = True AND [accounttype] =" 'Checking'"

Brook



Gary Miller said:
Brook,

Here is a quick example for you that will use the same
button to apply filter and change the button caption as
needed:


If Me.FilterOn = True Then
Me.FilterOn = False
Me!YourCommandButtonName.Caption = "Unreconciled Checks
Only"
Else
Me.Filter = "[Reconciled] = True AND [Account Type] =
'Checking'"
Me.FilterOn = True
Me!YourCommandButtonName.Caption = "Show All Entries"
End If

You will need to modify this for your command button name

HTH

--
Gary Miller
Sisters, OR

What do you mean? that is the point of my question...

how do I set up a filter based on two properties
Click to expand...
Click to expand...
 
Brook,

It may be that you have an extra quote in your criteria line
after [accounttype]

This...
Me.Filter = "[Reconciled] = True AND [accounttype] ="
'Checking'"
should be...
Me.Filter = "[Reconciled] = True AND [accounttype] =
'Checking'"

Also, double check that your AllowFilters property for the
form is set to true in the Data tab of the form properties.
 
Thank you very much....

That was perfect!

have a wonderful day...

Brook

Gary Miller said:
Brook,

It may be that you have an extra quote in your criteria line
after [accounttype]

This...
Me.Filter = "[Reconciled] = True AND [accounttype] ="
'Checking'"
should be...
Me.Filter = "[Reconciled] = True AND [accounttype] =
'Checking'"

Also, double check that your AllowFilters property for the
form is set to true in the Data tab of the form properties.


--
Gary Miller
Sisters, OR

Brook said:
Gary,

thnaks for the post, I added the code but am getting a
run-time error "2448".

You cannot assign a value to this object.

When I click the debug button it goes to this portion of
code:

Me.Filter = "[Reconciled] = True AND [accounttype] ="
'Checking'"

Brook
Click to expand...
Click to expand...
 
Gary,

I have another question for you, what if I want the accounttype of contain
multiple names for the filter? I.E. 'Checking' and/or 'checking/Expense'.

Thanks,

Brook

Gary Miller said:
Brook,

It may be that you have an extra quote in your criteria line
after [accounttype]

This...
Me.Filter = "[Reconciled] = True AND [accounttype] ="
'Checking'"
should be...
Me.Filter = "[Reconciled] = True AND [accounttype] =
'Checking'"

Also, double check that your AllowFilters property for the
form is set to true in the Data tab of the form properties.


--
Gary Miller
Sisters, OR

Brook said:
Gary,

thnaks for the post, I added the code but am getting a
run-time error "2448".

You cannot assign a value to this object.

When I click the debug button it goes to this portion of
code:

Me.Filter = "[Reconciled] = True AND [accounttype] ="
'Checking'"

Brook
Click to expand...
Click to expand...
 
Brook,

Now you are getting toward full functionality! If you have
covered your bases well with your design, you are pulling
the account types from a table, hopefully using a combobox
to limit the users choices when they fill out a check on the
register.

If you do have a lookup table you can create a combobox to
select between 'Checking', 'Checking/Expense' or whatever
other types you have. Now you can reference the whatever the
content of that combobox is when you run your filter.
Assuming that you are always toggling between 'All' and
different types of unreconciled you could change the earlier
code to something like...

Dim strType as string

' Pick up the value of the combo or zero length string if
empty
strType = Nz(cboAccountType),"")


If Me.FilterOn = True Then
Me.FilterOn = False
Me!YourCommandButtonName.Caption = "Unreconciled Checks
Only"
Else
' If the entry length is longer than zero (a type was
selected)
If Len(strType) > 0 Then
Me.Filter = "[Reconciled] = False AND [accounttype] = '"
&
Me!cboAccountType & "'"
Else
' Show all UnReconciled regardless of type
Me.Filter = "[Reconciled] = False
End If
Me.FilterOn = True
Me!YourCommandButtonName.Caption = "Show All Entries"
End If

Be careful on the line where you reference the combobox. For
a string data type that should read...

[accounttype] equals singlequote doublequote ampersand
Me!cboAccountType ampersand doublequote singlequote
doublequote

Sometimes things like this will be triggered directly from
the combobox AfterUpdate. You will have to modify your
button captions, of course.

--
Gary Miller
Sisters, OR



Brook said:
Gary,

I have another question for you, what if I want the
accounttype of contain
multiple names for the filter? I.E. 'Checking' and/or
'checking/Expense'.

Thanks,

Brook

Gary Miller said:
Brook,

It may be that you have an extra quote in your criteria
line
after [accounttype]

This...
Me.Filter = "[Reconciled] = True AND [accounttype] ="
'Checking'"
should be...
Me.Filter = "[Reconciled] = True AND [accounttype] =
'Checking'"

Also, double check that your AllowFilters property for
the
form is set to true in the Data tab of the form
properties.


--
Gary Miller
Sisters, OR

message
Gary,

thnaks for the post, I added the code but am getting a
run-time error "2448".

You cannot assign a value to this object.

When I click the debug button it goes to this portion
of
code:

Me.Filter = "[Reconciled] = True AND [accounttype] ="
'Checking'"

Brook
Click to expand...
Click to expand...
Click to expand...
 
Gary,

Actually I only want to toggle between the following :

All Records and Records containing Accounttype as (Checking, Checking /
Expense & Checking / Loan).

Is this clear?

Brook

Gary Miller said:
Brook,

Now you are getting toward full functionality! If you have
covered your bases well with your design, you are pulling
the account types from a table, hopefully using a combobox
to limit the users choices when they fill out a check on the
register.

If you do have a lookup table you can create a combobox to
select between 'Checking', 'Checking/Expense' or whatever
other types you have. Now you can reference the whatever the
content of that combobox is when you run your filter.
Assuming that you are always toggling between 'All' and
different types of unreconciled you could change the earlier
code to something like...

Dim strType as string

' Pick up the value of the combo or zero length string if
empty
strType = Nz(cboAccountType),"")


If Me.FilterOn = True Then
Me.FilterOn = False
Me!YourCommandButtonName.Caption = "Unreconciled Checks
Only"
Else
' If the entry length is longer than zero (a type was
selected)
If Len(strType) > 0 Then
Me.Filter = "[Reconciled] = False AND [accounttype] = '"
&
Me!cboAccountType & "'"
Else
' Show all UnReconciled regardless of type
Me.Filter = "[Reconciled] = False
End If
Me.FilterOn = True
Me!YourCommandButtonName.Caption = "Show All Entries"
End If

Be careful on the line where you reference the combobox. For
a string data type that should read...

[accounttype] equals singlequote doublequote ampersand
Me!cboAccountType ampersand doublequote singlequote
doublequote

Sometimes things like this will be triggered directly from
the combobox AfterUpdate. You will have to modify your
button captions, of course.

--
Gary Miller
Sisters, OR



Brook said:
Gary,

I have another question for you, what if I want the
accounttype of contain
multiple names for the filter? I.E. 'Checking' and/or
'checking/Expense'.

Thanks,

Brook

Gary Miller said:
Brook,

It may be that you have an extra quote in your criteria
line
after [accounttype]

This...
Me.Filter = "[Reconciled] = True AND [accounttype] ="
'Checking'"
should be...
Me.Filter = "[Reconciled] = True AND [accounttype] =
'Checking'"

Also, double check that your AllowFilters property for
the
form is set to true in the Data tab of the form
properties.


--
Gary Miller
Sisters, OR

message
Gary,

thnaks for the post, I added the code but am getting a
run-time error "2448".

You cannot assign a value to this object.

When I click the debug button it goes to this portion
of
code:

Me.Filter = "[Reconciled] = True AND [accounttype] ="
'Checking'"

Brook
Click to expand...
Click to expand...
Click to expand...
 
Watch out for the line wrapping on the filter line...

If Me.FilterOn = True Then
Me.FilterOn = False
Me!YourCommandButtonName.Caption = "Unreconciled Checks
Only"
Else
Me.Filter = "[Reconciled] = True AND [Account Type] =
'Checking' OR [Account Type] = ' Checking / Expense ' OR
[Account Type] = 'Checking / Loan'"
Me.FilterOn = True
Me!YourCommandButtonName.Caption = "Show All Entries"
End If
 
Gary,

ok... I set it up and here is my code. But when I run the filter its not
recognizing the Reconciled = False filter portion of the code?

If I only have one Accouttype criteria then it works, but when I add the
multiple accountype criteria's the Reconciled filter isn't funtioning?

Any ideas?

Private Sub cmdfilter_Click()
If Me.FilterOn = True Then
Me.FilterOn = False
Me!cmdfilter.Caption = "Unreconciled Checks Only "
Else
Me.Filter = "[Reconciled] = False AND [accounttype] = 'checking' OR
[accounttype] = 'checking / Expense' or [accounttype] = 'Checking / Personal
Payment' or [accounttype] = 'Checking / Loan' or [accounttype] = 'Checking /
Wire'"
Me.FilterOn = True
Me!cmdfilter.Caption = "Show All Entries"
End If
End Sub


Thanks,

Brook
 
Brook,

Sorry, my fault there. I must have been a in a bit of a fog.
What needs to happen is to wrap the various Account Type
options in parenthesis so that Reconciled = False And
(Checking or Checking / Expense, etc...)

I would think that the best way to have structured this
would actually to have had an additional field so that you
have Account Type = Checking, Savings, or whatever and then
have another field for Expenditure Type = Personal,
Expenditure, Loan, etc...

Here is the code with the paren's...Left paren added after
AND and the right one just before the last double quote.

If Me.FilterOn = True Then
Me.FilterOn = False
Me!YourCommandButtonName.Caption = "Unreconciled Checks
Only"
Else
Me.Filter = "[Reconciled] = True AND ([Account Type] =
'Checking' OR [Account Type] = ' Checking / Expense ' OR
[Account Type] = 'Checking / Loan')"
Me.FilterOn = True
Me!YourCommandButtonName.Caption = "Show All Entries"
End If

--
Gary Miller
Sisters, OR



Brook said:
Gary,

ok... I set it up and here is my code. But when I run the
filter its not
recognizing the Reconciled = False filter portion of the
code?

If I only have one Accouttype criteria then it works, but
when I add the
multiple accountype criteria's the Reconciled filter isn't
funtioning?

Any ideas?

Private Sub cmdfilter_Click()
If Me.FilterOn = True Then
Me.FilterOn = False
Me!cmdfilter.Caption = "Unreconciled Checks Only "
Else
Me.Filter = "[Reconciled] = False AND [accounttype] =
'checking' OR
[accounttype] = 'checking / Expense' or [accounttype] =
'Checking / Personal
Payment' or [accounttype] = 'Checking / Loan' or
[accounttype] = 'Checking /
Wire'"
Me.FilterOn = True
Me!cmdfilter.Caption = "Show All Entries"
End If
End Sub


Thanks,

Brook

Gary Miller said:
Watch out for the line wrapping on the filter line...

If Me.FilterOn = True Then
Me.FilterOn = False
Me!YourCommandButtonName.Caption = "Unreconciled
Checks
Only"
Else
Me.Filter = "[Reconciled] = True AND [Account Type] =
'Checking' OR [Account Type] = ' Checking / Expense ' OR
[Account Type] = 'Checking / Loan'"
Me.FilterOn = True
Me!YourCommandButtonName.Caption = "Show All Entries"
End If
Click to expand...
Click to expand...
 
Brook,

Sorry, my fault there. I must have been a in a bit of a fog.
What needs to happen is to wrap the various Account Type
options in parenthesis so that Reconciled = False And
(Checking or Checking / Expense, etc...)

I would think that the best way to have structured this
would actually to have had an additional field so that you
have Account Type = Checking, Savings, or whatever and then
have another field for Expenditure Type = Personal,
Expenditure, Loan, etc...

Here is the code with the paren's...Left paren added after
AND and the right one just before the last double quote.

If Me.FilterOn = True Then
Me.FilterOn = False
Me!YourCommandButtonName.Caption = "Unreconciled Checks
Only"
Else
Me.Filter = "[Reconciled] = True AND ([Account Type] =
'Checking' OR [Account Type] = ' Checking / Expense ' OR
[Account Type] = 'Checking / Loan')"
Me.FilterOn = True
Me!YourCommandButtonName.Caption = "Show All Entries"
End If


--
Gary Miller
Sisters, OR



Brook said:
Gary,

ok... I set it up and here is my code. But when I run the
filter its not
recognizing the Reconciled = False filter portion of the
code?

If I only have one Accouttype criteria then it works, but
when I add the
multiple accountype criteria's the Reconciled filter isn't
funtioning?

Any ideas?

Private Sub cmdfilter_Click()
If Me.FilterOn = True Then
Me.FilterOn = False
Me!cmdfilter.Caption = "Unreconciled Checks Only "
Else
Me.Filter = "[Reconciled] = False AND [accounttype] =
'checking' OR
[accounttype] = 'checking / Expense' or [accounttype] =
'Checking / Personal
Payment' or [accounttype] = 'Checking / Loan' or
[accounttype] = 'Checking /
Wire'"
Me.FilterOn = True
Me!cmdfilter.Caption = "Show All Entries"
End If
End Sub


Thanks,

Brook

Gary Miller said:
Watch out for the line wrapping on the filter line...

If Me.FilterOn = True Then
Me.FilterOn = False
Me!YourCommandButtonName.Caption = "Unreconciled
Checks
Only"
Else
Me.Filter = "[Reconciled] = True AND [Account Type] =
'Checking' OR [Account Type] = ' Checking / Expense ' OR
[Account Type] = 'Checking / Loan'"
Me.FilterOn = True
Me!YourCommandButtonName.Caption = "Show All Entries"
End If
Click to expand...
Click to expand...
 
Perfect...

Thank you..

And the reason for the different catagories is that I "store" all my account
data in one table.. for reporting to make it easier as well as seting up
reports for tax purposes.

Thanks again for all your help and patience!

Brook

Gary Miller said:
Brook,

Sorry, my fault there. I must have been a in a bit of a fog.
What needs to happen is to wrap the various Account Type
options in parenthesis so that Reconciled = False And
(Checking or Checking / Expense, etc...)

I would think that the best way to have structured this
would actually to have had an additional field so that you
have Account Type = Checking, Savings, or whatever and then
have another field for Expenditure Type = Personal,
Expenditure, Loan, etc...

Here is the code with the paren's...Left paren added after
AND and the right one just before the last double quote.

If Me.FilterOn = True Then
Me.FilterOn = False
Me!YourCommandButtonName.Caption = "Unreconciled Checks
Only"
Else
Me.Filter = "[Reconciled] = True AND ([Account Type] =
'Checking' OR [Account Type] = ' Checking / Expense ' OR
[Account Type] = 'Checking / Loan')"
Me.FilterOn = True
Me!YourCommandButtonName.Caption = "Show All Entries"
End If


--
Gary Miller
Sisters, OR



Brook said:
Gary,

ok... I set it up and here is my code. But when I run the
filter its not
recognizing the Reconciled = False filter portion of the
code?

If I only have one Accouttype criteria then it works, but
when I add the
multiple accountype criteria's the Reconciled filter isn't
funtioning?

Any ideas?

Private Sub cmdfilter_Click()
If Me.FilterOn = True Then
Me.FilterOn = False
Me!cmdfilter.Caption = "Unreconciled Checks Only "
Else
Me.Filter = "[Reconciled] = False AND [accounttype] =
'checking' OR
[accounttype] = 'checking / Expense' or [accounttype] =
'Checking / Personal
Payment' or [accounttype] = 'Checking / Loan' or
[accounttype] = 'Checking /
Wire'"
Me.FilterOn = True
Me!cmdfilter.Caption = "Show All Entries"
End If
End Sub


Thanks,

Brook

Gary Miller said:
Watch out for the line wrapping on the filter line...

If Me.FilterOn = True Then
Me.FilterOn = False
Me!YourCommandButtonName.Caption = "Unreconciled
Checks
Only"
Else
Me.Filter = "[Reconciled] = True AND [Account Type] =
'Checking' OR [Account Type] = ' Checking / Expense ' OR
[Account Type] = 'Checking / Loan'"
Me.FilterOn = True
Me!YourCommandButtonName.Caption = "Show All Entries"
End If
Click to expand...
Click to expand...
Click to expand...
 
Glad to help. Good luck with your project.

--
Gary Miller
Sisters, OR



Brook said:
Perfect...

Thank you..

And the reason for the different catagories is that I
"store" all my account
data in one table.. for reporting to make it easier as
well as seting up
reports for tax purposes.

Thanks again for all your help and patience!

Brook

Gary Miller said:
Brook,

Sorry, my fault there. I must have been a in a bit of a
fog.
What needs to happen is to wrap the various Account Type
options in parenthesis so that Reconciled = False And
(Checking or Checking / Expense, etc...)

I would think that the best way to have structured this
would actually to have had an additional field so that
you
have Account Type = Checking, Savings, or whatever and
then
have another field for Expenditure Type = Personal,
Expenditure, Loan, etc...

Here is the code with the paren's...Left paren added
after
AND and the right one just before the last double quote.

If Me.FilterOn = True Then
Me.FilterOn = False
Me!YourCommandButtonName.Caption = "Unreconciled
Checks
Only"
Else
Me.Filter = "[Reconciled] = True AND ([Account Type] =
'Checking' OR [Account Type] = ' Checking / Expense ' OR
[Account Type] = 'Checking / Loan')"
Me.FilterOn = True
Me!YourCommandButtonName.Caption = "Show All Entries"
End If


--
Gary Miller
Sisters, OR



message
Gary,

ok... I set it up and here is my code. But when I run
the
filter its not
recognizing the Reconciled = False filter portion of
the
code?

If I only have one Accouttype criteria then it works,
but
when I add the
multiple accountype criteria's the Reconciled filter
isn't
funtioning?

Any ideas?

Private Sub cmdfilter_Click()
If Me.FilterOn = True Then
Me.FilterOn = False
Me!cmdfilter.Caption = "Unreconciled Checks Only "
Else
Me.Filter = "[Reconciled] = False AND [accounttype] =
'checking' OR
[accounttype] = 'checking / Expense' or [accounttype] =
'Checking / Personal
Payment' or [accounttype] = 'Checking / Loan' or
[accounttype] = 'Checking /
Wire'"
Me.FilterOn = True
Me!cmdfilter.Caption = "Show All Entries"
End If
End Sub


Thanks,

Brook

:

Watch out for the line wrapping on the filter line...

If Me.FilterOn = True Then
Me.FilterOn = False
Me!YourCommandButtonName.Caption = "Unreconciled
Checks
Only"
Else
Me.Filter = "[Reconciled] = True AND [Account Type]
=
'Checking' OR [Account Type] = ' Checking / Expense '
OR
[Account Type] = 'Checking / Loan'"
Me.FilterOn = True
Me!YourCommandButtonName.Caption = "Show All
Entries"
End If
Click to expand...
Click to expand...
Click to expand...
 
Gary,

I do have one more question for you....

As you know I'm creating this in order to balance and reconcile my
checking account. Is it possible to create / preview a report based on just
the filtered data?

Or as I view "unreconciled transactions" and perform my reconcilations,
preview a report based on the undreconciled transactions filter as well as
the new reconciled records...?

Thanks,

Brook

Gary Miller said:
Glad to help. Good luck with your project.

--
Gary Miller
Sisters, OR



Brook said:
Perfect...

Thank you..

And the reason for the different catagories is that I
"store" all my account
data in one table.. for reporting to make it easier as
well as seting up
reports for tax purposes.

Thanks again for all your help and patience!

Brook

Gary Miller said:
Brook,

Sorry, my fault there. I must have been a in a bit of a
fog.
What needs to happen is to wrap the various Account Type
options in parenthesis so that Reconciled = False And
(Checking or Checking / Expense, etc...)

I would think that the best way to have structured this
would actually to have had an additional field so that
you
have Account Type = Checking, Savings, or whatever and
then
have another field for Expenditure Type = Personal,
Expenditure, Loan, etc...

Here is the code with the paren's...Left paren added
after
AND and the right one just before the last double quote.

If Me.FilterOn = True Then
Me.FilterOn = False
Me!YourCommandButtonName.Caption = "Unreconciled
Checks
Only"
Else
Me.Filter = "[Reconciled] = True AND ([Account Type] =
'Checking' OR [Account Type] = ' Checking / Expense ' OR
[Account Type] = 'Checking / Loan')"
Me.FilterOn = True
Me!YourCommandButtonName.Caption = "Show All Entries"
End If


--
Gary Miller
Sisters, OR



message
Gary,

ok... I set it up and here is my code. But when I run
the
filter its not
recognizing the Reconciled = False filter portion of
the
code?

If I only have one Accouttype criteria then it works,
but
when I add the
multiple accountype criteria's the Reconciled filter
isn't
funtioning?

Any ideas?

Private Sub cmdfilter_Click()
If Me.FilterOn = True Then
Me.FilterOn = False
Me!cmdfilter.Caption = "Unreconciled Checks Only "
Else
Me.Filter = "[Reconciled] = False AND [accounttype] =
'checking' OR
[accounttype] = 'checking / Expense' or [accounttype] =
'Checking / Personal
Payment' or [accounttype] = 'Checking / Loan' or
[accounttype] = 'Checking /
Wire'"
Me.FilterOn = True
Me!cmdfilter.Caption = "Show All Entries"
End If
End Sub


Thanks,

Brook

:

Watch out for the line wrapping on the filter line...

If Me.FilterOn = True Then
Me.FilterOn = False
Me!YourCommandButtonName.Caption = "Unreconciled
Checks
Only"
Else
Me.Filter = "[Reconciled] = True AND [Account Type]
=
'Checking' OR [Account Type] = ' Checking / Expense '
OR
[Account Type] = 'Checking / Loan'"
Me.FilterOn = True
Me!YourCommandButtonName.Caption = "Show All
Entries"
End If
Click to expand...
Click to expand...
Click to expand...
 
Brook,

I believe that this should work:

Use the control wizard to create a command button to open
your report. The wizard should generate code something like
this:

Dim stDocName As String

stDocName = "Your Report Name"
DoCmd.OpenReport stDocName, acPreview

Now change the last line to add your form filter in the
'WHERE' section of the OpenReport command...

DoCmd.OpenReport stDocName, acPreview,,Me.Filter

This will use the same filter that the form is using. Now
you need to do one other thing so that you don't get a
filter when you want all of the records. Add this to the end
of the previous code that we worked on with your command
button turning the filter on and off. In the section that
turns the filter off add this line to make the filter a zero
length string after the line Me.FilterOn = False...

Me.Filter = ""

or

Me.Filter = Null to make it null

Now when the report goes to look at the filter when you are
showing all the records there will be no filter string and
you should get all of your records.

--
Gary Miller
Sisters, OR



Brook said:
Gary,

I do have one more question for you....

As you know I'm creating this in order to balance and
reconcile my
checking account. Is it possible to create / preview a
report based on just
the filtered data?

Or as I view "unreconciled transactions" and perform my
reconcilations,
preview a report based on the undreconciled transactions
filter as well as
the new reconciled records...?

Thanks,

Brook

Gary Miller said:
Glad to help. Good luck with your project.

--
Gary Miller
Sisters, OR



message
Perfect...

Thank you..

And the reason for the different catagories is that I
"store" all my account
data in one table.. for reporting to make it easier as
well as seting up
reports for tax purposes.

Thanks again for all your help and patience!

Brook

:

Brook,

Sorry, my fault there. I must have been a in a bit of
a
fog.
What needs to happen is to wrap the various Account
Type
options in parenthesis so that Reconciled = False And
(Checking or Checking / Expense, etc...)

I would think that the best way to have structured
this
would actually to have had an additional field so that
you
have Account Type = Checking, Savings, or whatever and
then
have another field for Expenditure Type = Personal,
Expenditure, Loan, etc...

Here is the code with the paren's...Left paren added
after
AND and the right one just before the last double
quote.

If Me.FilterOn = True Then
Me.FilterOn = False
Me!YourCommandButtonName.Caption = "Unreconciled
Checks
Only"
Else
Me.Filter = "[Reconciled] = True AND ([Account
Type] =
'Checking' OR [Account Type] = ' Checking / Expense '
OR
[Account Type] = 'Checking / Loan')"
Me.FilterOn = True
Me!YourCommandButtonName.Caption = "Show All
Entries"
End If


--
Gary Miller
Sisters, OR



message
Gary,

ok... I set it up and here is my code. But when I
run
the
filter its not
recognizing the Reconciled = False filter portion of
the
code?

If I only have one Accouttype criteria then it
works,
but
when I add the
multiple accountype criteria's the Reconciled filter
isn't
funtioning?

Any ideas?

Private Sub cmdfilter_Click()
If Me.FilterOn = True Then
Me.FilterOn = False
Me!cmdfilter.Caption = "Unreconciled Checks Only "
Else
Me.Filter = "[Reconciled] = False AND [accounttype]
=
'checking' OR
[accounttype] = 'checking / Expense' or
[accounttype] =
'Checking / Personal
Payment' or [accounttype] = 'Checking / Loan' or
[accounttype] = 'Checking /
Wire'"
Me.FilterOn = True
Me!cmdfilter.Caption = "Show All Entries"
End If
End Sub


Thanks,

Brook

:

Watch out for the line wrapping on the filter
line...

If Me.FilterOn = True Then
Me.FilterOn = False
Me!YourCommandButtonName.Caption = "Unreconciled
Checks
Only"
Else
Me.Filter = "[Reconciled] = True AND [Account
Type]
=
'Checking' OR [Account Type] = ' Checking / Expense
'
OR
[Account Type] = 'Checking / Loan'"
Me.FilterOn = True
Me!YourCommandButtonName.Caption = "Show All
Entries"
End If
Click to expand...
Click to expand...
Click to expand...
 
Thanks Gary,

I will try this and post a reponse...

Brook

Gary Miller said:
Brook,

I believe that this should work:

Use the control wizard to create a command button to open
your report. The wizard should generate code something like
this:

Dim stDocName As String

stDocName = "Your Report Name"
DoCmd.OpenReport stDocName, acPreview

Now change the last line to add your form filter in the
'WHERE' section of the OpenReport command...

DoCmd.OpenReport stDocName, acPreview,,Me.Filter

This will use the same filter that the form is using. Now
you need to do one other thing so that you don't get a
filter when you want all of the records. Add this to the end
of the previous code that we worked on with your command
button turning the filter on and off. In the section that
turns the filter off add this line to make the filter a zero
length string after the line Me.FilterOn = False...

Me.Filter = ""

or

Me.Filter = Null to make it null

Now when the report goes to look at the filter when you are
showing all the records there will be no filter string and
you should get all of your records.

--
Gary Miller
Sisters, OR



Brook said:
Gary,

I do have one more question for you....

As you know I'm creating this in order to balance and
reconcile my
checking account. Is it possible to create / preview a
report based on just
the filtered data?

Or as I view "unreconciled transactions" and perform my
reconcilations,
preview a report based on the undreconciled transactions
filter as well as
the new reconciled records...?

Thanks,

Brook

Gary Miller said:
Glad to help. Good luck with your project.

--
Gary Miller
Sisters, OR



message
Perfect...

Thank you..

And the reason for the different catagories is that I
"store" all my account
data in one table.. for reporting to make it easier as
well as seting up
reports for tax purposes.

Thanks again for all your help and patience!

Brook

:

Brook,

Sorry, my fault there. I must have been a in a bit of
a
fog.
What needs to happen is to wrap the various Account
Type
options in parenthesis so that Reconciled = False And
(Checking or Checking / Expense, etc...)

I would think that the best way to have structured
this
would actually to have had an additional field so that
you
have Account Type = Checking, Savings, or whatever and
then
have another field for Expenditure Type = Personal,
Expenditure, Loan, etc...

Here is the code with the paren's...Left paren added
after
AND and the right one just before the last double
quote.

If Me.FilterOn = True Then
Me.FilterOn = False
Me!YourCommandButtonName.Caption = "Unreconciled
Checks
Only"
Else
Me.Filter = "[Reconciled] = True AND ([Account
Type] =
'Checking' OR [Account Type] = ' Checking / Expense '
OR
[Account Type] = 'Checking / Loan')"
Me.FilterOn = True
Me!YourCommandButtonName.Caption = "Show All
Entries"
End If


--
Gary Miller
Sisters, OR



message
Gary,

ok... I set it up and here is my code. But when I
run
the
filter its not
recognizing the Reconciled = False filter portion of
the
code?

If I only have one Accouttype criteria then it
works,
but
when I add the
multiple accountype criteria's the Reconciled filter
isn't
funtioning?

Any ideas?

Private Sub cmdfilter_Click()
If Me.FilterOn = True Then
Me.FilterOn = False
Me!cmdfilter.Caption = "Unreconciled Checks Only "
Else
Me.Filter = "[Reconciled] = False AND [accounttype]
=
'checking' OR
[accounttype] = 'checking / Expense' or
[accounttype] =
'Checking / Personal
Payment' or [accounttype] = 'Checking / Loan' or
[accounttype] = 'Checking /
Wire'"
Me.FilterOn = True
Me!cmdfilter.Caption = "Show All Entries"
End If
End Sub


Thanks,

Brook

:

Watch out for the line wrapping on the filter
line...

If Me.FilterOn = True Then
Me.FilterOn = False
Me!YourCommandButtonName.Caption = "Unreconciled
Checks
Only"
Else
Me.Filter = "[Reconciled] = True AND [Account
Type]
=
'Checking' OR [Account Type] = ' Checking / Expense
'
OR
[Account Type] = 'Checking / Loan'"
Me.FilterOn = True
Me!YourCommandButtonName.Caption = "Show All
Entries"
End If
Click to expand...
Click to expand...
Click to expand...
 
Gary,

I hope this doesn't post twice...

Here is the code I added to the filter button...

Private Sub cmdfilter_Click()
If Me.FilterOn = True Then
Me.FilterOn = False
Me!cmdfilter.Caption = "Unreconciled Checks Only "
Else
Me.Filter = "[Reconciled] = False AND ([accounttype] = 'checking' OR
[accounttype] = 'checking / Expense' or [accounttype] = 'Checking / Personal
Payment' or [accounttype] = 'Checking / Loan' or [accounttype] = 'Checking /
Wire')"
Me.FilterOn = True
Me.Filter = ""
Me!cmdfilter.Caption = "Show All Entries"

End If
End Sub

the Me.Filter ="" doesn't change anything on the opening of the report, and
the button never changes names between show all and unreconciled captions?

the other portion of your suggestion works fine... as far as the opening the
report with the Me.filter...

When I "Check" new records for reconcilation, will the report show those?



Thanks,

Brook

Gary Miller said:
Brook,

I believe that this should work:

Use the control wizard to create a command button to open
your report. The wizard should generate code something like
this:

Dim stDocName As String

stDocName = "Your Report Name"
DoCmd.OpenReport stDocName, acPreview

Now change the last line to add your form filter in the
'WHERE' section of the OpenReport command...

DoCmd.OpenReport stDocName, acPreview,,Me.Filter

This will use the same filter that the form is using. Now
you need to do one other thing so that you don't get a
filter when you want all of the records. Add this to the end
of the previous code that we worked on with your command
button turning the filter on and off. In the section that
turns the filter off add this line to make the filter a zero
length string after the line Me.FilterOn = False...

Me.Filter = ""

or

Me.Filter = Null to make it null

Now when the report goes to look at the filter when you are
showing all the records there will be no filter string and
you should get all of your records.

--
Gary Miller
Sisters, OR



Brook said:
Gary,

I do have one more question for you....

As you know I'm creating this in order to balance and
reconcile my
checking account. Is it possible to create / preview a
report based on just
the filtered data?

Or as I view "unreconciled transactions" and perform my
reconcilations,
preview a report based on the undreconciled transactions
filter as well as
the new reconciled records...?

Thanks,

Brook

Gary Miller said:
Glad to help. Good luck with your project.

--
Gary Miller
Sisters, OR



message
Perfect...

Thank you..

And the reason for the different catagories is that I
"store" all my account
data in one table.. for reporting to make it easier as
well as seting up
reports for tax purposes.

Thanks again for all your help and patience!

Brook

:

Brook,

Sorry, my fault there. I must have been a in a bit of
a
fog.
What needs to happen is to wrap the various Account
Type
options in parenthesis so that Reconciled = False And
(Checking or Checking / Expense, etc...)

I would think that the best way to have structured
this
would actually to have had an additional field so that
you
have Account Type = Checking, Savings, or whatever and
then
have another field for Expenditure Type = Personal,
Expenditure, Loan, etc...

Here is the code with the paren's...Left paren added
after
AND and the right one just before the last double
quote.

If Me.FilterOn = True Then
Me.FilterOn = False
Me!YourCommandButtonName.Caption = "Unreconciled
Checks
Only"
Else
Me.Filter = "[Reconciled] = True AND ([Account
Type] =
'Checking' OR [Account Type] = ' Checking / Expense '
OR
[Account Type] = 'Checking / Loan')"
Me.FilterOn = True
Me!YourCommandButtonName.Caption = "Show All
Entries"
End If


--
Gary Miller
Sisters, OR



message
Gary,

ok... I set it up and here is my code. But when I
run
the
filter its not
recognizing the Reconciled = False filter portion of
the
code?

If I only have one Accouttype criteria then it
works,
but
when I add the
multiple accountype criteria's the Reconciled filter
isn't
funtioning?

Any ideas?

Private Sub cmdfilter_Click()
If Me.FilterOn = True Then
Me.FilterOn = False
Me!cmdfilter.Caption = "Unreconciled Checks Only "
Else
Me.Filter = "[Reconciled] = False AND [accounttype]
=
'checking' OR
[accounttype] = 'checking / Expense' or
[accounttype] =
'Checking / Personal
Payment' or [accounttype] = 'Checking / Loan' or
[accounttype] = 'Checking /
Wire'"
Me.FilterOn = True
Me!cmdfilter.Caption = "Show All Entries"
End If
End Sub


Thanks,

Brook

:

Watch out for the line wrapping on the filter
line...

If Me.FilterOn = True Then
Me.FilterOn = False
Me!YourCommandButtonName.Caption = "Unreconciled
Checks
Only"
Else
Me.Filter = "[Reconciled] = True AND [Account
Type]
=
'Checking' OR [Account Type] = ' Checking / Expense
'
OR
[Account Type] = 'Checking / Loan'"
Me.FilterOn = True
Me!YourCommandButtonName.Caption = "Show All
Entries"
End If
Click to expand...
Click to expand...
Click to expand...
 
Back
Top