ACCESS FEED: Re: [MS_AccessPros] sorting off prior date conversion-fixed

thank you I will check it out, probably too much copy and pasting information in
Patty

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Great!

BTW, did you notice you have the filter twice on DiagnosisCode in your query? Once in the WHERE clause and repeated in the HAVING clause. The one in the WHERE clause should do it.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Feb 5, 2015, at 4:05 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Figured out how to do the date sort, it required adding a new field from another table (main table), not converting it and using it to do the sort. that way I still get the date of birth showing in reports correctly and can sort for date ranges.
appreciate your help just wanted to let you know all set on this one
Patty

---In MS_Access_Professionals@yahoogroups.com, <pattykf@...> wrote :

I fixed the diagnosis with this change:

SELECT Person_filter_New.person_nbr, Person_filter_New.last_name, Person_filter_New.first_name, CDate(Left([dbo_person].[date_of_birth],4) & "-" & Mid([dbo_person].[date_of_birth],5,2) & "-" & Right([dbo_person].[date_of_birth],2)) AS DOB, dbo_problem_list_data_.ProblemDescription, dbo_problem_list_data_.DiagnosisCode, dbo_problem_list_data_.create_timestamp, dbo_problem_list_data_.Resolved, Person_filter_New.expired_ind, Person_filter_New.description, dbo_problem_list_data_.DeletedReason, Person_filter_New.LastOfexclude_from_reporting, Count(Person_filter_New.person_nbr) AS CountOfperson_nbr
FROM dbo_person INNER JOIN (dbo_problem_list_data_ INNER JOIN Person_filter_New ON dbo_problem_list_data_.person_id = Person_filter_New.person_id) ON dbo_person.person_id = Person_filter_New.person_id
WHERE (((CDate(Left([dbo_person].[date_of_birth],4) & "-" & Mid([dbo_person].[date_of_birth],5,2) & "-" & Right([dbo_person].[date_of_birth],2)))>#1/1/1929#) AND ((dbo_problem_list_data_.ProblemDescription) Is Not Null) AND ((dbo_problem_list_data_.DiagnosisCode) Between "401.0" And "402.0") AND ((dbo_problem_list_data_.Resolved) Is Null) AND ((Person_filter_New.expired_ind)="N") AND ((Person_filter_New.description) Is Null) AND ((Person_filter_New.LastOfexclude_from_reporting)<1)) OR (((dbo_problem_list_data_.Resolved)=0))
GROUP BY Person_filter_New.person_nbr, Person_filter_New.last_name, Person_filter_New.first_name, CDate(Left([dbo_person].[date_of_birth],4) & "-" & Mid([dbo_person].[date_of_birth],5,2) & "-" & Right([dbo_person].[date_of_birth],2)), dbo_problem_list_data_.ProblemDescription, dbo_problem_list_data_.DiagnosisCode, dbo_problem_list_data_.create_timestamp, dbo_problem_list_data_.Resolved, Person_filter_New.expired_ind, Person_filter_New.description, dbo_problem_list_data_.DeletedReason, Person_filter_New.LastOfexclude_from_reporting
HAVING (((dbo_problem_list_data_.DiagnosisCode) Between "401.0" And "402.0"))
ORDER BY dbo_problem_list_data_.create_timestamp DESC , dbo_problem_list_data_.DeletedReason;

---In MS_Access_Professionals@yahoogroups.com, <pattykf@...> wrote :

it is running now but it is not filtering for the age or the diagnosis. it is returning patients of all ages and diagnosises

---In MS_Access_Professionals@yahoogroups.com, <pattykf@...> wrote :

Date of birth fields are full it is a required field
ProblemDescription is text
Lastof exclude is a number not text

the query is returning a data type mismatch in criteria, tried to see if it was maybe the last field issue but changing that criteria did not help, Ideas?
thank you

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Patty-

First question: Are any of the date_of_birth fields blank?

Second question: Is the ProblemDescription field text?

Third question: Is the LastOfexclude_from_reporting field a numeric field? (Not text?)

Your query would be better stated like this:

SELECT Person_filter_New.person_nbr, Person_filter_New.last_name, Person_filter_New.first_name, CDate(Left([dbo_person].[date_of_birth],4) & "-" & Mid([dbo_person].[date_of_birth],5,2) & "-" & Right([dbo_person].[date_of_birth],2)) AS DOB, dbo_problem_list_data_.ProblemDescription, dbo_problem_list_data_.DiagnosisCode, dbo_problem_list_data_.create_timestamp, dbo_problem_list_data_.Resolved, Person_filter_New.expired_ind, Person_filter_New.description, dbo_problem_list_data_.DeletedReason, Person_filter_New.LastOfexclude_from_reporting, Count(Person_filter_New.person_nbr) AS CountOfperson_nbr
FROM dbo_person INNER JOIN (dbo_problem_list_data_ INNER JOIN Person_filter_New ON dbo_problem_list_data_.person_id = Person_filter_New.person_id) ON dbo_person.person_id = Person_filter_New.person_id
WHERE (((CDate(Left([dbo_person].[date_of_birth],4) & "-" & Mid([dbo_person].[date_of_birth],5,2) & "-" & Right([dbo_person].[date_of_birth],2)))>#1/1/1929#) AND ((dbo_problem_list_data_.ProblemDescription)>"0") AND ((dbo_problem_list_data_.DiagnosisCode) Like "*401.*") AND ((dbo_problem_list_data_.Resolved) Is Null Or (dbo_problem_list_data_.Resolved)=0) AND ((Person_filter_New.expired_ind)="N") AND ((Person_filter_New.description) Is Null) AND ((Person_filter_New.LastOfexclude_from_reporting)<1))
GROUP BY Person_filter_New.person_nbr, Person_filter_New.last_name, Person_filter_New.first_name, CDate(Left([dbo_person].[date_of_birth],4) & "-" & Mid([dbo_person].[date_of_birth],5,2) & "-" & Right([dbo_person].[date_of_birth],2)), dbo_problem_list_data_.ProblemDescription, dbo_problem_list_data_.DiagnosisCode, dbo_problem_list_data_.create_timestamp, dbo_problem_list_data_.Resolved, Person_filter_New.expired_ind, Person_filter_New.description, dbo_problem_list_data_.DeletedReason, Person_filter_New.LastOfexclude_from_reporting
ORDER BY dbo_problem_list_data_.create_timestamp DESC , dbo_problem_list_data_.DeletedReason;

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Feb 4, 2015, at 8:42 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Ok here it is
SELECT Person_filter_New.person_nbr, Person_filter_New.last_name, Person_filter_New.first_name, CDate(Left([dbo_person].[date_of_birth],4) & "-" & Mid([dbo_person].[date_of_birth],5,2) & "-" & Right([dbo_person].[date_of_birth],2)) AS DOB, dbo_problem_list_data_.ProblemDescription, dbo_problem_list_data_.DiagnosisCode, dbo_problem_list_data_.create_timestamp, dbo_problem_list_data_.Resolved, Person_filter_New.expired_ind, Person_filter_New.description, dbo_problem_list_data_.DeletedReason, Person_filter_New.LastOfexclude_from_reporting, Count(Person_filter_New.person_nbr) AS CountOfperson_nbr
FROM dbo_person INNER JOIN (dbo_problem_list_data_ INNER JOIN Person_filter_New ON dbo_problem_list_data_.person_id = Person_filter_New.person_id) ON dbo_person.person_id = Person_filter_New.person_id
GROUP BY Person_filter_New.person_nbr, Person_filter_New.last_name, Person_filter_New.first_name, CDate(Left([dbo_person].[date_of_birth],4) & "-" & Mid([dbo_person].[date_of_birth],5,2) & "-" & Right([dbo_person].[date_of_birth],2)), dbo_problem_list_data_.ProblemDescription, dbo_problem_list_data_.DiagnosisCode, dbo_problem_list_data_.create_timestamp, dbo_problem_list_data_.Resolved, Person_filter_New.expired_ind, Person_filter_New.description, dbo_problem_list_data_.DeletedReason, Person_filter_New.LastOfexclude_from_reporting
HAVING (((CDate(Left([dbo_person].[date_of_birth],4) & "-" & Mid([dbo_person].[date_of_birth],5,2) & "-" & Right([dbo_person].[date_of_birth],2)))>#1/1/1929#) AND ((dbo_problem_list_data_.ProblemDescription)>"0") AND ((dbo_problem_list_data_.DiagnosisCode) Like "*401.*") AND ((dbo_problem_list_data_.Resolved) Is Null Or (dbo_problem_list_data_.Resolved)=0) AND ((Person_filter_New.expired_ind)="N") AND ((Person_filter_New.description) Is Null) AND ((Person_filter_New.LastOfexclude_from_reporting)<1))
ORDER BY dbo_problem_list_data_.create_timestamp DESC , dbo_problem_list_data_.DeletedReason;

thank you so much

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Patty-

Switch to SQL view in your query design and copy and paste the SQL in a reply. I need to see your entire query, not just a picture of part of it.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Feb 4, 2015, at 8:24 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

this is what i tried and it would not work