Check Two Time Values

[JithuAccess]

Hello Guys,

I have two Fields in my Table Review Start Time and Review End Time and the Data type is Date/Time. These are the values in these Fields:

When a user enter a Time between 8:15 AM and 4:20 PM in the Form, I want to find the count of these Records. For Example if a user Enter 8:16 AM to 4:20 PM I want to show in a Text Box as 2, because there is already a Time 8:15 AM and 4:20 PM in the Table and now we have a new value 8:16 AM and 4:20 PM which comes Between 8:15 AM and 4:20 PM. So here, I need to check the Review Start Time and Review End Time in the Text Boxes in my Form with the Data already in the Table

Can I display this in a Text Box Using DCount Function?

Thanks

 

[Minty]

Yes , something like

DCount("*","YourTableName","ReviewStartTime > " & Timevalue([YourReviewStartTime]) & " AND " & Timevalue([YourReviewStartTime]) & "< ReviewEndTime ")

This might well have been easier with DateTime Fields as how do you know which date you are comparing?

 

[JithuAccess]

Yes , something like

DCount"*","YourTableName","ReviewStartTime > " & Timevalue([YourReviewStartTime]) & " AND " & Timevalue([YourReviewStartTime]) & "< ReviewEndTime ")

This might well have been easier with DateTime Fields as how do you know which date you are comparing?

Thanks a lot. I think I can add another criteria like Between [Forms]![My Form]![Start Date] and [Forms]![My Form]![End Date]

May be a very bad Logic.

Thanks

 

[JithuAccess]

Yes , something like

DCount("*","YourTableName","ReviewStartTime > " & Timevalue([YourReviewStartTime]) & " AND " & Timevalue([YourReviewStartTime]) & "< ReviewEndTime ")

This might well have been easier with DateTime Fields as how do you know which date you are comparing?

Hi,

This is my Code:

=DCount("*","[tblReviewed Hours]","[Review Start Time] > " & TimeValue([Forms]![frmReviewed Hours]![Review Start Time]) & " AND " & TimeValue([Forms]![frmReviewed Hours]![Review End Time]) & "< [Review End Time] ")

I am getting #Error

Thanks

 

[Gasman]

Put all the criteria into a string variable and debug.print that to see what you produce, when correct use that in the domain function.

 

[JithuAccess]

Put all the criteria into a string variable and debug.print that to see what you produce, when correct use that in the domain function.

Thanks a lot.

This is the Error Message I am getting when I put the Code in the Lost Focus Event of Review End Time

Text120.ControlSource = DCount("*", "[tblReviewed Hours]", "[Review Start Time] > " & TimeValue([Forms]![frmReviewed Hours]![Review Start Time]) & " AND " & TimeValue([Forms]![frmReviewed Hours]![Review End Time]) & "< [Review End Time] ")

 

[Gasman]

Need space before < and # surrounding times perhaps?

 

[JithuAccess]

Need space before < and # surrounding times perhaps?

Hi,

I changed code to

Text120.ControlSource = DCount("*", "[tblReviewed Hours]", "#[Review Start Time]# > " & TimeValue([Forms]![frmReviewed Hours]![Review Start Time]) & " AND " & TimeValue([Forms]![frmReviewed Hours]![Review End Time]) & " < #[Review End Time]# ")

But still getting Error

Thanks

 

[Gasman]

No!, # around the literal times, not the fields

 

[JithuAccess]

No!, # around the literal times, not the fields

Sorry I didn't get you. I have to put # around where?

 

[The_Doc_Man]

When you have a date/time FIELD in a record, Access knows it is a date and/or time. However, a string such as 1-Jan-2021 might be an expression involving a variable named Jan - unless you bracket the literal date constant with #, as #1-Jan-2021# - and date/time combinations are permitted as well: #03/15/2021 14:51# - which would be a valid date/time constant in the afternoon of the Ides of March.

 

[MajP]

That is a lot to bite off in one line. The control source looks bizarre to me. The dcount returns a number. You could have a control source like "=7" to show 7

dim startTime as string
dim EndTime as string
dim criteria as string
startTime = "#" & TimeValue([Forms]![frmReviewed Hours]![Review Start Time]) & "#"
endTime = "#" & TimeValue([Forms]![frmReviewed Hours]![Review End Time]) & "#"
criteria = "[Review Start Time] > " & startTime & "AND " & EndTime & " < [Review End Time]"
debug.print criteria
'The below makes no sense how can a control source be a number.  It would have to be "=N"
text120.controlsource = DCount("*", "[tblReviewed Hours]", criteria)

 

[JithuAccess]

When you have a date/time FIELD in a record, Access knows it is a date and/or time. However, a string such as 1-Jan-2021 might be an expression involving a variable named Jan - unless you bracket the literal date constant with #, as #1-Jan-2021# - and date/time combinations are permitted as well: #03/15/2021 14:51# - which would be a valid date/time constant in the afternoon of the Ides of March.

Hi,

I have changed Code

Text120.ControlSource = DCount("*", "[tblReviewed Hours]", "[Review Start Time] > #" & TimeValue([Forms]![frmReviewed Hours]![Review Start Time]) & "# AND #" & TimeValue([Forms]![frmReviewed Hours]![Review End Time]) & "# < [Review End Time]")

and this time I am getting #Name? Error in the Text Box.

Thanks

 

[MajP]

and this time I am getting #Name? Error in the Text Box

Wow you cannot debug this single line of complex code? Really? My clues did not help. Please provide you debug.print as I showed.

 

[JithuAccess]

That is a lot to bite off in one line. The control source looks bizarre to me. The dcount returns a number. You could have a control source like "=7" to show 7

dim startTime as string
dim EndTime as string
dim criteria as string
startTime = "#" & TimeValue([Forms]![frmReviewed Hours]![Review Start Time]) & "#"
endTime = "#" & TimeValue([Forms]![frmReviewed Hours]![Review End Time]) & "#"
criteria = "[Review Start Time] > " & startTime & "AND " & EndTime & " < [Review End Time]"
debug.print criteria
'The below makes no sense how can a control source be a number.  It would have to be "=N"
text120.controlsource = DCount("*", "[tblReviewed Hours]", criteria)

Thanks a lot for your Reply.

To find a sum based on criteria, I am using DCount Function. The criteria I am adding in the Control Source Property of a Text Box.

I wan this in an Event. So I put this Code in the Lost Focus Event of a Text Box Like:

Private Sub Review_End_Time_LostFocus()

Text120.ControlSource = DCount("*", "[tblReviewed Hours]", "[Review Start Time] > #" & TimeValue([Forms]![frmReviewed Hours]![Review Start Time]) & "# AND #" & TimeValue([Forms]![frmReviewed Hours]![Review End Time]) & "# < [Review End Time]")


End Sub

I think this should Return the Sum in to the Text Box120

Thanks

 

[JithuAccess]

Wow you cannot debug this single line of complex code? Really? My clues did not help. Please provide you debug.print as I showed.

Thanks. Your clues Helped me a lot Sir. But scared VBA Codes

 

[Gasman]

Text120=dcount(......

 

[JithuAccess]

Text120=dcount(......

Thanks a lot It worked Perfectly

 

[JithuAccess]

Wow you cannot debug this single line of complex code? Really? My clues did not help. Please provide you debug.print as I showed.

Sorry it is my Fault. As Gasman suggested I changed my Code Text120= instead of Text120.ControlSource, my apologies

 

[Gasman]

Start giving your controls meaningful names. A few months from now text120 will mean nothing to you.