I have a csv file which seperates each field with a comma. Now i have some fields that contain comma's within them but they are enclose in quotation marks. How would i import it so that it doesnt seperate these fields?
View RepliesHi
I'm having a senior moment...
I have a reference field - examples below and need to split into the component parts
example Ref_IDs:
C21/0051,Sheet 18, Sheet 19, E 3, 12/01/07
C21/0052.04, Sheet21 ,E 2
C21/0054, Sheet 23, B 2, 05/01/07
First - ref no, Second - Sheet (note some have more than one), Third - Grid and lastly some have dates, but not all
I am using Access 2013 and have created a music inventory database.I have a question about formatting some text in a table. One of my tables has the following fields:
tblArtist
Artist ID
DVD Number
Artist Last Name
Artist First Name
Artist Full Name
In my form, I want the artist's name to appear like this:
Mozart, Wolfgang
Dylan, Bob
Beatles
Aerosmith
I know how to format the table to do this, and I know how to construct the form for this. To format the name, in the tblArtist table, I use the following expression in the Artist Full Name field:[Artist Last Name] & ", " & [Artist First Name]
If an artist has only one name, such as Aerosmith, I enter it in the Artist Last Name column. Artists such as The Beatles or The Rolling Stones, I just omit "The."When a one-name artist's full name appears in the form, there is a comma after their name.
Is there a simple way to eliminate the comma if there is no text in the Artist First Name column? If so, what is the simplest, easiest, most practical novice-friendly way to do this?
I have one field containing a couple of 6 digit codes and would like to count the number of codes in the field and write the number to a calculated field. Each code is separated by a comma and a space. How can I count the no of comma within the field?
View 3 Replies View RelatedWith microsofts article, I have made to store multi options value of a list box in a text box with comma. However, since these are IDs being stored, I want these values to run a query and get results also.
But I am confused since have never used comma like in query
I have inherited a database where technicians track equipment that has been repaired.In this database, there is a memo field where the technician lists all the part numbers he / she uses to repair the piece of equipment. This field was never reported against and was strictly used for reference. However, someone is coming to me now and asking that a report be generated with that information. Then she will separate those parts out on her end. because it is a free form field, the technician is not forced to put a comma between each part number. I was wondering if there is a way for the database to go through approximately 62,000 records and ensure that there is a comma between each part number? I was also wondering if there is way to program the database to automatically insert a comma after each part number.
View 5 Replies View RelatedI've current got two tables, one with Products and the other with Suppliers. The products table has a column in which I may indicate Supplier IDs that carry the product. I'd like to make a report that groups the Products by the Supplier that carries them and I'm having some trouble. The way I see this working is to relate the Supplier ID found in the Product table with the auto generated Supplier ID in the supplier table.
My trouble is this, when the field is set to text I'm able to enter values separated by commas no problem but I can't report on them because I'd be relating a number to a string. How may I overcome this?
I would like update a table column values separated by ; For example
1. ColumA has no data, I want to do an update and place a comma at the end
2. If ColumA has already value do not delete add new valued at the end where ; if found.
My start attempt as follow:
Code:
qry_test= "UPDATE Table1 SET ColumA =" & "Enter Entered ;" & & _
" WHERE ID=" & 1
Set RS = cnnDB.Execute(qry_test)
What do I need to do to add more stuff where ; is found without delete existing value if values exist.
I have a table like this below:
ProductID ProductName
1 AAA
2 BBB
3 CCC
I have a field in another table, like this:
RelatedProducts
AAA,CCC
AAA
AAA,BBB,CCC
I need a field in one of my queries that will return the related products field data like this:
RelatedProducts
1,3
1
1,2,3
I'm trying to turn any period entered into a certain textfield on my form into a comma (to prevent access from turning 1.1 into 11 for example).
I have the on dirty event set to me.fieldname = replace(me.fieldname,".",",").
Everything works fine as long as I use the period key on the numpad. However, when I use the regular period key on the keyboard, it does not convert it.
Hi.
Stupid question, but I have Access 97 database with ssn in each record.
I need to create a comma-delimted text file with these ssns.
When I use the export text file with a query that lists each record's ssn, it does not produce comma-delimited file.
How?
Russ
I am building a horse racing based database, two of my tables being Courses (details of each track) and Races (Type of race, where run, prize money etc). In my Courses table I have CourseID (autonumber, key) and CourseName (text, max 25). The longest name in the list is 19 characters.
In my Races table, I have CourseID set up as a lookup, related to CourseName in the Courses table. This works fine if I type the CourseName in manually, or select it from the drop-down list.
My problem arises when I import it via excel, when the whole CourseID column is deleted, as not the correct data type. I have tried formatting the CourseID column in excel as text, general, and even number prior ti importing, all to no avail. How should I format this column in excel?
I need to be able to import data from some excel spread sheet data into some individual fields of a table. Is there a way to do that assuming I have the same field names?
View 4 Replies View RelatedHi,
I would like to link to an external data source (table) from MS Access 2003. I attempt to use the 'Import' feature to do this, and select 'ODBC Connection'. I am trying to connect to an application called 'Action Remedy'. I make the connection fine, but when Access attempts to import the table, it does not like one of the Action Remedy field names, and the Import aborts. The name is something like "Call Status-History.Closed.Time". Obviously, Access does not like this field name. And the chances of getting it changed (on the A/R backend are slim to none). Thus, my question: Is there away around this ?
Thanks !
Hi,
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
Hello,
Not sure I posted to the correct forum, but here goes
I would like to import an excel sheet into a new access table. However I am having problems
with multiple fields.
Lets say for instance that in my excel sheet I have the following fields
Field nameName ExamExam_scoreDate
PurgeMaths607-01-2007
--------------------------
Then I import that into a table. Then that is no problem, but then going back to the excel sheet I have noticed that one study could take many different exams. Hence the below
NameExamExam_scoreDate
PurgeEnglish707-01-2007
------------------------
we have a problem in the table because the fields begin to multiply if Purge has taken 20 exams
fieldname
name
exam
exam_score
date
exam2
exam_score2
exam3
exam_score3
exam4
and so on....can you imagine if I hit 40?
I was thinking maybe I could add some lookup field to a cell in excel or access? Am I on the correct path? Since I want to import, but the multiple fields/columns are getting in the way.
I use TransferText to import some data. However, I am getting errors where the data is greater than $1,000 because it has a comma in it! But the file is comma delimited.
The row of data that causes problems looks like this:
001000002002105000PMTo,528,79,$1.95,"$1,030.95"
They have put quotes around it.
How do you get around this problem?
Thanks,
Dave
How do I send a comma value to a stored procedure?
In Denmark we use comma as decimal separator (whereas US uses dot) and I'm having issues with sending Single values to a stored procedure! If I use dot as a separator Access will simply remove the dot. E.g. 2.5 turns into 25 and 2,5 doesn't get inserted at all (or updated for that matter). If I just run the query from the database window all is fine but not through ADO. Is there anyway to tell Access that this is a Single value and not a delimiter?
Regards, Jon.
I have a text box that has an iif statement in it and a referenct to a numerical value that is suppose to be a currency.
The table that is providing the source for the report show the value as $x,xxx.xx and if a create a text box with just it in the report it shows as $x,xxx.xx but when I put it into the iif statement it shows as $xxxx.xx with no comma.
iif statement example in txtbox:
Code:
=iif([Happy]=true,"I am happy with " & [value] & " in profits","I am sad with " & [value] & " in profits")
[value] is suppose to be like $1,111.11 but instead their is no comma showing $1111.11.
I need to import 300+ individual .txt files into a MS Access DB table (named "tbl_input"). Each of the the .txt files have a naming convention similar to this:
Pink_Floyd_YYYYMMDD.txt
Because this initial import is so large and I would like to avoid doing it manually, I need to be able to:
import each of these files into a single table
delimit the text in each based on the "|" symbol
add the filename into an empty field
I have a table with the numbers formatted as example 9999,99. My mysql database i'm exporting to uses the point as decimal sign. Can I change my query so it outputs the decimal sign as point instaed of a comma?
In my international setting decimal is a comma and should stay a comma.
Thanks
I have this query:
INSERT INTO 1_2_06 ( Issue_ID, Assigned_To, ListType )
SELECT Issue_ID, Assigned_To, 'QPT'
FROM QPTActions
WHERE QPTActions.Entry_Date<=DateValue('1/2/2006') And QPTActions.Close_Date>DateValue('1/2/2006');
The Assigned_To values are a Lastname, Firstname format as they come out of the first database. But I need the comma stripped before they get entered into the INSERT database. Is it possible to do this on the fly?
I have a list box with an onClick that grabs the selection(s) made and pops them into a textbox on another form seperated by a ", ".
I don't want the last selection made to end with the ", "
txtselection = strList
Forms!FormName![txtselection].Requery
Trim ([txtselection]Right(", "))
How do I use the Trim and Instr function to remove a ", " at the end of the string?
I would like to remove the comma, but only one that is at the end of my string(there is a space after the comma, should be also removed).How should I do that in vba?
View 2 Replies View RelatedI have the following piece of code for importing the .csv files from a selected folder and then renaming them into 'imported & filename' if succesful and 'failed & filename' if import failes for whatever reason (bad formating, etc.).
The problem is that the first time it encounters a 'bad' file after another, instead of going again to the Error handler, the command
Code:
DoCmd.TransferText acImportDelim, , "Activitate", filename, True
gives me the default MS Access error, namely 2391. I would like it to go the the Error_handler again ang follow the course of actions and rename the files into 'failed' and 'imported'.
Here's the code:
Sub ImportActivitate()
Dim strFile As String 'Filename's
Dim strFileList() As String ' File Array
Dim intFile As Integer 'Number of files
Dim filename As String, sFullName As String, sFilename As String
DoCmd.SetWarnings False
[code]....
Also, how can I make the routine stop cycling through the files after I renamed them all?
I want to make a database of diseases (need to learn them for school and would like a serchable database on my smart phone for future reference).
Unfortunately spent a lot of time making hundreds of pages of word tables before i realized a database would be better. See attached image or pdf. Is there a way to import the tables to Access?
I need to preserve the hierarchic info in the nested bullet point lists. E.g. under treatment i might have a point called Acute treatment, with sub-levels, Step 1, Step 2, etc. with their own sub-levels. I need to maintain this relational hierarchic info.