Can you create a start and end date out of the same column of data (1 Viewer)

Access N0vice · Mar 14, 2018

Hello all,
I have this database that has information taken from equipment that has hour meters. My data fields have the day the equipment hours were taken the serial number of the equipment and the meter reading. what I need is a report that gives the total of hours run for any given month. I need to get the hours of the equipment at the first of the month and then the hours at the last of the month to get the total usage. So lets say the person getting the information collects it on the 4th of March to start then gets information during the month 10 other times finishing on the 28th. how do I get the information from just the 4th for the beginning or (startdate) and the 28th for the end or (Enddate)? is it possible?

Mark_ · Mar 14, 2018

Yes.

If you know the dates, use something like the below, but filled in with your tables/fields/sources for data;

FirstRead = DLookup("[FieldName]","TableName", "MeterID = " & Me.MeterID & " AND DateRead = " & Me.DateFrom)

LastRead = DLookup("[FieldName]","TableName", "MeterID = " & Me.MeterID & " AND DateRead = " & Me.DateThru)

Then you would do LastRead - FirstRead to get your usage.

Ranman256 · Mar 14, 2018

make a field called "usage"
this code uses a query: qsMeterReads, that sorts by machine,then time
alter the field names to match yours.

the query will limit to the date range.

Code:

Public Sub CalcUsage()
Dim c As Integer
Dim lDiff As Double, lCurr As Double, lPrev As Double
Dim vMachine, vStart, vPrevMach
Dim rst

Set rst = CurrentDb.OpenRecordset("qsMeterReads")
vStart = Now
lPrev = 0
With rst
    While Not .EOF

        vMachine = .Fields("Machine").Value
        lCurr = .Fields("Reading").Value
                
         If vPrevMach <> vMachine Then
             lDiff = 0
         Else
             lDiff = lCurr - lPrev
         End If
             
            'set the difference from the last reading
        .Fields("Usage").Value = lDiff
        .Update
        
        lPrev = lCurr
        vPrevMach = vMachine
    
    .MoveNext
Wend
end with
Debug.Print DateDiff("n", vStart, Now) & " minutes"
End Sub

Mark_ · Mar 14, 2018

Ranman256 said:

make a field called "usage"
this code uses a query: qsMeterReads, that sorts by machine,then time
alter the field names to match yours.

the query will limit to the date range.

Code:

Public Sub CalcUsage()
Dim c As Integer
Dim lDiff As Double, lCurr As Double, lPrev As Double
Dim vMachine, vStart, vPrevMach
Dim rst

Set rst = CurrentDb.OpenRecordset("qsMeterReads")
vStart = Now
With rst
    While Not .EOF
        [COLOR="Red"]lPrev = 0[/COLOR]
        vMachine = .Fields("Machine").Value
        lCurr = .Fields("Reading").Value
                
         If vPrevMach <> vMachine Then
             lDiff = 0
         Else
             lDiff = lCurr - lPrev
         End If
             
            'set the difference from the last reading
        .Fields("Usage").Value = lDiff
        .Update
        
        lPrev = lCurr
        vPrevMach = vMachine
    
    .MoveNext
Wend
Debug.Print DateDiff("n", vStart, Now) & " minutes"
End Sub

Question, why declare and use lPrev if you will always set it to 0 for each record? You would need to move the lPrev = 0 BEFORE the while to have this actually do something other than be 0.

Ranman256 · Mar 14, 2018

it was to empty the variable , so no funny business happens with it.
the lPrev = 0 is not needed.

i copied it from another code batch...and didnt vet it totally.

Access N0vice · Mar 14, 2018

So Mark,

Would it look like this?
FirstRead: DLookUp("[MeterReading]","HourMeterTable","MeterID = " & [MeterReading] & " AND DateRead = " & [ReadingDate] From)

I am getting "The Expression you entered contains invalid syntax"

Mark_ · Mar 14, 2018

What fields do you have in your table? Also, are you trying to do this on a form or in a query?

Access N0vice · Mar 14, 2018

First Field "ReadingDate", Second Field "MeterReading" the Table is "HourMeterTable"

I am doing this in a query. I want to enter dates to search by.

Mark_ · Mar 14, 2018

Where are you trying to enter the dates? And how do you plan to use this data?

For myself, I would simply have this on a form and display when you need for each record.

Can you please give us the "Big picture" and let us know what you are trying to do? How do you want to display this, where do you want to display this, how do you want to define start and end dates, anything else relevant.

Access N0vice · Mar 14, 2018

Managers of the department want to check how teams are using the equipment(Over 300 Pieces) and have a Individual periodically checking the hours used, randomly, they want me to give them reports monthly to show them how much the equipment is being used. Also to bar chart the information to instill competition between teams on their equipment use. They may also come and have me research past months or years. I would expect to enter the dates when I run the query.
as far as the naming "FirstRead" "LastRead" or "StartDate" "EndDate" Would be fine.
Sorry I am dumb as a rock with this.

Mark_ · Mar 14, 2018

Access N0vice said:
Managers of the department want to check how teams are using the equipment(Over 300 Pieces) and have a Individual periodically checking the hours used, randomly, they want me to give them reports monthly to show them how much the equipment is being used. Also to bar chart the information to instill competition between teams on their equipment use. They may also come and have me research past months or years. I would expect to enter the dates when I run the query.
as far as the naming "FirstRead" "LastRead" or "StartDate" "EndDate" Would be fine.
Sorry I am dumb as a rock with this.

OK,
Most of the advice so far is not needed then. Please look at this link regarding summing in reports

This will probably be MUCH EASIER than any of the previous advice.

Access N0vice · Mar 15, 2018

ok,

I figured out a way to do it just in the report.
Thank you for your help.

Mark_ · Mar 15, 2018

For bar charts, I normally add label controls in the report. I use the data to determine their width.

You will want to look at the following three concepts:
Report Detail event - Format
Width property and Caption property for Labels
Twips (A twip is a unit of screen measurement. There are 1440 twips to an inch or 567 twips to a cm)

For myself, if I know there is a maximum value I will be using I set the size of a bar = Current Value / Max value (returns a fraction) multiplied by max width. I also can set the labels caption to hold what I need displayed.

If you need specific help in setting up bar charts on a report, do a search for bar chart report. If that doesn't answer your needs you can start a new thread.

Can you create a start and end date out of the same column of data (1 Viewer)

Access N0vice

Registered User.

Mark_

Longboard on the internet

Ranman256

Well-known member

Mark_

Longboard on the internet

Ranman256

Well-known member

Access N0vice

Registered User.

Mark_

Longboard on the internet

Access N0vice

Registered User.

Mark_

Longboard on the internet

Access N0vice

Registered User.

Mark_

Longboard on the internet

Access N0vice

Registered User.

Mark_

Longboard on the internet

Similar threads

Users who are viewing this thread