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

Rabu, 04 Februari 2015

Re: [MS_AccessPros] sorting off prior date conversion

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