Filtering between two dates

[DeanEdwards2012]

Hi all, i have an issue with the database im working on:

I want to filter a listbox on a form to a user defined range entered into two text boxes

i have tried entering this into a query for [Date of Breakdown] but when i do so the form goes is blank!?

Between [Forms]![frmMachineDowntime]![txtfromdate] And [Forms]![frmMachineDowntime]![txttodate]

I already have a filter for the query which filters the listbox to display results for a specific machine which works fine

I have attached the form,table, query in this post and would very much appreciate it if someone could take a look for me and explain/show me how to achieve this.

Also, if someone could explain to me how to avoid getting a blank form should their search return no results that would be a massive help!

Thanks in advance

Dean

 

[Mihail]

The problem was that in the table you store the date AND the time (Now() function do this) but you provide only the date as parameters for query.
I tweaked a little bit the table design.
Also I showed you a solution based on a external function (see the query) but now your solution (based on Between statement) should work too - try it.

Let me know if now is OK.
Cheers !

 

[Alansidman]

Amended your SQL statement for your query as follows and it populates properly for me.

SELECT Sum((CInt(Format([Downtime(Hrs)],"hh"))*60)+CInt(Format([Downtime(hrs)],"n"))) AS TotalMins, tblBreakdowns.BreakdownID, tblBreakdowns.[Date of Breakdown], tblBreakdowns.[Machine Name], tblBreakdowns.Fault, tblBreakdowns.[Work Done], tblBreakdowns.[Further Action], tblBreakdowns.[Downtime(Hrs)], tblBreakdowns.Sign
FROM tblBreakdowns
GROUP BY tblBreakdowns.BreakdownID, tblBreakdowns.[Date of Breakdown], tblBreakdowns.[Machine Name], tblBreakdowns.Fault, tblBreakdowns.[Work Done], tblBreakdowns.[Further Action], tblBreakdowns.[Downtime(Hrs)], tblBreakdowns.Sign
HAVING (((tblBreakdowns.[Date of Breakdown]) Between [Forms]![frmMachineDowntime].[txtfromdate] And [Forms]![frmMachineDowntime].[txttodate]) AND ((tblBreakdowns.[Machine Name]) Like "*" & [Forms]![frmMachineDowntime]![cmbMachine] & "*"));

 

[Brianwarnock]

The problem was that in the table you store the date AND the time (Now() function do this) but you provide only the date as parameters for query.

Cheers !

This need not cause a problem as time always exists in a date/time field if not provided it defaults to 0. However I cannot access Access so cannot comment further.

Brian

 

[Brianwarnock]

Alan

As the criteria is not applied to aggregated fields it would be more efficient to replace the Having with a Where statement, no point in doing the work on records not required.

For clarity a Where is applied before aggregation a Having after. If doing the work in the QBE grid drag the field in twice , once for the Group By and once for the Where.

Brian

 

[Mihail]

@Brian
Hi, Brian !
I can not find a solution until I don't change the design for the table (the default value, and, of course, by removing the time from the records).
I am very interested in how YOU solve this. What I don't know here ? Thank you.

 

[Alansidman]

Brian; Agree with you. No grouping is necessary. Didn't notice the aggregation in the OPs original query. I just added the criteria in the QBE, tested it for results (which worked) and then posted the SQL statement. I should have looked at it a little closer. Thanks for pointing that out.

 

[DeanEdwards2012]

Mihail, your db worked ok, but when a search woud return ZERO results the form would go blank for me.

Any ideas on how to prevent this?

Alan, i tried your SQL but the form would not load at all, would be a blank form with no controls

Thanks

Dean

 

[DeanEdwards2012]

My understanding of searching between the two form fields is that a time was not required in the date, like Brian said, a date has a 'Hidden' time of 00:00 attached to it anyway, and since i am only interested in the date it should include all records between yes?

Problem is when it returns no records, the form goes blank and for some reason the expression in the query which i use to calculate the total downtime does not seem to return a consistent and correct result

 

[DeanEdwards2012]

I've attached a screenshot of what happens when i do a successful search (Form is not blank as i actually get results) but the total hours calculation, which normally works returns incorrect totals

Thanks for helping me out in advance, fairly beginner :banghead:

 

[RainLover]

My understanding of searching between the two form fields is that a time was not required in the date, like Brian said, a date has a 'Hidden' time of 00:00 attached to it anyway, and since i am only interested in the date it should include all records between yes?

I don't have 2007 so I cannot help to much, however further to what Brian correctly said a Date is Represented by a number. For example 32453.0000.

The Numbers to the Left of the decimal are the number of days since 30/12/1899 and the decimal part represent part of a day. .7500 would be 6:00 PM.

Time is not stored as 00:00. This is only how it is displayed depending on the Formatting.

If a copy of this Database was posted in 2003 I would have a look at it. But only do that if you run into problems. The current people helping should be able to find a fix for you.

 

[RainLover]

Your Total Downtime should be the Sum of Downtime Hours.

Make sure you are Summing the Value in the Query not the Form.

HTH

 

[DeanEdwards2012]

Thanks Rain, the sum has to be an expression, it was a hurdle i had to overcome earlier in the developing of this thing.

I was after the sum of time, so i needed an expression and formula in a module to work it out correctly, it was converting total time over 24:00 hours back to 00:: and starting again, i needed days, hours and mins so an expression was the only way to achieve this. Thanks anyway

 

[Mihail]

Change this:
1) The control Source for txtDTTBreakdown:

=GetDaysHoursMins()

2) The GetDaysHoursMins() function:

Public Function GetDaysHoursMins() As String
Dim TotalMins
TotalMins = Nz(DSum("TotalMins", "Query1"), 0)
Dim RemMins As Long
Dim iDays As Long, iHours As Long, iMins As Long

    iDays = TotalMins \ 1440
    RemMins = TotalMins Mod 1440
    iHours = RemMins \ 60
    iMins = RemMins Mod 60
    
    GetDaysHoursMins = CStr(iDays) & " Days, " & CStr(iHours) & " Hours, " & CStr(iMins) & " Minutes"
End Function

One more thing:
Use Option Explicit in your VBA modules.

 

[DeanEdwards2012]

Mihail, you sir, are a godsend!

Works now so thanks very much!

 

[RainLover]

Mihail

Well done.

 

[Mihail]

Glad to help
Thank you, Rian. I tried to save in 2003 format for you but Access do not allow this because some features that are not supported in 2003 (don't ask me WHAT features because I have no idea )