Re: [MS_AccessPros] sorting off prior date conversion

 

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@cox.net [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

 So I must be doing something wrong but just not sure what, it converts the date but will not allow me to put in a date criteria, this one will only need a date less than, but I will have others that I need to do date ranges for

as always, thank you for all your help
Patty


---In

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

I want to make sure i understand this, so in the new query I need to also put int he CDate information to have it convert the date to this correct format?  It already is showing the correct date format in the query. Where do i put the new CDate information in the query
I must sound like I have no knowledge in this and you are correct, learning but uphill and you are a huge support, sorry if it is a bother.
Patty

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

Patty-

Where is the CDate call in your DOB field?  It should read:

DOB: CDate(Left([dbo_person].[date_of_birth],4) & "-" & Mid([dbo_person].[date_of_birth],5,2) & "-" & Right([dbo_person].[date_of_birth],2))

Then your criteria should work.  You're getting a data type mismatch because you're comparing the original string date_of_birth with a date/time literal.

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 7:46 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I am not sure I have it correct since still getting error, in the query I made the date of birth field an alias DOB
and put in date greater field

I still get a data missmatch error  the second picture is of the field before I try to add the date criteria
Patty

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

Patty-

The CDate should work as long as all your rows have yyyymmdd format in the date_of_birth field, but you must give the new field an alias like DOB - AS DOB.  Then in the Criteria line, use something like:

BETWEEN #01/01/1960# AND #12/31/1970#

Note that you have to use hashtags as delimiters for date literals.

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 

John Viescas Consulting

			John Viescas Consulting If you're at all interested in Microsoft Access or SQL Server, this is the place to be! Check out the book recommendations and all the cool links!
			View on www.viescas.com	Preview by Yahoo

 

 

(Paris, France)

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

I am trying to pull information matching up my person table where we did a date conversion formula on it with a diagnosis table to give me the list of  patients with the identified diagnosis but within a date range in the date of birth. we used this conversion (see below) based on a date that was a text field of a date. I tried to use the "text" format for the date as well as the  normal date format but it gave me data missmatch error. How do I now work with this date format?

dbo_person.person_id, dbo_person.person_nbr, dbo_person.last_name, dbo_person.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 date_of_birth

Thank you for all of your help

Patty

 

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (12)

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___