between and

  • Thread starter ielmrani via AccessMonster.com
  • Start date
I

ielmrani via AccessMonster.com

Can anyone tell me why this code is not working:

Private Sub Form_Open(Cancel As Integer)
If Me.[Inception_Date] between #1/1/2006# And #12/31/2006# Then
txtCode = "6I"
Else
If Me.[Inception_Date] between #1/1/2007# And #12/31/2007# Then
txtCode = "7I"
End If
End If

End Sub

It does like the word Between.

Thanks in advance
 
F

fredg

Can anyone tell me why this code is not working:

Private Sub Form_Open(Cancel As Integer)
If Me.[Inception_Date] between #1/1/2006# And #12/31/2006# Then
txtCode = "6I"
Else
If Me.[Inception_Date] between #1/1/2007# And #12/31/2007# Then
txtCode = "7I"
End If
End If

End Sub

It does like the word Between.

Thanks in advance
Click to expand...


If Me.[Inception_Date] >= #1/1/2006# And Me![Inception_Date] <=
#12/31/2006# Then
txtCode = "6I"
Else
If Me.[Inception_Date] >= #1/1/2007# And Me![Inception_Date] <=
#12/31/2007# Then
txtCode = "7I"
 
D

Douglas J. Steele

I don't believe you can use Between in VBA.

Try:

Private Sub Form_Open(Cancel As Integer)

If Me.[Inception_Date] >= #1/1/2006# And _
Me.[Inception_Date] <= #12/31/2006# Then
txtCode = "6I"
Else
If Me.[Inception_Date] >= #1/1/2007# And _
Me.[Inception_Date] <= #12/31/2007# Then
txtCode = "7I"
End If
End If

End Sub

Alternatively, you could use

Private Sub Form_Open(Cancel As Integer)

If Year(Me.[Inception_Date]) = 2006 Then
txtCode = "6I"
Else
If Year(Me.[Inception_Date]) = 2007 Then
txtCode = "7I"
End If
End If

End Sub
 
M

missinglinq via AccessMonster.com

BETWEEN is not valid VBA code, nor is the pound signs surrounding the dates;
they're only used in SQL statements. You need to use a structure similar to
this.

If (Me.[Inception_Date] =>"1/1/2006") And (Me.[Inception_Date] =<
"12/31/2006") Then
txtCode.Value = "6I"
ElseIf (Me.[Inception_Date] =>"1/1/2007") And (Me.[Inception_Date] =<
"12/31/2007") Then
txtCode.Value = "7I"
End If

This structure is sound, but I'm a little fuzzy on comparing dates (Me.
[Inception_Date] =>"1/1/2006"); I don't have access to Access right now, so I
can't testdrive this, but I think this is correct, and it's certainly a
pointer in the right direction.
 
I

ielmrani via AccessMonster.com

thank you very much everyone
BETWEEN is not valid VBA code, nor is the pound signs surrounding the dates;
they're only used in SQL statements. You need to use a structure similar to
this.

If (Me.[Inception_Date] =>"1/1/2006") And (Me.[Inception_Date] =<
"12/31/2006") Then
txtCode.Value = "6I"
ElseIf (Me.[Inception_Date] =>"1/1/2007") And (Me.[Inception_Date] =<
"12/31/2007") Then
txtCode.Value = "7I"
End If

This structure is sound, but I'm a little fuzzy on comparing dates (Me.
[Inception_Date] =>"1/1/2006"); I don't have access to Access right now, so I
can't testdrive this, but I think this is correct, and it's certainly a
pointer in the right direction.
Click to expand...
 
Top