Date In between two others

[teel73]

I am trying to check if the current date is between 2 other dates and I can't figure out a way to write an expression. For example I want to know if today's date (7/20/2010) is in between 7/19/2010 and 7/26/2010 ... Here's what I have below:

I have 4 variables and a sql statement that I pull the field [BeginWeek] which is a date field.: myStart and myEnd

Dim curDate as Date
Dim rst As Recordset
Dim db As Database
Dim sql As String
Dim myEnd, myStart As Date
 
sql = "SELECT tblOnCallTable.[B]BeginWeek[/B], tblOnCallTable.Personnel, tblOnCallTable.[Home#], tblOnCallTable.[Cell#] " _
    & "FROM tblOnCallTable;"
    
Set db = CurrentDb
Set rst = db.OpenRecordset(sql)

rst.movefirst
 
myStart = Format(rst![BeginWeek], "mm/dd/yyyy") & " 17:00"9/19/2010 5:00:00 PM 

'I add 7 days to myStart
myEnd = DateAdd("d", 7, Format(rst![BeginWeekDay], "mm/dd/yyyy")) & " 07:00"
 
curDate = Format(Now, "mm/dd/yyyy hh:nn")

myStart = Format(rst!BeginWeekDay, "mm/dd/yyyy") & " 17:00"
 
 If curDate >= myStart And curDate <= myEnd Then
                onCallPerson = rst!personnel & " - Cell: " & rst![Cell#] & "  Home: " & rst![Home#]
      
    Else
        
    End If
 
debug.print onCallPerson

 

[ghudson]

What do you get when you verify your dates? Are they valid?

MsgBox "curDate = " & curDate
MsgBox "myStart = " & myStart

 

[teel73]

Yes.

curDate returns 07/02/2010 10:18
myStart returns 07/19/2010 17:00

 

[Brianwarnock]

I think that your problem is that Format returns a string, use CDate to convert the strings to an actual date.

Brian

 

[ghudson]

Oops, I forgot one. And your myEnd is valid?

MsgBox "curDate = " & curDate
MsgBox "myStart = " & myStart
MsgBox "myEnd = " & myEnd

 

[teel73]

OMG .. It works. I had the wrong date entered into the table. I'm sorry for that. Thanks again though.

 

[ghudson]

I have done worse.