Populating Textbox with Range of Time

[athyeh]

Hello,

Is there a way to have Access show a range of time given that it is XX:XX AM/PM?

For instance:

It is 7:30 AM
A textbox displays: "7:00 AM - 8:00 AM"?

[June7]

Maybe with an expression.

x represents the time given as a:


1. string value

Val(x) & ":00 " & Right(x,2) & " - " & Val(x) + 1 & ":00 " & Right(x,2)

Complication arises if the range crosses noon or midnight.


2. date/type value

Val(x) & ":00 " & Right(x,2) & " - " & Val(DateAdd("h",1,x)) & ":00 " & Right(DateAdd("h",1,x),2)

[athyeh]

=IIf([Text70]=Format("mm/dd/yyyy hh:mm:ss AM/PM") >= 9:00:00 AM AND Format("mm/dd/yyyy hh:mm:ss AM/PM") < 10:00:00 AM,"9:00 AM - 10:00 AM")

In its current state this does not work, but what about something like this input into a textbox? Where Text70 is a textbox that is populated by =Now()

[June7]

The expression is nonsense.

Format results in a string value, not a date. The Format function does not have a value to format and the comparison value is incomplete and needs delimiters. Don't need the date part.

=IIf(Format([date field or textbox name], "hh:mm:ss AM/PM") >= "09:00:00 AM" And Format([date field name], "hh:mm:ss AM/PM") < "10:00:00 AM", "9:00 AM - 10:00 AM", something else)

Yes, Text70 could be populated with Now().

Don't understand how this gets you what you need. Why a static fixed range?

[athyeh]

I am trying to find a way to reset a DCount back to zero after every hour.

A few weeks ago, I thougt this was the ticket, but now I can't remember and I can't find my notes anywhere.

[June7]

Don't understand. DCount is a domain aggregate function, can't 'reset' DCount. You can reset a variable.

Perhaps if you provided a full explantion of what you are trying to do, what business process are you trying to code, and the code you have attempted, might be able to offer solution.

[athyeh]

I am tracking production by having processors scan in barcodes through a form.

On my form I have a DCount function:

Text64 =DCount("LID","tblMainDB","Operator = '" & [Forms]![tblMainDB]![Operator] & "'")

That is acting as a Visual for the Processor to show him/her how many barcodes he/she has scanned. I want that counter to reset to 0 every hour, reflecting that every hour begins a new period of production.

Any assistance is greatly appreciated as this is one of the last things that needs to occur before we implement.

[June7]

Sounds tricky. Anything involving timing is. DCount won't help unless you have a date/time component in the WHERE argument.

Create textboxes on form for the hour and count. Possible code in the scan box AfterUpdate event:

If Hour(Now()) > Nz(Me.tbxHour,-1) Then
Me.tbxCount = 0
Me.tbxHour = Hour(Now())
End If
Me.tbxCount = Me.tbxCount + 1

[athyeh]

So a variation of that "worked" but now I see what the problem is and I think you were trying to point me to it the whole time. We can 'reset' the DCount Function to 0 all we want. As soon as we requery the form, DCount is going to continue to count the exact same records as before.

Now after a lot of research I am trying to point the Criteria portion of the DCount function towards [Time Added] an Auto-Fill field in my table.


Text88 = DCount("LID", "tblMainDB", "[Operator] = '" & [Forms]![tblMainDB]![Operator] & "" + [Time Added] < #Hour(Now())# & <#Hour(Now())-1

Now I know that's not going to work but is there a way that we could make it work?

[June7]

The WHERE expression makes no sense.

Don't understand [Time Added]. Why would you add it to Operator?

If you want multiple conditions, must use AND and OR operators.

[athyeh]

I point to the Operator because I want the counter to show his/her production only. The [Time Added] part of the statement is to point it at hourly intervals (I want the counter to show production for this hour).

Example:

It is 12:30 PM
The Counter is counting up each barcode Suzie has scanned in since 12:00 PM. At 1:00 PM, the Counter begins counting up each barcode Suzie has scanned in since 1:00 PM.

Previously, this statement:
Text64 =DCount("LID","tblMainDB","Operator = '" & [Forms]![tblMainDB]![Operator] & "'")

Worked but only to the point that it counted up every record the Operator had ever scanned in. In my ignorance I thought that "resetting"/setting/whatever the counter to zero would solve the problem. Only now do I understand that I can set the field's value to 0 all I want, it's still going to count the thing it is designed to count.

Now I am looking for a way to DCount("LID","tblMainDB","Operator = '" & [Forms]![tblMainDB]![Operator] & "'" And "[Time Added] = '" [This current hour of production]).

I have a very base understanding of VBA code and I know that the above statement is incorrect and probably makes no sense, but hopefully was able to articulate the need?

[JrMontgom]

If I have the defined the problem -You want to count in a 1 hr increments the number of barcodes processed. If this is correct what about keeping track in a 2 dimensioned array the time start and the current count incrementing the count as well as checking the array for the time and if the current time is +1 hr then save that time (called time1 ) in a global Tempvars("StartTime") = time1 + 1 and reset the array to new time and new count. Display the data in a combo box by adding the data as a string Tempvars("StartTime") & ":" & array(0,1) or if you just want "1 : 23" indicating first hour 223 barcodes. Rest the Tempvars("StartTime"). Hope this helps

[June7]

The suggestion given in post 8 does not serve?

[athyeh]

Thank you June7. This thread is solved.