Question Query by date and checkbox

[raziel3]

I have a set of records with a checkbox. When the checkbox is 'checked' a corresponding field is automatically filled with the date (Field=Sdate). For example if the ending period is 1/31/16 once 'checked' Sdate=1/31/16. I want to be able make a query to only return the records that is not 'checked' within that period. For example if I query all records by (Field=Date Paid) between 3/1/16 to 3/31/16 all the records within that period is shown and records not 'checked' between 1/1/16 to 2/29/16.

The problem is whenever the records that are not checked are checked in a future period they do not show up in the query because the query sees it as being checked already. For example if a record in January is checked in July and I run the query for March period the January record does not show up. Can anyone help me please.

I've attached my db for reference. Table 'Data' is the records I would like to have filtered based on the criteria.

 

[mike60smart]

Hi

What criteria in your query are you using to get your Date Range??

 

[mike60smart]

Hi
If you use this as your criteria:-



It will return the following 17 Records

 

[Mark_]

Just to be clear, checking the box fills [Sdate] with the current date?

If true, then your report should not be looking for the check box to be checked, it should be looking for [Sdate] in period (or before, if you also don't want those).

NOTE: Personally I would remove the spaces in field names. I would normally have "Date Paid" and "Entry Date" as Dt_Paid and Dt_Entered, just to make sure I don't accidentally use a reserved word where it aught not be.

 

[raziel3]

Basically what I am trying to achieve is a Bank Reconciliation. If you filter the Data Table by a period lets say 1/1/2016 to 1/31/2016, I will use the checkbox to 'clear' the records that appear on the bank statement. Once 'cleared' it fill [Sdate] with the bank statement date '1/31/2016' for February [Sdate]='2/29/16'.

Want I need is for the items 'not cleared' to appear in the next period ie '2/29/2016'. I've achieved this part but what is happening is, if a record is then cleared in a future period like a record with [Date Paid]='1/28/16' is cleared on '3/31/16' if i go back and generate a query for '2/29/16' I am not seeing the record for '1/28/16' as not cleared in that period.

So the goal is to have a query that gets all unrepresented cheques for a period for example:

Period end 1/31/16 may have 5 unpresented cheques
Period end 2/28/16 may have 4 unpresented cheques (2 from January and 2 from February) and so on


I do hope I'm making sense.

 

[Pat Hartman]

Your criteria needs to use 2 conditions

Where somedate between firstdate and second date OR cleared = false

Make sure that your check field defaults to false instead of null and run an update query to set all current null values to false if you need to fix existing data.

 

[arnelgp]

see if this is what you
need. adjust the code
if necessary (sdate afterupdate).

Private Sub Form_Current()
On Error Resume Next
With Me.bankrecsub.Form
    .RecordSource = "SELECT * FROM DATA WHERE " & _
    "([Cleared] = False AND [Date Paid] <= #" & Me.SDATE & "#) OR (NZ([STATD],#12,31,9000#)<#" & Me.SDATE & "#)"
    'Filter = "([Cleared] = False AND [Date Paid] <= #" & Me.SDATE & "#) OR (NZ([STATD],#12,31,9000#)<#" & Me.SDATE & "#)"
   '.Filter = "[Date Paid] BETWEEN #" & Me.PB & "# AND #" & Me.SDATE & "# and [StatD]<> #" & Me.SDATE & "#"
   '.FilterOn = True
   '.Requery
End With
End Sub

Private Sub SDATE_AfterUpdate()
On Error Resume Next
With Me.bankrecsub.Form
    .RecordSource = "SELECT * FROM DATA WHERE " & _
    "([Cleared] = False AND [Date Paid] <= #" & Me.SDATE & "#) OR (NZ([STATD],#12,31,9000#)<#" & Me.SDATE & "#)"
    'Filter = "([Cleared] = False AND [Date Paid] <= #" & Me.SDATE & "#) OR (NZ([STATD],#12,31,9000#)<#" & Me.SDATE & "#)"
    '.Filter = "[Date Paid] BETWEEN #" & Me.PB & "# AND #" & Me.SDATE & "#"
    '.FilterOn = True
    '.Requery
End With

End Sub

 

[raziel3]

Hi and thanks for the code. I modified it a little

.RecordSource = "SELECT * FROM DATA WHERE " & _
    "([Date Paid] <= #" & Me.SDATE & "#) "
     .Filter = "[Date Paid] BETWEEN #" & Me.PB & "# AND #" & Me.SDATE & "# and [StatD]<> #" & Me.SDATE & "#"
    .FilterOn = True
    .Requery

It's filtering the unpresented cheques within the period but it's not carrying forward the unpresented cheques. For example if a cheque outstanding in January that is 'Cleared' in March, is not showing up in the February recordsource. I realized that filtering by the checkbox is not returning the records I want so I left it out of the filter.

 

[arnelgp]

don't use Filter. that is why
i commented out (') the filter on
the code.
the code i gave you is the code
from your Main Form.
Change recordsource, and not
Filter.

 

[Mark_]

Hi and thanks for the code. I modified it a little

.RecordSource = "SELECT * FROM DATA WHERE " & _
    "([Date Paid] <= #" & Me.SDATE & "#) "
     .Filter = "[Date Paid] BETWEEN #" & Me.PB & "# AND #" & Me.SDATE & "# and [StatD]<> #" & Me.SDATE & "#"
    .FilterOn = True
    .Requery

It's filtering the unpresented cheques within the period but it's not carrying forward the unpresented cheques. For example if a cheque outstanding in January that is 'Cleared' in March, is not showing up in the February recordsource. I realized that filtering by the checkbox is not returning the records I want so I left it out of the filter.

arnelgp's sample includes the one piece you are missing; the OR that should add in all cheque's not presented. Have you tried simply using his code?

 

[raziel3]

Removed the filters but still no luck. The problem I'm having is that "[Cleared] = False" part of the code because if an unpresented cheque for January is presented in June then it becomes Cleared=True so it will not show up anymore. So if I wanted to check what was outstanding in either January, February, March, April or May that cheque will not be seen. How do I achieve this.

 

[arnelgp]

Copy the code i gave u. Open your main form in design view. Replace the form current event with my code. Replace the afterupdate event of your dated textbox with my code.

You dont pay attention to what we are saying.

 

[raziel3]

That is what I did.

 

[Mark_]

Raziel3,
What field holds the date the cheque cleared? Don't look at it as "Did this cheque clear = Yes/No", think of it as "When did this cheque clear = DATE or null". Save the date the cheque cleared as it both answered "Did it clear?" and "When did it clear?" which is required for you to include it during periods it had not cleared.

 

[raziel3]

This is what I came up with for the Form_Current Event

.RecordSource = "SELECT * FROM DATA WHERE " & _
    "([StatD] > #" & Me.SDATE & "# AND [Date Paid] <= #" & Me.SDATE & "#)"

It filters all the unpresented cheques.

 

[raziel3]

Raziel3,
What field holds the date the cheque cleared? Don't look at it as "Did this cheque clear = Yes/No", think of it as "When did this cheque clear = DATE or null". Save the date the cheque cleared as it both answered "Did it clear?" and "When did it clear?" which is required for you to include it during periods it had not cleared.

Mark, You gave me an idea, so I tried this on SDATE_AfterUpdate event

.RecordSource = "SELECT * FROM DATA WHERE " & _
    "(IsNull([StatD]) = True AND [Date Paid] <= #" & Me.SDATE & "#)"

Everything works perfectly. Thanks everyone for your help.

 

[arnelgp]

You might want to test this one

 

[Pat Hartman]

In most applications, you do not have the ability to run a report TODAY that shows whatever was current YESTERDAY. If this is a requirement, you have a huge schema change and probably a complete rewrite ahead of yourself. You will need to store copies of each record as the record existed prior to the change. Your queries and code will then need to rebuild records on the fly by starting with the original version and working forward in time to update to the record's state on a particular date.

If all you care about tracking is status changes, then you can probably do this by keeping a separate date for each status. You will also need to keep a current status and date otherwise your queries will be very difficult.

 

[raziel3]

Thanks for the advice Mr. Hartman. I'm not sure if you're familiar with Peachtree. I'm trying to emulate how Peachtree deals with account reconciliations, so for each period (1st to the last day of a month) I want to capture all unreconciled items ie any unpresented cheques or uncleared deposits. This is how Peachtree generates account reconciliations for a given period.

 

[raziel3]

You might want to test this one

This is not doing what i want. arnelgp I've attached one for you to look at to see exactly what I wanted. Open the Form 'BankRec' normally and cycle through the records. This is like a review of what was outstanding each month

Then open it up from the button on the 'Home' page fill in the feilds 'NACC', 'FCBNACC' and SDATE=7/31/2016.

These are the records that I wanted to display. From there the user can do the reconciliation for that period.