Adjusting field data with update query

I run an import where the date is formated as follows:
  Wed Aug 22 11:47:43 -0500 2007

In excel I created the following macro to adjust the format to mm/dd/yy 
hh:mm:ss
 For Each Cell In Range("e2:h" & Range("e65535").End(xlUp).Row)
        If Cell.Value <> "" Then
            Y = Right(Cell, 4)
            M = Month(Mid(Cell, 5, 6))
            D = Mid(Cell, 9, 2)
            T = TimeValue(Mid(Cell, 12, 8))
            Cell = DateSerial(Y, M, D) + T
        End If
    Next

Can I create something similar with an update query?
I tried placing the following into the 'update to', but it simply deletes 
the entire field:

DateSerial(Year(Right([Created On],4)),Month(Mid([Created 
On],5,3)),Day(Mid([Created On],9,2)))

Microsoft Access 2003
-- 
KamronsDad
0
Utf
9/26/2007 11:07:01 PM
access 16762 articles. 3 followers. Follow

2 Replies
463 Views

Similar Articles

[PageSpeed] 24

=?Utf-8?B?S2Ftcm9uc0RhZA==?= <KamronsDad@discussions.microsoft.com>
wrote in news:8EE3DB28-05BA-4C93-BCCF-EDACD83EA7FA@microsoft.com: 

> I run an import where the date is formated as follows:
>   Wed Aug 22 11:47:43 -0500 2007
> 
> In excel I created the following macro to adjust the format to
> mm/dd/yy hh:mm:ss
>  For Each Cell In Range("e2:h" & Range("e65535").End(xlUp).Row)
>         If Cell.Value <> "" Then
>             Y = Right(Cell, 4)
>             M = Month(Mid(Cell, 5, 6))
>             D = Mid(Cell, 9, 2)
>             T = TimeValue(Mid(Cell, 12, 8))
>             Cell = DateSerial(Y, M, D) + T
>         End If
>     Next
> 
> Can I create something similar with an update query?
> I tried placing the following into the 'update to', but it simply
> deletes the entire field:
> 
> DateSerial(Year(Right([Created On],4)),Month(Mid([Created 
> On],5,3)),Day(Mid([Created On],9,2)))
> 
> Microsoft Access 2003

Your year() month() and day() functions serve to extract the 
relevant portions from the date, which is stored in Access as a 
double precision number, with the integer part being the number of 
days since Dec 31, 1899 and the fraction the percentage of the time 
passed in the day. You cannot use them against a string.

Also since a date in Access is a number, it doesn't belong in a text 
field, but in a proper date/time type field. Excel doesn't care what 
goes where.

To convert a string to a date, use the cdate() function in your 
update to row of a new field to hold the date.

CDate(Mid([created on], 5, 6) & Right([created on], 5) & Mid
([created on], 11, 9))

-- 
Bob Quintal

PA is y I've altered my email address.

-- 
Posted via a free Usenet account from http://www.teranews.com

0
Bob
9/27/2007 12:33:01 AM
Bob,

Thank you so much for the explanation, and the code necessary to make it do 
what I needed to.
-- 
KamronsDad


"Bob Quintal" wrote:

> =?Utf-8?B?S2Ftcm9uc0RhZA==?= <KamronsDad@discussions.microsoft.com>
> wrote in news:8EE3DB28-05BA-4C93-BCCF-EDACD83EA7FA@microsoft.com: 
> 
> > I run an import where the date is formated as follows:
> >   Wed Aug 22 11:47:43 -0500 2007
> > 
> > In excel I created the following macro to adjust the format to
> > mm/dd/yy hh:mm:ss
> >  For Each Cell In Range("e2:h" & Range("e65535").End(xlUp).Row)
> >         If Cell.Value <> "" Then
> >             Y = Right(Cell, 4)
> >             M = Month(Mid(Cell, 5, 6))
> >             D = Mid(Cell, 9, 2)
> >             T = TimeValue(Mid(Cell, 12, 8))
> >             Cell = DateSerial(Y, M, D) + T
> >         End If
> >     Next
> > 
> > Can I create something similar with an update query?
> > I tried placing the following into the 'update to', but it simply
> > deletes the entire field:
> > 
> > DateSerial(Year(Right([Created On],4)),Month(Mid([Created 
> > On],5,3)),Day(Mid([Created On],9,2)))
> > 
> > Microsoft Access 2003
> 
> Your year() month() and day() functions serve to extract the 
> relevant portions from the date, which is stored in Access as a 
> double precision number, with the integer part being the number of 
> days since Dec 31, 1899 and the fraction the percentage of the time 
> passed in the day. You cannot use them against a string.
> 
> Also since a date in Access is a number, it doesn't belong in a text 
> field, but in a proper date/time type field. Excel doesn't care what 
> goes where.
> 
> To convert a string to a date, use the cdate() function in your 
> update to row of a new field to hold the date.
> 
> CDate(Mid([created on], 5, 6) & Right([created on], 5) & Mid
> ([created on], 11, 9))
> 
> -- 
> Bob Quintal
> 
> PA is y I've altered my email address.
> 
> -- 
> Posted via a free Usenet account from http://www.teranews.com
> 
> 
0
Utf
9/27/2007 1:50:01 PM
Reply:

