We had a dusting of snow yesterday afternoon, but nothing stuck. Supposed to bet another dusting tonight, but no accumulation. Typical gray winter in Paris, but we do get bits of sun every once in a while. Temp hovering around 32F.
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
(Paris, France)
On Jan 31, 2015, at 5:46 PM, pattykf@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
for your question on the Where last modify timestamp- the field where the last modify stamp is a date and time field, so I found a way to have that one only return the patients seen after jan 1 2013 at 12:01:01 AM, I really wish these electronic health record companies could use one or similiar date formats in their tables, but got to work with what we get.
thank you on this, really helpful, sorry it too so many replies to get to the bottom of it.
Have a wonderful weekend.
We are due for another big storm in my neck of the woods, 8-12 inches snow again in less than a week. oh fun.
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
thank you on this, really helpful, sorry it too so many replies to get to the bottom of it.
Have a wonderful weekend.
We are due for another big storm in my neck of the woods, 8-12 inches snow again in less than a week. oh fun.
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
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#));
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
(Paris, France)
On Jan 31, 2015, at 5:14 PM, pattykf@... [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, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(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 source2: Offset into the string source to start (in this case, the 5th character (was an error using 3))3: Length of the string to extractJohn Viescas, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(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 (27) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar