Remove Quote Marks From Imported Table

I am using DoCmd.TransferText to import a CSV file to a new table.
The problem is some of the fields contain quotation marks which really messes things up for me later on.
Is there some way I can go through the whole table and remove them?

Maybe a Query?

View Replies


Replace Double Quotations Marks In A Table - Version: 2003 (11.0)

I have some code that pulls information into a temp table, which I then export to a txt file to be used in a mail merge. The data in the table has double quote marks that I want to remove, but I am not sure how to remove them from every field in the table. I had hoped I could just strip them out using the replace function, but do I need to specify each field? I had wanted something like this:

temp_table = Replace("temp_table", """", "")

But I know that isn't right. Anyone with any ideas?


View Replies View Related

Table Layout For Airport Minicab Quote System

ok basically im creating a quote system that will show users prices from one destination to the other destination based on the size of the vehicle..

you can see a similar quote system used on the following website..

there will be three main tables..

Airport Table
airportID - primary key, autonumber
airportNAME - text

the airport table will consist of 5 records..Heathrow,Gatwick,Stansted,London City, Luton

Town Table
TownID - primary key, autonumber
TownNAME - text

the town table will contain a list of postcodes, maybe 50 or so (n1,n2,n3,n4,e1,e2...)

Car Table
CarID - primary key, autonumber
CarNAME - text

the car table will consist of 4 records..Saloon,Estate,MPV,Executive

now what i need to do is create a similar quote system like there is on the url above..

my original idea would be to do something like this..

Have three tables that contain the quotes for each type of car..the first two listed here will work i think..

Airport2TownQuotes Table
quoteID - primary key, autonumber
carID - foreign key from car table
airportID - the airport Pickup, foreign key
townID - the town Destination, foreign key
price - currency

Town2AirportQuotes Table
quoteID - primary key, autonumber
carID - foreign key from car table
townID - the town Pickup, foreign key
airportID - the airport Destination, foreign key
price - currency

however.. for airport to airport quotes.. the resembling table would look something like this..

Airport2AirportQuotes Table
quoteID - primary key, autonumber
carID - foreign key from car table
airportID - the airport Pickup, foreign key
airportID - the airport Destination, foreign key
price - currency

but you cannot place the airport id twice in the same table..(this maybe a case of simply renaming the airport fields in this table such as airport1, and airport2.. but can you still link them and enforce integrity if the fields have different names?)

also one example may be Heathrow to n1 is 30... this does not mean however that n1 to heathrow will be 30.. this is why i need to separate the quotes in this way.. for the 3 possible journeys (airport to airport, town to airport, airport to town).. also the quotes will be different for each car type..

anyway.. i shall be using asp to create a similar quote system on my website.. but i just need to get the final layout of the database sorted.

thanks in advance for any help.

View Replies View Related

Prevent An Apostrophe (or Single Quote) In A Table - Version: 2002 (10.0) XP

I'm trying to add some integrity rules to a form, which is tied to a table.

How can I prevent the user from adding a name that includes an apostrophe. For example, if the person wanted to input a name such as "John O'Donnell," how can I prevent this from happening, since the last name contains an apostrophe?

I tried looking at the Validation Rules in table design, but can't seem to get the right syntax.


View Replies View Related

Imported Table Not Included In Reports!

I have an imported table that I created a form for (containing 400+ entries). I created a subform on it for a new table that I will be entering information to tie in with the imported form.

However, when I try to create a report, the report won't acknowledge any of the information. It's all outlined in the SQL, but nothing appears.

I did discover, though, that if I create a new entry on the imported table, that information appears on the report, but nothing for the existing data that was imported.

Please help!!!!!

View Replies View Related

Excluding Deleted Records From An Imported Table


I have a table that I am importing from FOXPRO DATABASE. When someone goes into the system and deletes a record, Foxpro does not really delete the records but marks it for deletion. There is no field to query on that I can see in the table, but when I go to Foxpro the record is "marked" for deletion.

Anyway to exclude these records when doing a query or importing the table?


View Replies View Related

Ok, I Got A New One. How Can I Set An Imported Table To Hidden? - Version: 2000 (9.0)

I know there must be a way to do this. Here is my current VBA for importing the table.

Dim stdocname As String
stdocname = "qryDeleteAllocationTableData"
stDocName1 = "tblAllocationData$_ImportErrors"
'stDocName2 = "tblAllocationData"
If MsgBox("Get Data from Excel?", vbYesNo, "Confirm") = vbYes Then
DoCmd.OpenQuery stdocname, , acReadOnly
DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel8, "tblAllocationData", Forms!frmprojectinfo!projectfolderpath & " emporary data.xls", 1, , 0
DoCmd.SetWarnings (False)
DoCmd.DeleteObject acTable, stDocName1
'SetAttr stDocName2, vbHidden
End If

Now you can see I commented out the "SetAttr" because it's not working. Can anyone point me in the right direction?


View Replies View Related

Verify Imported Field Based On List In Table

Hi, I'm not sure if this is possible, but I've heard you guys really know your stuff.

I have a client who would like to import a list of e-mail addresses, but would like to verify the domain name used in the provided address against a list of domain names pre-populated in a table. Do I need to use Dlookup? Here are some additional details. Thanks in advance!




View Replies View Related

Order Of Imported Data Into Table - Version: 2002 (10.0) XP

I have a module where I am running a delete query to remove all records from a table. I then do a transfertext to import a text file that contains a header record and then several detail records that follow that header. Additional headers and details repeat several times. I have noticed that the data is not always coming into the table in the same order as the text file. This is a problem since each detail line relies on the proceeding header. I am not using autonumber or any primary key since the table is basically one 100 character text field for each record. Any thoughts on why this is happening?? Thank you!

View Replies View Related

Record Is Delete As A Table Being Imported... - Version: 2000 (9.0)

My primary question starts with a "HUH?!"

For the past year, I have no problem in importing the table using this EventProc:

DoCmd.TransferDatabase acImport, "Microsoft Access","C:MyTracking.mdb", acTable, "Grades", "Grades", False

Now, after the records start to get to the high 7k, the error message becomes:

"Record is deleted"

This happens whether or not I import the table manually, using a query to import over as a table, and I even try to import the table's definition only, then use an Append Query to bump the info over...same thing. "Record is deleted". Could any share some light on this subject?

View Replies View Related

Lookup Table And Imported Data - Version: 2003 (11.0)

I have a table that contains 900+ records that are related to each other on a row basis. The Dept field needs to be able to determine which of the other account numbers is being used.

- Once a particular department is selected I need to have the corresponding numbers fill a form based on the department that is being used.

- The other aspects of the form will be imported from an external source that my company is using to do gather the payroll information. Also is there any easy way to import the necessary data each month from an internet application that is being used to calculate the payroll amounts of the company and sub companies.

Based on the data that is imported I need to be able to have dept number from the imported data dictate which account should be used. Is there a way to accomplish this without having to go into each record and set the account numbers based on the department that is pulled from an external data source?

View Replies View Related

Removing Blank Rows From An Imported Table? - Version: 2002 (10.0) XP

This is a problem I've tackled for a while and as I redesign my database, I've approached it again.

As part of my daily database tasks, the database will import an excel spreadsheet sent to us from a client.

My system will then build an SQL append query based on the client, and append that data into my master table.


The only problem is, often our clients will send us 'dirty' excel sheets. No, not porn, but files that often have blank rows. This I suspect is due to them reusing previous files as templates.

Regardless, my task is to remove these blank lines prior to the big append, otherwise, how am I to append correctly. I don't want blank rows in my master table, that would be a disaster.

What I need to know is, is there a script, ADODB routine, anything out there that will scan a table and kill any row that has no data in it. This would be ideal for the problem. I fear such a tool doesn't exist, but I hope it does.

I have other alternatives, I can configure a field to be the 'master' field for the client and delete any rows where this field is empty, I can do that with ADODB, but if I can avoid having to introduce that extra bit of configuration, I would like to. I want to be ruthless. If a row has no data, why do I need it? I don't. So I'd like to delete it there and then.

Any suggestions would be great. I appreciate this has probably been asked several times before. If so, I apologise. My DB is an Access Project linked to SQL 2000. Thank you Happy July 4th.

View Replies View Related

Imported Spreadsheet Has Many Blank Rows In Access Table - Version: 2002 (10.0) XP

I've been importing a spreadsheet into a temporary 'holding' table for manipulation of the data prior to committing this data to the main table.

I've noticed that the holding table contains a large number of blank rows. There is no data visible outside of the expected rows in the spreadsheet. In the spreadsheet, I've tried deleting rows below the last row and columns beyond the last column to no avail. Note, that the number of rows varies each time the spreadsheet is imported so it is difficult to specify a range for the transfer spreadsheet command.

So, I've decided to just delete the rows from the holding table in code. The problem is that I'm doing it by selecting rows where the "primary key" is either blank or Null. Although I ask the users to set the spreadsheet up correctly when importing to the holding table, this table is created on the fly with no conditions. And who knows whether users will follow the directions. This means that they can set any column name they want in the spreadsheet. But when I select, I have to actually use a field name.

My question is two fold.

1) How can I refer to the first field (column?) of the holding table in an SQL statement without actually using the field name. I think I can use the Table Fields property, but I'm not sure of the syntax.

2) Is there a way to delete blank rows from a table without selecting based on a table field? I doubt it, but I though I would ask. So delete blank rows from table with no criteria for any fields.


View Replies View Related

Append ID Field To Imported Table Field - Version: 2003 (11.0)

I have a table called "teams" with an autonumber Primary Key that matches a field named "tm".
I have a table called "data" with a field named "tm" and a field named "tblteams_tm".
I want to append the Primary Key into the table named "data" within the corresponding row that matches the "tm".

When I run an append query, all that happens is it adds the data I wanted but into new records/rows.

How do I get the Primary Key ID numbers to append into the correspoinding record?

thanks in advance for help offered.

View Replies View Related

Remove Ticks From A Table

i have a table that contains a column of Check boxes. in one of the forms i've made i need to create a button that removes the ticks from all the boxes in that column. does any one know a method that will do this.


if it isn't broken don't fix it

View Replies View Related

How To Remove The Table Link?

anyone know how to remove the table link (not delete linked table) at run time?

View Replies View Related

Need To Remove Spaces From Field In Table

I have a table that for some reason when I imported the data, placed some spaces before about 75% of the numbers in only one column. I need these numbers to be exactly the same as in another table because I use this number to compare to records and import other data depending on the corresponding numbers.

I tried doing a find and replace, but for some reason it doesn't find the spaces to replace. If I type in the space and the number, then in the replace with type only the number, it works. But I cannot do that since there are literally 10's of thousands of different numbers.

Someone mentioned a "LTRIM" command. Does this work within Access and if so, how does it work?


View Replies View Related

How Do I Remove Duplicate Entries From A Table ?

Hi all,

I have imported a table from Excel. The table contains a long list of names and there are quite a lot of duplicates.
How do I remove the duplicates so I can import the clean list into a value list in Access ?

Thanks in advance !

View Replies View Related

How To Remove The Table Link? - Version: 97 (8.0)

anyone know how to remove the table link (not delete linked table) at run time?

View Replies View Related

Make Table To CSV, Remove Text Qualifier

Thanks for looking!

I'm using a query to export a table to a text file, i.e.

SELECT * INTO [Text;HDR=No;Database=C:].[test#txt]
FROM [TestTbl]

Text fields end up surrounded by double quotes as a text qualifier. Is there a property I can use to get rid of the text qualifier within the SQL (similar to how HDR=No gets rid of the headers)?

I've tried searching and guessing, but nothing has worked yet. Thanks!

View Replies View Related

Remove Duplicate Values Within Table - Version: 97 (8.0)

I have a database which is used to score productivity and quality.

The users input the account number they worked and the action they took (via check box). It is quite possible that the user will work the same account several times in the same month.

The database is presently set up so that at the end of the month, the supervisor just enters in the user they want to check and the date span. The database then presents them a list of 20 randomly selected accounts. Since user's may work the same account several times in a month, an account can randomly selected more then one. We wish to eliminate this.

Presently, here is the coding as it stands right now:

Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Dim rst As Recordset
Dim TempSQL As String
Dim strSQL As String
Dim strTableName As String
Dim where As Variant
Dim QD As QueryDef

Set db = CurrentDb()

On Error Resume Next

db.QueryDefs.Delete "ProductivitySummary"

On Error GoTo 0

where = Null
where = where & " AND [TechID] = " + Me![TechID] + ""

If Not IsNull(Me![EndDate]) Then
where = where & " AND [Date] between #" + _
Me![StartDate] + "# AND #" & Me.[EndDate] & "#"
End If

Set QD = db.CreateQueryDef("ProductivitySummary", "Select * from qryProductivityMaster" & _
(" where" + Mid(where, 6) & ";"))

TempSQL = "SELECT ProductivitySummary.*" & _
" INTO tblQualityRandTemp" & _
" FROM ProductivitySummary" & _
" WHERE (((ProductivitySummary.Rejected)=False));"

db.Execute TempSQL


Set tdf = db.TableDefs("tblQualityRandTemp")
Set fld = tdf.CreateField("RandomNum", dbSingle)
tdf.Fields.Append fld

Set rst = db.OpenRecordset("tblQualityRandTemp", dbOpenTable)
rst![RandomNum] = Rnd()
Loop Until rst.EOF
Set rst = Nothing

strTableName = "tblQualityReview"
strSQL = "SELECT TOP 20 tblQualityRandTemp.ProductivityID, tblQualityRandTemp.CPIAcctNum" & _
" INTO " & strTableName & "" & _
" FROM tblQualityRandTemp" & _
" ORDER BY tblQualityRandTemp.RandomNum;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

db.TableDefs.Delete ("tblQualityRandTemp")

Set tdf = db.TableDefs("tblQualityReview")
Set fld = tdf.CreateField("Contact", dbBoolean)
tdf.Fields.Append fld
Set fld = tdf.CreateField("Action", dbBoolean)
tdf.Fields.Append fld
Set fld = tdf.CreateField("Phone", dbBoolean)
tdf.Fields.Append fld
Set fld = tdf.CreateField("Content", dbBoolean)
tdf.Fields.Append fld
Set fld = tdf.CreateField("NextAction", dbBoolean)
tdf.Fields.Append fld

DoCmd.OpenForm "sfrmQualityControl"

I placed the stop above where I'm thinking I need to add code to get rid of the duplicate account numbers.

Any suggestions?

View Replies View Related

Remove Modal Popups In Make-table Query

I need to be able to run a number of queries where some of the queries are make-table queries. What I don't want is to have is all the messages that come up during the creation of the table ie deleting existing files and showing the number of records created to place in the file. Is there a way to block these messages?

View Replies View Related

How To Remove Repeated Records In The Table - Version: 2003 (11.0)

I have created a table with rows of repeated records which I would like to remove. How can I do that in Access?

Example of the records:


Thanks in advance for any help!


View Replies View Related

Removing Quotation Marks ....


This is probably real easy to do, but ........ I have a field in Access that looks like this:

"12:12:01 PM 12/5/2005, 5:00:01 AM 12/6/2005, 7:00:25 AM 12/6/2005"

From this string, all I want is the last Date entry (mm/dd/yyyy). Thus in this result, it would be 12/6/2005. I know how to use the Right(), Left() and Mid() functions, but I do not want quotation marks included in my result.

Any help would be great ......

View Replies View Related

SQL Statement Quotation Marks

I have a statement which is driving me nuts...i'm a beginner with using SQL:

Private Sub Command45_Click()

Me.Graph0.RowSource = "SELECT (Format([Date],""" """mmm""" ''yy')) AS Expr1 FROM Chart_Unsafe_Daily GROUP BY (Format([Date],'mmm'' '''yy")), (Year([Date])*12+Month([Date])-1);

End Sub

The statement is RED for an error, i'm sure it is because the quotation marks within the statement are incorrect.

Can anybody help me out?

View Replies View Related

Quote Generator Help

I've been asked if I can create a database which will act as a quote generator.
There will be 5 components to make up the whole product. However, the options available for components 3, 4 and 5 will be dependant on the components chosen for 1 and 2.
i.e. there may be 5 options for each component.
If part 1a is chosen, then only parts 2c, 2d and 2e will be available. If part 1a and 2c are chosen, then only parts 3a and 3d are available and 4b, 4d and 4e are available.
...does any of this make sense?????

My question is... can anyone tell me how I should create the tables and the relationships to enable me to create a database like this. I am completely stumped.
It may be that this is completley beyond me, but i'd like to at least be able to give it a try.
Any help would be appreciated.

View Replies View Related

Quote Form

I have been trying to create a Quote form in my Database. I have a list of products, each product then has a table that details prices based on quantity breaks (i.e 500 to 999 and 1000 to 1999 etc.).

In my Form I am unable to create a subform in datasheet view that will enable me to select a product from a combo, type in a quantity and for the price to be displayed based on the quantity?

Has anyone managed to achieve this? I would love to see the code and put it into practice.

Many thanks


View Replies View Related

Remove Duplicate Entries From Access Table - Version: 2003 (11.0)

I have an Access 2003 table with about a million records, of which many are duplicates.

I would like to filter the table based on 2 fields ('Street' and 'PAON') so that i'm left with only unique records.

Can anyone please tell me how i can do this?

Many thanks

View Replies View Related

Accent Marks & Foreign Characters


I'm having an issue with foreign language characters (that have accent marks) not being accepted in our database input fields when a user tries to enter them into the textbox. i've enabled foreign languages, checked help menus, used the character map in accessories, and even tried using the alt+# method but to no avail.

since our company works with many foreign countries, many of the firms names have all sorts of crazy accents ( ǐ ) and as you can see from that example, one already showed up incorrectly as a box instead of an "i" with the "u" looking accent above it. I've attached a screenshot to this post so you can see what i'm talking about, however in the example it's the "s" with the "u" looking accent that doesn't show in access, as opposed to the "i" replaced by a box in this post.

My question is, is there any way of adjusting our system so as to make sure all accents are allowed? If you also have a list of steps that I can follow, I can try to redo it your way since the ones I've tried have not worked.

Any help would be greatly apprecaited. Thanks in advance!

View Replies View Related

Quotation Marks On A Report Field

Hello I am trying to design a CD label 'report', it's going well except that the title field of our productions has to print with quotation marks around it'. The name of the field is, yes you guessed it, TITLE. I know it must be a simple solution but I'm at a loss to find it or understand it. I'm just a dumbo. Thanks for your time and expertise.

View Replies View Related

How To Eliminate A Double Quote

I have a string with one or more double quotes in it. How can I remove the double quotes? (I was able to remove single quotes.)

Example: Purchase "other items". (This the data in a column.)

I want to change it to Purchase other items

View Replies View Related

Single Quote Glitch


When trying to update an access membership db where expired members are
being set to 'inactive' the script keeps hanging up with a runtime error
when the first or last name of the member
has a single quote in their name (eg O'Neil)

Any ideas where to look to fix this would be appreciated.


View Replies View Related

Single Quote Dilemma

i have an error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Products.Catalogs LIKE '%[a-z]%' AND (Products.ItemName LIKE '%t'ai%') AND ((Products.NewItem = True) OR (Products.HotBuy = True)) ORDER BY Products.ItemName'.
/webdev/flaghouse/NEWITEM_List_Main.asp, line 149

this results when search terms contain a ' (SINGLE QUOTE). i've tried to double up all of the SINGLE QUOTES in the code and still get an error.

heres the CODE:

' check if keywords entered, split comma-delimited list into array of keywords

If Request("Keyword") > " " Then
theKeyword = Request("Keyword")
listKeywords = split(theKeyword,",")
maxCounter = ubound(listKeywords)
whereClause = "Products.ItemName LIKE '%" & listKeywords(0) & "%'"
FOR counter=1 TO maxCounter
theKeyword = listKeywords(counter)
whereClause = whereClause & " OR Products.ItemName LIKE '%" & theKeyword & "%'"

SQLString = SQLString & " AND ("
SQLString = SQLString & whereClause & ")"
theKeyword = "None"
End If

if anyone sees anything amiss, please let me know. i'm at wit's end!!

thanks. please email me at: URL. again, thank you in advance for your help.


View Replies View Related

Copyrights 2005-15, All rights reserved