We have imported an Excel spreadsheet with dates in one column that were imported as numbers such as '39287', which can also be displayed in Excel as July 24, 2007. We concatenated this import to a table where all our dates are stored as 20070724. Now we have both formats in one column, with over 70,000 records in the 20070724 format and a few thousand in the Excel format. How can we convert the fields in the Excel format to the appropriate YYYYMMDD format we use?
Many thanks for your help, we can find a good way to clean this up...
Genevatexan
In excel spreadsheet, when I eg type in a numeric column 10 1500 so on, I would column11 appear text such as one thounsand and five hundred. How can I convert this?
Attached in the ZIPPED file is an Excel spreadsheet.
Columns A is all numeric, and needs to be represented in access as a text field. Column B is a mixed format of dates entered and in some instances only plain numeric. I need to import this column as is into a text field in access.
I tried importing the excel sheet, but the data gets changed.I tried to linked the Excel sheet but it also had an influence on the data.In both cases the influence of change is NOT throughout. Hence my need to get this spreadsheet into access as is.
You see what I am trying to do? I am trying to make the Parts a column header, and kind of remove Serial/Part/Batch titles.
My first instinct was to take it into Excel and make a pivot table out of it, but that doesn't seem to work? The pivot table keeps counting up everything, and I don't want anything counted, I just want all data displayed.
I have a text field having data i-e HO-1, HO,2, ACW-25 and so on. The field name is nBadge_num and is Unique. The data in this field is sorted automatically like 1, 10, 11, 12, 13, 2, 3, 4, 5...because this is the text field.
The number on the form is automatically generated, when the user type HO- for example on field exit event. The last number will generate like HO-5.
Code for automatic number generation is:
Dim dbs As Database, rst As Recordset, Response Set dbs = CurrentDb Set rst = dbs.OpenRecordset("SELECT Max(Right([nBadge_Num],Len([nBadge_Num])-" & Len(Me.NBadge_Num) & ")) AS MaxNo " _
[Code]....
My problem is when the number is generated it give HO-5 instead of HO-14, How can I sort the numeric part of the field ?
I have an address field currently formatted 1 Streetname, city, post/zip code it is displayed on a form within a multi-line textbox. I'd like to change the commas to a line return so it will display
1 streetname city postcode
I was thinking that a small macro might go through each record and change the comma to a line return? But not sure where to start exactly?
OK, i'm getting a type mismatch. I've tried searching for that and read a lot of posts, but none hit the nail on the head.
I know where it is. I am trying to link two table by using a field which is a string in one to link to a field which is a number of some kind in the other. I can;t change the formats of these as it's a query i'm running on a database i don't control. I understand I need to convert the format of one of the fields in a query and then link using that, but when i try to reformat the number it doesn't do what i'm expecting after reading the help.
in the help it suggest using this: Format(expression[, format[, firstdayofweek[, firstweekofyear]]])
So i'm trying to use that to reformat the string field to a number so i can use that query.
Numbers: Use predefined named numeric formats or create user-defined numeric formats: This is where i fail completely!! it just rejects everything i write in my query.
I have a table with postcodes (1.6M rows) that have had all the spaces removed. I want to match another table to it and ordinarily would have removed the spaces from the postcodes in that but I think because of the vol (2.5M rows) Access is reluctant (it either crashes or only updates a few thousand). A better way would be to reformat the postcode using some SQL or VBA code. The trouble is the postcodes are different numbers of characters and the space would either need to be after the 3rd when there are 6 characters or after the 4th when there are 7 characters. eg PO121DL becomes PO12 1DL PO91UL becomes PO9 1UL
I can concatenate fields and count characters but am unsure as to the best way to approach this in terms of coding and structure? Using Access 2002.
I am using a template that I downloaded off of MicrosoftOffice.com. I really like the setup but I need to make a few modifications to it, but the modifications I want to make I can not do due to it being a web-based(I think). I am a newbie to access and I am slowly learning about macros, vba codes, formatting, and what not.
I am trying to import a sheet from excel into a new table in access. One of my columns has a date value which, when imported changes to the actual appliacation value (i.e. 1/1/2006 becomes 38718).
Is there a way to stop this happening or a function within acces that changes the value back to a date format?
I have text file which contains different types of Record (identified by RecordType).Its a simple FLAT file having specific record structure for each type of record.
My requirement is 1.Parse the .txt file,identify record type 2.Based on corresponding record structure apply validation rules 3.Generate error report where data violates validation rules.
I am new to access . If any of u have sample code on how to parse any file and show error report accordingly, that will help immensely
I am trying to do the following If any one of you have experienced Abacus ticketing system, each reservation is comprised of a group of names as follows
I have to copy the above data from the abacus tcketing system into a text box. My question is how can i devide the above three names into three seperate text boxes.
I need some assistance parsing some information from a web address. Here is an example of what the web address is.-http://www.websitename.com/ct20/pages2/client_bcphone_service_order.aspx?client_id=160399 4&contract_document_id=924182&cipid=206123444-What I am trying to do is get the client ID and the contract id. The client id being 1603994 and the contract id being 924182. Any ideas would be appreciated.Gregg
hi, im new to both access and sql and was hoping i could get some help...
i have a string of numbers seperated by commas stored in my database. i need to count how many numbers there are in each string and then display all the results with less than 36 numbers contained.
am i able to do this with an sql statement, or am i likely to need vba??
I am using the following statement in an update query (which by the way I found in this forum) to pull out the first name from the FirstName field. The field could have a first name or a first name and a middle initial. If the field has a first name and niddle intital: John P then I get John. If the field only has a first name, I get #Error in the update field. I have thousands of records to move to a new Oracle table which has a first name field and a middle intial field.
FNAME: IIf([FirstName] Is Null," ",Left([FirstName],InStr(1,[FirstName]," ")-1))
I have a field (Name) that is formatted: lastname, firstname
I need to parse the name field so I can update two fields for firstname and lastname....I have done this before, using left and instr functions, but cannot remember how...can you help? thank you! Tricia:)
I'm trying to parse text strings by certain spaces and characters within the string. For Example, if my text string is RAST 2006-A1 B1 mtge, how can I 1) create a new field with just the RAST 2006-A1, 2) Create a field with just the RAST 2006-A1 B1?
I would like to convert existing MS Access forms to an internal Delphi application.
Any information about the fomat of representing forms in the Access database and the method of reading/parsing the forms would be appreciated. Also, I wonder if it's possible to read the forms without using Window's technologies (ADO, etc)?
I have the following string: NE1220904 I need to remove the left two characters and the right four characters of a string in a query. I have found how to remove one side or the other, but not from both sides. Thanks in advance for the help!
Hi, Is it possible to parse, and format a textfield in a report on a line by line basis? What I am trying to do is get a document to be processed into a printable format using reports. The text, and formatting information of each line, and any specific word details are stored in separate columns in the table. If, for example an entire line needs to be made Bold, I can get that done. What I would like to know is that if a word in a particular line needs to be formatted differently to the rest of the line, eg underlined, is there a way to do so?
For example, if I had the line as follows:
The quick brown fox jumps over the lazy dog.
I'd have the default, unformatted text as one field in the table, and the word, in this case quick, and it's formatting in another field of the table.
So rather than stating my question again, can it be done?
We publish a PDF file every week full of taskings that our subordinate organizations need to accomplish for the following week. I would like to have parse the PDF and store the data in a table. I think the only way to start this operation is to first save the PDF to Text (Plain) as this create some way to delimit and parse the data. So, once it's saved as a txt file, it basically looks something like this:
Code: // Column 1 Data/StuffIDontCareAbout/ StuffIDontCareAbout/StuffIDontCareAbout//Column 2 Data StuffIDontCareAbout/StuffIDontCareAbout/ Column 3 Data
[Code] ....
...and so on and so forth anywhere between 50-100 more times
The entire text file will always start and end with "//" at the top and bottom. You also see that each tasking paragraph (starting with "Column 1 Data" and ending with "Column 3 Data") is preceded by a "//" on its own line, and also followed by a "//" on its own line. The text file will always look exactly like this with each tasking paragraph having the same number of forward slashes.
Column 1 Data always starts on a new line under "//" and runs until the first "/" (single forward slash). Column 2 Data always starts after the "//" on the 2nd paragraph line and runs until the end of the paragraph line Column 3 Data always starts on the 4th paragraph line and runs until the end of the paragraph line
Here's a quick example of what I'm talking about:
Code: // 37 NOS001/DCO/ TaskPer/TBD//310001ZAUG2014-292359ZSEP2014 GenText/Remarks/ (U/FOUO) This will contain the actual task description and details. You can see that "(U/FOUO)" contains a forward slash.
[Code] ....
So after it's parsed, my table would have the following new rows:
Code: Column 1 | Column 2 | Column 3 37 NOS001 |310001ZAUG2014-292|(U/FOUO) This will contain 582 NOS012 |280001ZAUG2014-022|(U/FOUO) This another task
I have tables that are ODBC linked to a SQL database. The source tables have record date and time stamps written into one field. This means that I can't use the Access date filter functions on these fields. Is there an easy way to parse this field into two fields, one each for the date and time?
