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?
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?
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.. http://www.londonairporttaxi.co.uk
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.
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.
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?
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!
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!
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?
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?
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.
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.
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?
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.
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?
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 ?
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:
Code: 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
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));"
Set tdf = db.TableDefs("tblQualityRandTemp") Set fld = tdf.CreateField("RandomNum", dbSingle) tdf.Fields.Append fld
Set rst = db.OpenRecordset("tblQualityRandTemp", dbOpenTable) rst.MoveFirst Do Randomize rst.Edit rst![RandomNum] = Rnd() rst.Update rst.MoveNext Loop Until rst.EOF rst.Close 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
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
I placed the stop above where I'm thinking I need to add code to get rid of the duplicate account numbers.
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?
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.
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. Thanks
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!
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.
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.
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 & "%'" NEXT
SQLString = SQLString & " AND (" SQLString = SQLString & whereClause & ")" Else 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.