Problem with DateAdd and date range input parameters

I have created a query where I use the DateAdd function to calculate a date. I have named this calculated field NextVisitDue. The problem is that I'd like to be able to put an input parameter on NextVisitDue so that I can retrieve records within a date range. For some reason, when I do this, the resulting NextVisitDue dates include dates that are outside of the date range. For instance, if [Begin date:] = 1/1/2005 and [End date:] = 6/1/2005, I will get resulting dates in NextVisitDue like 1/12/2006, 10/21/2006, and 10/6/2007. These results are clearly outside of the date range. What follows is the SQL statement...

SELECT tblSpecialist.User, tblSpecialist.FirstName & " " & tblSpecialist.LastName AS Specialist, tblProvider.ManagerOwnerFN & " " & tblProvider.ManagerOwnerLN AS Provider, tblProvider.FacilityName, tblProvider.LastSupervisoryVisit, DateAdd("m",[tblFrequencyCodes.Frequency],[tblProvider.LastSupervisoryVisit]) AS NextVisitDue, tblProvider.VisitFrequencyCode, tblFrequencyCodes.Frequency
FROM tblSpecialist INNER JOIN (tblCaseLoad INNER JOIN (tblArea INNER JOIN (tblProvider INNER JOIN tblFrequencyCodes ON tblProvider.VisitFrequencyCode = tblFrequencyCodes.FrequencyCode) ON (tblArea.AreaCounty = tblProvider.AreaCounty) AND (tblArea.AreaFacilityType = tblProvider.AreaFacilityType) AND (tblArea.AreaZipCode = tblProvider.AreaZipCode)) ON tblCaseLoad.CaseLoadID = tblArea.CaseLoadID) ON tblSpecialist.SpecialistID = tblCaseLoad.SpecialistID
WHERE (((DateAdd("m",[tblFrequencyCodes.Frequency],[tblProvider.LastSupervisoryVisit])) Between [Begin date:] And [End date:]))
ORDER BY DateAdd("m",[tblFrequencyCodes.Frequency],[tblProvider.LastSupervisoryVisit]);

The real interesting thing is that, if I use literal values for the date range criteria, the query results are as expected! For instance, the following works beautifully (but doesn't allow for user input parameters as required)...

SELECT tblSpecialist.User, tblSpecialist.FirstName & " " & tblSpecialist.LastName AS Specialist, tblProvider.ManagerOwnerFN & " " & tblProvider.ManagerOwnerLN AS Provider, tblProvider.FacilityName, tblProvider.LastSupervisoryVisit, DateAdd("m",[tblFrequencyCodes.Frequency],[tblProvider.LastSupervisoryVisit]) AS NextVisitDue, tblProvider.VisitFrequencyCode, tblFrequencyCodes.Frequency
FROM tblSpecialist INNER JOIN (tblCaseLoad INNER JOIN (tblArea INNER JOIN (tblProvider INNER JOIN tblFrequencyCodes ON tblProvider.VisitFrequencyCode = tblFrequencyCodes.FrequencyCode) ON (tblArea.AreaCounty = tblProvider.AreaCounty) AND (tblArea.AreaFacilityType = tblProvider.AreaFacilityType) AND (tblArea.AreaZipCode = tblProvider.AreaZipCode)) ON tblCaseLoad.CaseLoadID = tblArea.CaseLoadID) ON tblSpecialist.SpecialistID = tblCaseLoad.SpecialistID
WHERE (((DateAdd("m",[tblFrequencyCodes.Frequency],[tblProvider.LastSupervisoryVisit])) Between #1/1/2005# And #6/1/2005#))
ORDER BY DateAdd("m",[tblFrequencyCodes.Frequency],[tblProvider.LastSupervisoryVisit]);

Please help!

-CoddFish

CoddFish,

Just looking at it quickly:

Between [Begin date:] And [End date:]

You might try hard-coding the "#"

Between "#" & [Begin date:] & "#" And "#" & [End date:] & "#"

I assume that it works if they key in something like #1/1/2005#

Wayne

Any other suggestions?

Wayne,

Thanks for the reply. I tried your suggestion (I agree that it seems like a problem with data type), but it didn't work. The following query (using [Begin date:] = 1/1/2005 and [End date:] = 6/1/2005) results in zero rows of data...

SELECT tblSpecialist.User, tblSpecialist.FirstName & " " & tblSpecialist.LastName AS Specialist, tblProvider.ManagerOwnerFN & " " & tblProvider.ManagerOwnerLN AS Provider, tblProvider.FacilityName, tblProvider.LastSupervisoryVisit, DateAdd("m",[tblFrequencyCodes.Frequency],[tblProvider.LastSupervisoryVisit]) AS NextVisitDue, tblProvider.VisitFrequencyCode, tblFrequencyCodes.Frequency
FROM tblSpecialist INNER JOIN (tblCaseLoad INNER JOIN (tblArea INNER JOIN (tblProvider INNER JOIN tblFrequencyCodes ON tblProvider.VisitFrequencyCode = tblFrequencyCodes.FrequencyCode) ON (tblArea.AreaZipCode = tblProvider.AreaZipCode) AND (tblArea.AreaFacilityType = tblProvider.AreaFacilityType) AND (tblArea.AreaCounty = tblProvider.AreaCounty)) ON tblCaseLoad.CaseLoadID = tblArea.CaseLoadID) ON tblSpecialist.SpecialistID = tblCaseLoad.SpecialistID
WHERE (((DateAdd("m",[tblFrequencyCodes.Frequency],[tblProvider.LastSupervisoryVisit])) Between "#" & [Begin date:] & "#" And "#" & [End date:] & "#"))
ORDER BY DateAdd("m",[tblFrequencyCodes.Frequency],[tblProvider.LastSupervisoryVisit]);

You can declare the data type of the parameters in the query:-

PARAMETERS [Begin date:] DateTime, [End date:] DateTime;
SELECT tblSpecialist.User, tblSpecialist.FirstName & " " & tblSpecialist.LastName AS Specialist, tblProvider.ManagerOwnerFN & " " & tblProvider.ManagerOwnerLN AS Provider,
................................................
................................................


Or use the CDate() or DateValue() function on the parameters e.g.

Between CDate([Begin date:]) And CDate([End date:])

.

It works!

Jon,

Thanks! Somebody gave me the exact same suggestions in another forum. Both suggestions work! Thanks for the reply -- this was driving me CRAZY

-CoddFish