query help

[hispeaches]

I need some help from you the experts: I am having an issue with my access
query... I have a parameter asking for a start and end date (see below) when
I enter one date 2/14/2010 thru 2/14/2010 it shows the record but, if I enter
a range 02/14/2010 - 3/28/2010, I seem to get everything that starts with a 2
or 3.. HELP????

SELECT PatientInformation.MedicalRecordNumber, ([PatientLastName] & ", " &
[PatientFirstName]) AS [Patient Name], PatientInformation.PatientPhoneNumber,
PatInfo.Comments, PatInfo.DateOfVisit, PatInfo.DXDescription,
PatInfo.Physician, PatInfo.DueToFollowUp, FollowUp.FollowUpdays,
([DateOfVisit]+[FollowUpdays]) AS [Next F/U]
FROM PatientInformation INNER JOIN (PatInfo INNER JOIN FollowUp ON
PatInfo.DueToFollowUp = FollowUp.FollowUp) ON
PatientInformation.MedicalRecordNumber = PatInfo.MedicalRecordNumber
WHERE (((([DateOfVisit]+[FollowUpdays])) Between [start date] And [end date]))
ORDER BY ([DateOfVisit]+[FollowUpdays]);

 

[Jerry Whittle]

Is the DateOfVisit field a Date/Time data type or text? If text, it's working
as advertised. If text you could test the following:

CDate([DateOfVisit]+[FollowUpdays])

Also the first line of your SQL statement shoud be like below including the
semi-colon. It let's Access know that you are typing in a date and not text.

PARAMETERS [start date] DateTime, [end date] DateTime;

 

[hispeaches]

Yes, the DateOfVisit was a Date/Time field in the table. The first line of
the SQL statement is what fixed it!!! You are the best!!!! Thanks so much!!!!

Is the DateOfVisit field a Date/Time data type or text? If text, it's working
as advertised. If text you could test the following:

CDate([DateOfVisit]+[FollowUpdays])

Also the first line of your SQL statement shoud be like below including the
semi-colon. It let's Access know that you are typing in a date and not text.

PARAMETERS [start date] DateTime, [end date] DateTime;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I need some help from you the experts: I am having an issue with my access
query... I have a parameter asking for a start and end date (see below) when
I enter one date 2/14/2010 thru 2/14/2010 it shows the record but, if I enter
a range 02/14/2010 - 3/28/2010, I seem to get everything that starts with a 2
or 3.. HELP????

SELECT PatientInformation.MedicalRecordNumber, ([PatientLastName] & ", " &
[PatientFirstName]) AS [Patient Name], PatientInformation.PatientPhoneNumber,
PatInfo.Comments, PatInfo.DateOfVisit, PatInfo.DXDescription,
PatInfo.Physician, PatInfo.DueToFollowUp, FollowUp.FollowUpdays,
([DateOfVisit]+[FollowUpdays]) AS [Next F/U]
FROM PatientInformation INNER JOIN (PatInfo INNER JOIN FollowUp ON
PatInfo.DueToFollowUp = FollowUp.FollowUp) ON
PatientInformation.MedicalRecordNumber = PatInfo.MedicalRecordNumber
WHERE (((([DateOfVisit]+[FollowUpdays])) Between [start date] And [end date]))
ORDER BY ([DateOfVisit]+[FollowUpdays]);