between and

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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200612/1

On Thu, 14 Dec 2006 17:26:53 GMT, ielmrani via AccessMonster.com
wrote:

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


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"

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"ielmrani via AccessMonster.com" u21259@uwe wrote in message
news:6ac1f3f540207@uwe...
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200612/1

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.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200612/1

thank you very much everyone

missinglinq wrote:
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.


--
Message posted via http://www.accessmonster.com