Any way to import a file containing numbers with a "thousand space separator"
example :
1 000
2 000
3 000
4 000 000
5 000 000 000
Only workaround I found so far is to import de numbers into a text field in a temp table, do an update query to remove the spaces and then transfert the data into the real table that has a numeric field.
I have a user input form with a text box that stores a number. When the user inputs the number, I'd like it to display the comma separator (e.g. "1234" becomes "1,234". I've tried putting "#,###" and "#,##0." into the format property of the text box, but I still get 1234.
View 4 Replies View RelatedI'm trying to set up an import of an Excel file. One field in Excel, "Sales Tax" has a lead space, so " Sales Tax" is its Excel name. Since I can't name a field in Access starting with a space, the only way I know to import properly is to manually remove the space from the Excel file. While this isn't actually that difficult, I'm trying to avoid this manual step if possible.
View 9 Replies View RelatedHi,
I'm importing the contents of lots of text files using a combination of a loop and DoCmd.TransferText.
This works fine and I can get the contents of several hundred files in a few seconds. The files are called 1.dbb, 2.dbb... and each has an associated image file, 1.bmp, 2.bmp... etc. After the import I move the files to a new folder.
I need to create a link to the bmp file but this information is not included in the text file so I need to rely on a field which contains the file name.
How can I populate a field at import time to include the name of the imported file?
Anyone give me a clue please.
Regards,
Bernard D
Hi there anyone and everyone.
My problem concerns importing a date from a txt file. I've been doing this for years with '97 and 2003 with no problem, but with 2007 I am now getting import errors which I can't resolve. The files, either fixed width or delimited, contain date information in the format ddmmyyyy (without any date separators). After importing I receive an import error message, and the relevant field in the table is blank.
In Access '97 or 2003 I can import date information directly into an Access date/time field by using an import specification where I have set the date delimiter to blank (by default this is set to "/" , so I simply delete this to leave the box empty). Using this method a file containing "25122007" (without the inverted commas) will be imported into Access as a date, and can be subsequently manipulated as date without having to resort to any fancy reformatting tricks. Obviously the import will not be sucessful if the date delimiter in the import spec is left as "/", or set to anything else, eg "." or ":".
But now that I've updated to Access 2007 this is just not possible no matter what I do or try. I can only import a date if the txt data has a separator, and that the same separator is set in the import specification.
In some new databases I have done a couple of workarounds, importing it as text into a text field, then using a CDate(Format(CLng([date]),"00/00/0000")) type expression in a subsequent query, but this is a pain having to do it, a really backward step.
But the real pain is older databases ('97 or 2003) which have been converted up to '07; they no longer work, and there is a lot of redesign to be done unless somone can tell me what I'm doing wrong.
Unfortunately I am not able to change the format of the dates in the data I receive, so the solution has to come from my end (ie Access). Surely Access 2007 should be able to recognise 25122007 as a date? All ideas gratefully received.
I've been happily using various versions of Access since 1994 and I've learnt lots along the way, and have always previously been able to sort out problems that I've come across without resorting to a forum, but I'm now really puzzled by a problem that I have come across in Access 2007. So although this is my first post I certainly wouldn't call myself a newbie. Hopefully I have described the problem accurately (it is realy simple in fact), but very happy to provide more information if required.
Many thanks (in anticipation)
Benjy
I have a form which i use for a user to select an excel file they want to import and then click a cmd button to import the file into a table which works fine, however i want to append a date into a date field from an unbound txtbx before the file is imported so it will look something like;
id;date;excel info;excel info;excel info.
hello,
I would like to automate something presently done on a one-by-one basis. Here: a number of text files(containing data) are to be exported into an MS Excel file, with each text file to occupy a different worksheet. Presently, the idea is to use the Data/import external data/import data feature of MS Excel for importing the text files one-by-one into newly created worksheets(within the same workbook).
I would appreciate some advice on how to go about creating a useful MS Access application to achieve the above. I have checked the available Macros in MS Access, but I could not find one to suit my purpose. can anyone pls assist, on how I can get started?
Tokunbo
hello everyone, i really need some detailed help as the deadline is approaching and I need to find a solution for this. Any help would be greatly appreciated
I currently have a batch file that ftps a text file from a Red Hat Linux Server to my W2k C:. I would like to make another command in the batch file that imports this text file into an existing access table. I would like the text file to repopulate the table everytime it is imported. I do not want the data added on to the existing data in the table.
Thank you for your time and insight.!!!:)
I imported an Office 2010 excel file into Access 2010. All the headers match.
All the data transfered, but the phone numbers.
In Design View, the phone numbers are listed as numbers, not text.
Then I tried to copy the two columns from Excel and past into the two columns in the database. No go. It did create a new table called Paste Errors.
How do I move that into the Table that I first created?
I need to know how to get the phone numbers in their because I need to import several other excel files into this table.
Hi!
Is it possible for me to alias the fldGroup to Function Group?
How do you alias a field name to a name that contain a space?
Please advise.
Aijun.
I have a table that track monthly data for several thousand products.
The idea is to grab the first month and the first three months of sales for each product.
So I simply need to have a statement that scans through a predefined set of fields and seeks the first none zero value. This become the first month of sales (even if it is the 5th month of the year). It then needs to be able to grab the proceeding 2 months to create a sum of sales in the first 3 months. (however that would be for a second field, so presumably that is just a slightly more complex version of the same formula used to find the first month of sales).
Hi!
I have a field that has to be 20 Characters long. Most of the time data for this field is less than 20 characters, so the remaining has to be filled with spaces to make it 20. e.g. if there is a record xyz1234567, then the rest of the record should have 10 spaces like xyz1234567ssssssssss, meaning if i click on this record then the cursor should not blink after 7 but after the spaces. Hope I am making sense.
Thanks for any input.
Is there a way in a masked date field(mm/dd/yyyy) that when clicking anywhere in the field that the cursor can be at the beginning of the field? For instance if I were to click in the position of the dd can I get the cursor to be at the first m.
Thanks for any help.
T
In my Dbase, I have a table with a text field. Field name is Country
There are over 5000 records in the table and many of these in the Country field start with a space. I would like to get rid of this space as to ensure that all text starts with a character, rather than a space.
How can I achieve this.
I have a query that I export to a text file with fixed width settings.
For one particular field that had only been one character and 5 spaces I used PGM: " " & [ProgramCode] for my field. The recipient of the export file requires that this field be 6 spaces long and leading spaces fill in any unused field.
Now, one of the records for this field will be 2 character so the way it is now will cut off the leading character. How can I write this to add leading spaces until it reaches 6?
The above image is of a table which I need to update according to a new data on daily basis. As you can see when I added two records at the last , the AutoNumber primary key of the table jumped by thousands . I have used the following query to update the table
Code:
db.Execute "INSERT INTO D_Counterparty (CPTY_ENTITY_ID,CPTY_DESC)"_ & "SELECT Ctpy_Entity_Id,Ctpy_Entity_Legal_Nm"_ & "FROM NewCU LEFT JOIN D_Counterparty ON D_Counterparty.CPTY_ENTITY_ID=NewCU.Ctpy_Entity_Id"
I never changed the AutoNumber from incremental to random and I have also tried re-seeding it but had no success. Also when I inserted the sample values using a sample table ,the AutoNumber was working fine . Here's that query
Code:
db.Execute "INSERT INTO D_Counterparty (CPTY_ENTITY_ID,CPTY_DESC,) SELECT a,b, FROM sample"
I did not use join in this query ...
Not = " " And InStr(1,"seat_person"," ") <> 0
I have a field into which I wish to place a person's name and I want to ensure that the field has a space in it but is not a blank field,
so
all blank - illegal
"Bob" - Illegal
"Bob Smith" - legal
the edit at the top is my attempt to code it but it isn't working.
I want to be but to save a space in a field on a table but every time I do it appears to be a NULL and therefore fails a validation rule that I wish to preserve.
View 14 Replies View RelatedI use several fields to capture a name: LName, FName, MI and Suffix.
In a report I combine the fields.
=[LName] & " " & [Suffix] & ", " & [FName] & " " & [MI]
This works fine if there is a suffix; however, without a suffix I have an extra space.
What is the "work around" to eliminate the space.
I like to store SQL code as a memo field in a table. I then use a form to collect selection criteria from the user, and this is used to create a 'WHERE' clause which is then appended to the stored SQL. This has always worked, but in one recent case the SQL failed. On examination I discovered that although the SQL is correctly stored in the memo field, when Access retrieves the code it inserts a space character in the middle of one of the words, thereby of course creating an invalid SQL string. It's easy to work around it, but why this happens? A bug in Access?
View 1 Replies View RelatedI have MANY scanned image (.tif) files in multiple folders based on certain criteria. I need to find a way to import only the names of these (.tif) files into a table or even into an excel spreadsheet. Since they are scanned images you can not do a simple copy / paste.
I would appreciate if anyone has any ideas how this could be mastered
I have SQL code that would import an Excel file as follows:
Code:
Str = "SELECT [Ed_TEST$].* INTO [tblAct_Import_File] FROM [Ed_TEST$] IN '" & impFile & "' [Excel 8.0; HDR = YES;];"
cn.Execute Str
I do not want to use do not want to use "DoCmd.TransferSpreadsheet" I am finding do to rounding some figures are braught in a little off. However, I have found that if I use code.
How can I adjust my required field so that you cannot simply enter a space bar character to circumvent the requirement?
Here is part of the code:
If IsNull([txtRequiredReason]) Then
MsgBox "The Reason field is required"
DoCmd.GoToControl "txtRequiredReason"
Else
I'm working on a report in Access 2013 and I have a multivalued field that stores employees names in the format "Last, First" and when I put it on my report it displays all the names as "Last1, First1, Last2, First2, Last3, First3" and so on. That's a lot of commas and it was suggested to me by my boss that I change access to use semicolons between the employees as he would like to keep the names as Last, First.
View 7 Replies View RelatedI needed to change the decimal symbol from a dot to comma for use in Europe. On changing the the settings to a comma everything works fine but for the field that I have the code in the AfterUpdate properties
Private Sub SprayCfuelC_AfterUpdate()
Me.Dirty = False
CurrentDb.Execute ("UPDATE ProcessData SET ProcessData.SprayCFuelC =" & Me.SprayCfuelC & " WHERE (((ProcessData.AuditNo)=" & Parent.[AuditNo] & "));")
Me.Recalc
End Sub
Now when the data is changed I get a run time error 3144 - syntax error in UPDATE statement.
I have a lot of dates in one column (dd/mm/yy), and i somehow need to duplicate them into another column where they are shown without the date separator (ddmmyy), so that i can use that figure in a future calculation where a variety of fields are combined to show one unique product number.
View 7 Replies View Related