Similar Artilces:

Remote data access
As a new .net developer, I would like to know how a VB.net Windows application can access a SQL Server database residing on a web server. In other words, using the Visual Studio IDE, is there a way to develop a Visual Basic, Windows application that can access a SQL Server database over the internet. Thanks for suggestions, John C. John C. wrote: > As a new .net developer, I would like to know how a > VB.net Windows application can access a SQL Server > database residing on a web server. > > In other words, using the Visual Studio IDE, is there a > way to develop...

Data Validation #12
Hi. I have data validations in sheet1 as named range lists from sheet2. When I have deleted sheet2 and copy sheet2 from identical other workbook - my validations does is not work... I see Name ranges in copied worksheet, but when I use they in validation - I have error: "The Source currently evaluates an error. Do you wish continue?" Only after deleting of Named ranges in Sheet2 and recreating its, my validations works. It is possible after replacing of worksheet with named ranges for validations (named ranges names are identical) use copied worksheet without recreating ...

field name or schema name?
When i adjust excel column-names to match CRM 3.0 field names, preparing for bulk-import, should i use the (label) field names in CRM 3.0, or the schema-names for example, if i made a new country field, use Country 2 or New_Country2 thanks Kas use the schema-names -- -Nick "_Kas_" wrote: > When i adjust excel column-names to match CRM 3.0 field names, preparing for > bulk-import, should i use the (label) field names in CRM 3.0, or the > schema-names > > for example, if i made a new country field, use > > Country 2 > > or > > N...

Data Entry Form 03-20-08
Hi, I created a form to use for viewing currently records and entering new data. This time, the form doesn't allow me to enter new data. In fact, all text boxes didn't let me to enter new information. Please help Thanks Chi Several things to check: In form properties: Allow Additions = Yes Allow Edits = Yes If the form's record source is a query, be sure the query is updatable. -- Dave Hargis, Microsoft Access MVP "Chi" wrote: > Hi, > > I created a form to use for viewing currently records and entering new data. > > This time, the ...

calculated field 12-30-09
i have a query with the following fields: year, month, customer, debit euro, credit euro and balance (debit-credit). the query returns multible entries for each customer. e.g. customer X in November 2009 has a balance of 500 euro and December 2009 has a balance of -100 euro. i want to build a field that returns 500 euro for November and 400 euro for December. can i achieve that in a query? i also want to create another field that returns a date. in the above mentioned example: if year is 2009 and month is 11 i want to add thrre months, that is the filed should return the d...

stock quotes not updating
Money 2004 ... premium ... stopped retrieving quote updates 10/5. I tell it to update prices, it says it's making the call, then returns "completed" but prices haven't changed, and dates for last quote haven't changed either. In microsoft.public.money, Frank wrote: >Money 2004 ... premium ... stopped retrieving quote updates 10/5. > >I tell it to update prices, it says it's making the call, then returns >"completed" but prices haven't changed, and dates for last quote haven't >changed either. Go to the Portfolio view, and C...

Sorting lots of data
We are trying to sort through a csv file that we converted to an excel spreadsheet. the spreadsheet has over 40,000 entries which include mailing addresses that are in some cases duplicated. is there anyway to sort/filter the info to locate the duplicates and then remove them?? any help would be greatly appreciated. thank you for your responses. Hi use 'Data - Advanced Filters' and check 'unique entries' or use the following addin for this: http://www.rondebruin.nl/easyfilter.htm -- Regards Frank Kabel Frankfurt, Germany miss lynar wrote: > We are trying to sort thr...

