Re: [MS_AccessPros] Date question

 

Patty-

Try this:

SELECT dbo_person.person_id, dbo_person.person_nbr, dbo_person.last_name, dbo_person.first_name, dbo_person.date_of_birth, 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_person.sex, dbo_person.expired_date, dbo_person.expired_ind, Chart1_filter_24_mos.LastOfmodify_timestamp, Chart1_filter_24_mos.LastOfinsurance1, Chart1_filter_24_mos.LastOfinsurance2, Chart1_filter_24_mos.LastOfPCP_DBP, Chart1_filter_24_mos.LastOfexclude_from_reporting, Pt_Status.description

FROM Pt_Status RIGHT JOIN (dbo_person INNER JOIN Chart1_filter_24_mos ON dbo_person.person_id = Chart1_filter_24_mos.person_id) ON Pt_Status.person_id = Chart1_filter_24_mos.person_id
WHERE (((Chart1_filter_24_mos.LastOfmodify_timestamp)>#1/1/2013 0:1:1#));

I'm not sure what you're trying to accomplish with:

WHERE (((Chart1_filter_24_mos.LastOfmodify_timestamp)>#1/1/2013 0:1:1#));

Is LastOfmodify_timestamp a Date/Time field, or is it a string?  Also, I'm not sure how Access will interpret #1/1/2013 0:1:1# - I'm assuming you want any records later than 1:01 AM on January 1, 2013.

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 Jan 31, 2015, at 5:14 PM, pattykf@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

SELECT dbo_person.person_id, dbo_person.person_nbr, dbo_person.last_name, dbo_person.first_name, dbo_person.date_of_birth, dbo_person.sex, dbo_person.expired_date, dbo_person.expired_ind, Chart1_filter_24_mos.LastOfmodify_timestamp, Chart1_filter_24_mos.LastOfinsurance1, Chart1_filter_24_mos.LastOfinsurance2, Chart1_filter_24_mos.LastOfPCP_DBP, Chart1_filter_24_mos.LastOfexclude_from_reporting, Pt_Status.description
FROM Pt_Status RIGHT JOIN (dbo_person INNER JOIN Chart1_filter_24_mos ON dbo_person.person_id = Chart1_filter_24_mos.person_id) ON Pt_Status.person_id = Chart1_filter_24_mos.person_id
WHERE (((Chart1_filter_24_mos.LastOfmodify_timestamp)>#1/1/2013 0:1:1#));

this is the sql query as it stands without the string, it would not let me go to sql with it in the criteria
thank you

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

Patty-

Version shouldn't matter.

With your query in Design view, click the Drop-down under the View button on the far left and choose SQL.  Copy and paste the text you see into a reply.

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 Jan 31, 2015, at 4:58 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Thank you for the information on replying with the messages,
I actually copied and pasted your string right into my query so as to not put in extra spaces etc, but still get an error it goes to the L in left again. I am in Access 2007 would that make a difference?

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

Patty-

If you're replying on the web, be sure to click Show Message History after you click the Reply button.  That will include previous replies so we can keep track of what has been recommended before without having to go searching on the website.

It looks like you have extra spaces in your expression.  Try this:

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

The arguments for the Mid function are:

1: String source

2: Offset into the string source to start  (in this case, the 5th character (was an error using 3))

3: Length of the string to extract

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 Jan 31, 2015, at 4:26 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I am sorry but for whatever reason this is not working. the error message (invalid dot or expression clicks to the parentesis before the Left word.

Just to give the info on the situation for those that tried to help but I did not give enough information
I have an access database that pulls in data from our electronic health record, The date fields in the tables are all different and one is a text field that I need to convert to a date field in a query. 
the table is dbo_person and the field is date_of_birth  the dates look like 20150101 in the source table

I have the query set up to pull this field, I entered the following in the criteria field (maybe that is the problem still learning)

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

For my understanding since I am still learning this stuff, what this is basically saying is convert the date, starting at the left of the particular field, take the first 4 numbers and then put in a -, take the 2 middle number and then put a - and then put in the last numbers to get the following 2015-01-01 for example. (not sure what the 3 means in the mid part of the formula)

If there is a good resource that I could go to learn more of this I would be glad to read up more on this. I really want to learn as much as I can about this.
thank you for your patience and assistance.

__._,_.___

---

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 (23)

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___