ACCESS FEED: Re: [MS_AccessPros] Date question

Sabtu, 31 Januari 2015

Re: [MS_AccessPros] Date question

thought maybe a picture would help, ??

---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.