Stacking Data??
if I have data like this A B C D... 1 Type\Date 01/10/04 02/10/04 03/10/04... 2 T-X 100 50 100 3 T-Y 50 70 0 4 T-Z 400 0 400 ... .. ...

Add Text Field Columns
PFDB: IIf(Nz([PFDirect])+Nz([PFBulk])=0,"",Nz([PFBulk])+Nz([PFDirect])) I have two fields both of which are text. When I try to add them as above, I get a concatenated result, not the sum. How does one add them? -- On Fri, 18 May 2007 17:38:49 -0600, bw wrote: > PFDB: IIf(Nz([PFDirect])+Nz([PFBulk])=0,"",Nz([PFBulk])+Nz([PFDirect])) > > I have two fields both of which are text. When I try to add them as above, I get a > concatenated result, not the sum. How does one add them? How does one add "This is text." + "this is also text&qu...

Smartlist current user id field
Hi all, Is there any table I can use that will indicate the user id of the user running the particular SmartList ? For one example, I want to build a report that only shows Orders that the current user who is running the report created. That way each person who wishes to run this report does not have to enter in the criteria their own ID each time the run it... Does that make sense ? Thanks, Chris B. If you are referring to the sales order out of the box, then this isn't possible. If this report comes from a SQL View that you've written then potentially you could. However t...

Pivot Table fields #2
Is there a way to format multiple pivot table fields at one time and have them refresh that way, rather than having to select each column and choose the format options? Thanks CLou: I think the solution for you is to open the Pivot Table Toolbar and select the Field Settings icon. This sets the format for all results for that particular field. One limitation that I haven't found a way around is getting a preferred column size to "stick" and not reset after a table refresh. Bruce >-----Original Message----- >Is there a way to format multiple pivot table fields at ...

diable command button based on field value
I have a command button which I would like to disable based on the following rules: If A = 1 then button is disabled If B = 2 then button is enabled Where you put the code depends on how the form works. If this needs to be checked for each record, use the form current event: If A = 1 Then Me.Button.Enabled = False ElseIf B = 2 Then Me.Button.Enabled = True End If But, what if A is not 1 and B is not 2? -- Dave Hargis, Microsoft Access MVP "barrynichols@gmail.com" wrote: > I have a command button which I would like to disable based on the >...

Turn data #2
Is there a way to turn the data in an Excel spreadsheet so that what is on the top is now on the side and what is on the side is now on top? I need to export my spreadsheet into Access but, I can't get it to format correctly in its current orientation. ~Make a backup copy of your data to work from (just in case, so you don't lose data) ~Highlight the entire range of data; click >Edit >Copy ~Choose a new location, on a different sheet perhaps; click >Edit >Paste Special and select the Transpose box; click OK Does that do it for you? thank you so much. You are a genius...

order data by a rank
i need to create a form order by the militaty rank.This form is based in a query "qrystaff" with three fields: MemberID FullName and Rank.I´d like that the data were ordered firstly by MilitaryRank and secondly by FullName in alphabetical order.There is an easy way to do this? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1 When you create queries, you're given the option to specify the sort order under each field. If the fields aren't in the order from left-to-right in the query grid, you can add ...

Data in columns not rows
I have file that is names, addresses and phone numbers. When I copy it into excel it comes out like this. (COLUMNA) NAME ADDRESS ADDRESS2 PHONE I need it to be like this COLUMNA COLUMNB COLUMNC COLUMND NAME ADDRESS ADDRESS2 PHONE Is there a way to change this without copying and pasting? Jenn Is the data consistently 4 rows? Or do you have varying sets? How far down Column A do the sets extend? If 4 rows per set try this..... In B1 enter =INDEX($A$1:$A$3000,(ROW()-1)*4+COLUMN()-1,1) Drag/copy across to E1 then select B1:E1 and drag/copy down u...

MS Query #4
First, does someone know of a document that provides details on MS Query functions and their syntax. For example, I know I can use left, right, etc. What other functions exist? I want to do some parsing on some data fields and need to determine the length of the field and the place where certain characters are located within that field. Thanks. Hi ODBC query syntax is determined by driver, not by Excel. Usually it's like the syntax, used in database system, from where you get data. P.e. the syntax for FoxPro/VisualFox ODBC query is much like to syntax, you use with queries in F...

