RE: [MS_AccessPros] sorting off prior date conversion

Patty,

Does every record have a valid entry or are some null? If there are nulls, how do you want them handled?


Duane Hookom MVP
MS Access

________________________________
> To: MS_Access_Professionals@yahoogroups.com
> From: MS_Access_Professionals@yahoogroups.com
> Date: Wed, 4 Feb 2015 10:46:30 -0800
> Subject: 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
> [https://a.gfx.ms/i_safe.gif][https://a.gfx.ms/i_safe.gif]
> 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<http://www.viescas.com/>
>
>
> [image] <http://www.viescas.com/>
>
>
> John Viescas Consulting <http://www.viescas.com/>
> 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 <http://www.viescas.com/>
>
> Preview by Yahoo
>
>
>
>
>
>
> (Paris, France)
>
>
>
>
> On Feb 4, 2015, at 7:19 PM, pattykf@...<mailto:pattykf@...>
> [MS_Access_Professionals]
> <MS_Access_Professionals@yahoogroups.com<mailto: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
>
>
>
>


------------------------------------

------------------------------------


------------------------------------

Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/