query help

  • Thread starter Thread starter hispeaches
  • Start date Start date
H

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]);
 
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;
 
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!!!!

Jerry Whittle said:
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.


hispeaches said:
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]);
Click to expand...
Click to expand...
 
Back
Top