Solved: Compare string against two fields

[Marcster]

Hi People,

Having trouble trying to do something which is probably easy for someone, been a long time since I developed in Access.

I have a table:

Name: Table1
Fields: DateFrom, DateTo
Only 1 record in table.

Using VBA I want to check that a date is or between these two dates.

Sub CheckDate(strDate)

If strDate is or between DateFrom and DateTo then OK, if not display error message and exit sub.

End Sub

Example, if Table1.DateFrom holds 01-Jan-2012 and Table1.DateTo holds 31-Jan-2012
and strDate has a value of 01-Jan-2012, then display message to say strDate OK.
If strDate is 02-Feb-2012 then diplay an error message.


Any one know a way to do this?.... :banghead: .

Thanks.

[MacroShadow]

Pretty simple...

Public Function fBetweenDates(DateEntered As Date) As Boolean

Dim dStart As Date
Dim dEnd As Date
On Error Resume Next
dStart = #5/12/1995#
dEnd = #5/12/2012#

If DateEntered >= dStart And DateEntered <= dEnd Then
fBetweenDates = True
Else
fBetweenDates = False
End If
End Function

Sub Test()
If fBetweenDates(Date) = True Then
MsgBox "True"
Else
MsgBox "False"
Exit Sub
End If
End Sub


p.s.
Don't forget to set the dStart and dEnd variables.

[Marcster]

Thanks MacroShadow.

If the dStart and dEnd variables are stored in a Table, how do I retrieve these values so I can use them in the function?.

[Marcster]

Ah ha, =DLOOKUP()