Using Today's Date to find Current, Former members etc

[wild007]

Hello,

I'm using an Access .adp against SQL Server 2000.

I have a form in which I am showing a roster of committee members in a listbox(lstCommitteeRoster) when another listbox is clicked on. I also want to have an option group (fraRosterMemberStatus) that will filter out Current(1), Former(2), Future(3) and All(4) members of a particular committee. I have done this a million times in Access using Today's date as a variable. For some reason, I can't get the VBA code right in order to populate this form. I was wondering if anyone could see if I have a Syntax problem or something. The CommitteeStartDate and CommitteeEndDate fields in the table are a DateTime datatype. I have this code running when the option group is updated or the committee list is cliced on:

With vForm.lstCommitteeRoster
strSQL = "Select CommitteeMemberID, tblCommitteeMembers.CommitteeID, tblCommitteeMembers.IndividualID, (IndInformalName + ' ' + IndLastName) as [Member], CommitteePositionCode as [Position], " & _
"CommitteeStartDate as [Start], CommitteeEndDate as [End] from tblCommitteeMembers INNER JOIN tblIndividuals ON tblCommitteeMembers.IndividualID = tblIndividuals.IndividualID Where CommitteeID = " & intCommitteeID
If vForm.fraRosterMemberStatus = 1 Then 'Show Current Members
.RowSource = strSQL & &quot; AND CommitteeStartDate <= &quot; & Date & &quot; AND CommitteeEndDate >= &quot; & Date + 365
End If
If vForm.fraRosterMemberStatus = 2 Then ' Show Former Members
.RowSource = strSQL & &quot; AND CommitteeStartDate <= &quot; & Date & &quot; AND CommiteeEndDate < &quot; & Date
End If
.ColumnCount = 7
.BoundColumn = 1
.ColumnHeads = True
.ColumnWidths = &quot;0,0,0,1.5 in,.5 in, .65 in, .65 in&quot;
.Requery
End With


even when I modified the code to just show CommitteeStartDate = Date it didn't find any matches.

Oddly, when I enter CommitteeDate > date, then it shows a match. Both should be today's date since I just entered in the record. Is it factoring in the time somewhere and not showing me at the table level?

Any and all help is greatly appreciated!

 

[wild007]

By the way, ignore the Date + 365 in the code. That was an error and I removed it.

 

[wild007]

Nailed it. Here's what I did:

If vForm.fraRosterMemberStatus = 1 Then 'Show Current Committee Members
.RowSource = strSQL & &quot; AND CommitteeStartDate <= GetDate() AND CommitteeEndDate >= GetDate()&quot;
End If
If vForm.fraRosterMemberStatus = 2 Then ' Show Former Committee Members
.RowSource = strSQL & &quot; AND CommitteeStartDate < GetDate() AND CommitteeEndDate < GetDate()&quot;
End If
If vForm.fraRosterMemberStatus = 3 Then ' Show Future Committee Members
.RowSource = strSQL & &quot; AND CommitteeStartDate > GetDate() AND CommitteeEndDate > GetDate()&quot;
End If
If vForm.fraRosterMemberStatus = 4 Then ' Show All Committee Members
.RowSource = strSQL
End If

 

[wild007]

Thanks for your help Wild007!

 

[wild007]

Your welcome.