Date entered must be between Start and End Dates 1

[dbar10]

I have an unbound text box that the user will enter a date for service. That date must be between the AuthStart and AuthEnd dates. I have been playing with this for a while and just can't get it to work. What am I doing wrong?

Private Sub Date_BeforeUpdate(Cancel As Integer)
If Not (Me!Date >= Int(Me!AuthStart) And Me!Date <= Int(Me!AuthEnd)) Or IsNull(Me!Date) Then
MsgBox "This date is not authorized"
Cancel = True
End If

End Sub

Thanks for your help

 

[lameid]

Date is a function and returns the current date.

You might have better luck if you rename your control to txtDate OR you put square brackets [] around all the date's... Me![Date]

 

[dbar10]

Thanks lameid. I put the brackets like you said (Duh), but I'm still getting a Runtime error '13' - Type mismatch

Debugger is highlighting this line:

If Not (Me![Date] >= Int(Me!AuthStart) And Me![Date] <= Int(Me!AuthEnd)) Or IsNull(Me![Date]) Then

I have all the fields formatted for Short Date and they all look the same to me. Any other ideas?

 

[projecttoday]

I don't think you should use Date as a name ever. And doesn't Int return an integer? If you want the integer value of a date you would need that on both sides of the >= but why would you need that?

 

[dbar10]

OK I'm still working with this but almost nothing is working. I changed the code to:

Private Sub Date_BeforeUpdate(Cancel As Integer)
If Not Me![Date] >= Me![AuthStart] And Me![Date] <= Me![AuthEnd]) Or IsNull(Me![Date]) Then
MsgBox "This date is not authorized"
Cancel = True
End If

End Sub

It runs but it doesn't accept any date, always gives the MsgBox.

Then I went to use the Valdation Rule in the [Date] textbox.
I entered >=[AuthStart] And <=[AuthEnd]. Still would not take anything. Then I entered just the first part. >=[AuthStart], and it works like its supposed to but I need to validate that date does not go beyond the authorization period which in many cases is next year.

Can anybody make sense out of this? I am losing it here.

 

[projecttoday]

If (Not (Me![Date1] >= Me![AuthStart] And Me![Date1] <= Me![AuthEnd])) Or IsNull(Me![Date1]) Then

 

[dbar10]

projecttoday. I did everything just like you said and am still getting :MsgBox "This date is not authorized"
on any date I put in either right or wrong.

Thanks for your help but is it possible that it's something about the end date being next year? I'm just grasping at straws at this point.

 

[projecttoday]

Have you tried debugging it? What do see when you put a stop on that statement and pass the cursor over the fields?

 

[dbar10]

OK, I figured out what you wanted.
In debug I put a breakpooint on the staement:
If (Not (Me![Date1] >= Me![AuthStart] And Me![Date1] <= Me!AuthEnd)) Or IsNull(Me![Date1]) Then

Me![Date1]= 8/3/2009
Me![AuthStart]="4/7/2009"
Me!AuthEnd= "4/7/2010"

Looks like it is seeing mu auth start and auth end as text instead of date. How do I fix that?

 

[projecttoday]

Text has to compare character by character so change the format to Date/Time. Format for table fields is in the table definition. Format for textboxes is on the format tab of properties.

 

[dbar10]

Thanks but the two Auth bos are unbound and they are formatted for short date just like the Date1 field. All these boxes are unbound. That's why I am confused. Any other ideas?

 

[dbar10]

Found the problem. The input masks did not match the source table of the auth start and auth end. I was using setvalue to send these dates to the unbound boxes so I forgot to go all the way back. Thanks very much for your help.

 

[dbar10]

OOps, I spoke too soon. I am still getting the same formatting issue when I debug the code.

If (Not (Me![Date1] >= Me![AuthStart] And Me![Date1] <= Me!AuthEnd)) Or IsNull(Me![Date1]) Then

Me![Date1]= 8/3/2009
Me![AuthStart]="4/7/2009"
Me!AuthEnd= "4/7/2010"

Does anybody have any ideas?

 

[projecttoday]

If they're formatted as Date they should be Date. Try somethink like

Dim AuthStartDate as Date
AuthStartDate = Me![AuthStart}
If Me![Date1] >= AuthStartDate then ...

 

[dbar10]

Eureka!!! With a little more modification and tweaking the statement it works perfect.

Dim AuthStartDate As Date
Dim AuthEndDate As Date
AuthStartDate = Me![AuthStart]
AuthEndDate = Me![AuthEnd]
If (Me![Date1] < AuthStartDate Or Me!Date1 > AuthEndDate) Or IsNull(Me.Date1) Then
MsgBox "This date is not authorized"
Cancel = True
End If

Thank you for your patience and hangin in there with me, I really appreciate it. Have a great day

 

[projecttoday]

Wonderful.