Using a Text / Data output as a cell reference
I am trying to use the end of a column as a divisor and need to convert what i guess is text into an actual cell reference if possible. In column A, say there are 13 data points ending at cell A13. I then try to turn that into a cell reference with the formula ="A"&TEXT(COUNTA(A1:A13,),"0") I put this formula in B1 the output of this is then A13 what i need this to do, is be able to divide any cell by A13. The reason I am going through all this trouble is that there are many columns and each has a different # of data points. I hope this is somewhat clear. Thanks ...

SQL update.
Hi NG. I have a: Table_1 Query_1 Query_2 Report_1 Form_1 Module_1 with 1 macro. In my Formula_1 I have a ButtonA to activate Module_1 where I Delete all records in Table_1 and import a txt-file to Table_1. With ButtonB I can activate Report_1. Next to this Button I want count from Query_2. Query_2 (Count records in Table_1 (by using Query_1) and is also for Excel): SELECT Count(Ellos_SE_Alm_Q.Postby) AS SE_Alm FROM Ellos_SE_Alm_Q; I have Query_2 as a SUBform in Form_1. My problem is that I must close/open Form_1 for update and that is annoying. I do hope for a...

No way to have "event" action for query... right?
Often I create a query and corrosponding Form (in data sheet view) I then create a Double Click Event for one of the fields That opens a traditional form showing all the records fields. For instance a list of Accounts and Names. Double click the Account to open a form to see the entire Account record... or whatever. Is there any way to do this without creating the form? We need the query as we use it to select appropriate data. thanks for any help. Mel You can actually base the form off of an SQL statement, and not need to have a saved query. In the form's data tab, just paste ...

Display of CRM data in SharePoint
Is is possible to allow non-CRM users the ability to view CRM data on SharePoint without using the CRM external connector? Thanks, -Rick M. Microsoft would require you to have a license per user I think ============================== John O'Donnell Microsoft CRM MVP http://www.crowecrm.com "Rick M" <RickM@discussions.microsoft.com> wrote in message news:B3E731A1-D059-4D49-B879-6025A1817FD8@microsoft.com... > Is is possible to allow non-CRM users the ability to view CRM data on > SharePoint without using the CRM external connector? > > Thanks, > -Rick...

Outlook Data File Not Closing Properly
I always get the following error message when starting Outlook 2002 (running Office XP and Windows XP) : "The data file 'outlook' was not closed properly. The file is being checked for problems." I get this message everytime (whether I hibernate or shut down) when I start Outlook. This checking of the file can take anywhere from 30 seconds to 5 minutes and is very frustrating. Anyone have any idea what the issue is ? This started happening just after I installed XP SP3. ...

BP Installation Error
Someone post this question before:- MbfPackager data importation process has failed. please c:\DOCUME~1\ADMIN~1\LOCALS~1\Temp\MbfPackager.Import.NoMerge.Common.xml for more information. Problem: When installing Microsoft Business Portal 2.5. Can anyone help me on this error? I'm installing Business Portal 2.5 Feature Pack on Windows 2003 Standard Edition. And, the BP is reading the Great Plains 8.0 database at another server. Cheers, Emily ...

Change color on date field in form for re-certification
My main form has two different date fields on it. One date field needs to be re-certified every 6 months, and the other field needs to be re-certified every 12 months. I would like each of those fields to automatically change color depending on how much time as elapsed since that training. For the 6 month field, months 1-5 should be green, 1 month left should be yellow and anything expired should be red. For the 12 month field, months 1-11 should be green, 1 month left should be yellow, and anything expired should be red. Thank you in advance for your help! messingerjc wrote...

Migration: Migrated custom fields can't sync with plan
We migrated a set of enterprise global fields from 2003 to 2007. However, some of the post migration changes to these fields can't be reflected into project professional for all plans. Example, we have a project outline code in 2003 called Portfolio, which is a hierarchical set of values. In 2003, we allowed selection of non-leave node and didn't allow multi-selection. After migration, we changed the settings of the field to allow only leave node and multi-selection. However, these changes are neither picked up by migrated project plans nor newly created project plan....

Access data
Is there a way to open an Access 97 database with Excel 97? Hi C, I have a hyperlink in my excel workbook that opens the access workbook, i use it for correction purposes. Would a hyperlink work? Larry -- keithl816 ------------------------------------------------------------------------ keithl816's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21287 View this thread: http://www.excelforum.com/showthread.php?threadid=492101 In Excel try Data>Import External Data>Import Data or New Database Query Gord Dibben Excel MVP On Fri, 9 Dec 2005 07:43:...