If.... Then procedure not working.

[pmullan]

Hello Everyone, I need some help with the following code. The "IF ... Then" statement does not work. I set the time in the myTime variable to 8:15 PM and when I step through the code, it just skips over this section straight to the End IF line. I believe the code is correct. Please advise. Thanks Patrick

If myTime >= #6:00:00 PM# And myTime <= #5:59:00 AM# Then
rst.MoveFirst
Do Until rst.EOF
If rst![ShiftDate] = myDate And (rst![ShiftTime] >= #6:00:00 PM# _
And rst![ShiftTime] <= #5:59:00 AM#) And rst![Shift] = "Nightshift" Then
Me.txtShiftDay.Value = rst![ShiftDay]
Me.txtShiftDate.Value = rst![ShiftDate]
Me.txtShiftTime.Value = rst![ShiftTime]
Me.cboShift.Value = rst![Shift]
Me.cboTechnicianII.Value = rst![TechIIEmp]
Me.cboTechnicianI.Value = rst![TechIEmp]
Me.cmdClose.SetFocus
Exit Do
End If
If rst![ShiftDate] = myDate And rst![Shift] = "Dayshift" Then Exit Do
rst.MoveNext
Loop
End If

 

[missinglinq]

When you only assign the Time component of a DateTime field, Access uses 12/30/1899 as the Date component, so when you use

If myTime >= #6:00:00 PM# And myTime <= #5:59:00 AM# Then


the Access gnomes are seeing

If myTime >= #12/30/1899 6:00:00 PM# And myTime <= #12/30/1899 5:59:00 AM# Then

with your

8:15 PM

being seen as

12/30/1899 8:15 PM

and 8:15 PM can't be After 6:00:00 PM And Before 5:59:00 AM...on the same day...hence the failure of your code.

Linq ;0)>

 

[isladogs]

Beautifully explained linq

 

[arnelgp]

create an arbitrary value for the Date, eg: 1/1/2017
then use this in your code:

const TEMP_DATE As Date = #1/1/2017#
const START_TIME As Date = #6:00:00 PM#
const END_TIME As Date = #5:59:00 AM#

IF TEMP_DATE + TimeValue([myTime]) >= TEMP_DATE + START_TIME And _
	TEMP_DATE + TimeValue([myTime]) <= (TEMP_DATE + 1) + END_TIME Then

	rst.MoveFirst

	DO Until rs.EOF
		IF DateValue(rst![ShiftDate]) + TimeValue(rst![ShiftTime])  >= _
			DateValue([myDate]) + START_TIME AND _
			DateValue(rst![ShiftDate]) + TimeValue(rst![ShiftTime]) <= _
			(DateValue([myDate]) + 1) + END_TIME AND _
			rst![Shift] = "NightShift" Then
		
			Me![txtShiftDay].Value = rst![ShiftDay]
			Me![txtShiftDate].Value = rst![ShiftDate]
			Me![txtShiftTime].Value = rst![ShiftTime]
			Me![cboShift].Value = rst![Shift]
			Me![cboTechnicianII].Value = rst![TechIIEmp]
			Me![cboTechnicianI].Value = rst! [TechIEmp]

			Me.cmdClose.SetFocus
			Exit Do
		End If
		rst.MoveNext
	Loop
End If

 

[pmullan]

Greetings Everyone, I want to thank everyone for their input. I do have a question though. In my code when I debug it (I set up Watches for the variables "myDate & myTime". They do show only the date (9/27/2017) or (10:15:00 AM). When Missinglinq mentioned what the Access gnomes do, wouldn't the debug watches show that?

Arnelgp, regarding your post, Can I assume that the "+" sign is meant to concatenate ? Also, for the TEMP_DATE variable, can I use the current date or does it need to be like what you posted?

Again, thanks everyone. I am relatively new to Access and I am using Access 2013 for this so I hope the code will work properly. Patrick

 

[arnelgp]

The + there is addition. You can use any date

 

[pmullan]

Thank you for you help! It is much appreciated.

 

[missinglinq]

Also, FYI, in Access VBA the operator for concatenation is the Ampersand (&)...not the Plus sign. While using the Plus sign for this can, sometimes work, it often leads to unexpected results!

Linq ;0)>

 

[isladogs]

Hi Linq

I've noticed an increasing use of + rather than & recently.

I've never used + as I also understand it causes errors but it would be useful to find a definitive source for what & why

OR ... could this just start another dot vs bang debate...?

Anyone else?

 

[The_Doc_Man]

The issue with + vs. & in an expression gets interesting.

In theory, + is only a numeric operator but I don't know what happens in the specific case that you use + on two numeric strings (i.e. IsNumeric(x) = True).

If you use + (or *) between a numeric and a string, you get a type mismatch. And as it happens for most keyboards, the use of * when you meant & is very easy, since the two are next to each other.

The & operator, when used between two numerics, SHOULD fail with a type mismatch. It should work for two strings and perform end-to-end concatenation in the process.

I think the mixed case with & will just complain with type mismatch.

The problem that complicates this is that VBA will allow implied up-typing when you have an expression with components that are different in some way. For instance, if you have a BYTE and a LONG and neither of them represent negative numbers, you can still add the two of them. The BYTE variable implicitly up-converts to LONG for the duration of the expression execution. I don't know if an integer can be converted to a numeric string. If it could, then concatenation would work. But I'm not holding my breath on that one.

 

[Galaxiom]

Concatenation with the + introduces Null propagation. If either side is Null then the result is Null.

"A" & Null = "A"

"A" + Null = Null

It can be useful for optionally concatenating a delimiter or space depending on whether a field contains a value.

 

[Cronk]

Doc, I would have agreed with you but having tried it....

Dim i As Integer, k As Integer
   Dim s1 As String, s2 As String
   
   i = 89:   k = 56
   Debug.Print 1, i & k
   
   s1 = "89": s2 = "56"
   Debug.Print 2, s1 * s2, 89 * 56
   
   Debug.Print 3, s1 + i, 89 + 89
   Debug.Print 4, s1 * i, 89 * 89

gives

1 8956
2 4984 4984
3 178 178
4 7921 7921

 

[isladogs]

What about the use of '+' rather than '&' as used earlier in this thread?

I've not tested it, but assume it must work as its written by one of our most frequent contributors.
However, will it lead to problems elsewhere?

 

[Galaxiom]

I've not tested it, but assume it must work as its written by one of our most frequent contributors.

Frequent contribution is no guarantee of veracity. We all harbour our particular misconceptions that come about from the vagaries of our experience and those we picked up from sources we had assumed were infallible.

However, arnelgp is adding date and time values which are numeric and hence always treated arithmetically by the + operator.

The only time I have ever seen using arithmetical operations on Time go wrong is where Access was linked to SQL Server. I posted about it some years ago but unable to find it at the moment.