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?
Or VBA?
I am trying to isolate a field with quote marks (").
WHERE (((dbo_AC_ECONOMIC.SECTION)="));
but that does not work..
I copy some information from a website and then paste/values into Excel. I noticed that some non-printing characters are copied to. Suppose I were to import this Excel spreadsheet into an Access table. Now I need to clean up/remove those non-printing characters. What is the best way to do this in Access?
I suppose I could set up a calculated column with =SUBSTITUTE([InvHours],char(160),"") as the formula, where InvHours is the field name containing the non-printing characters. But if I did this, then I would still retain the original field with the non-printing characters which would increase the size of the database. I don't think I could delete it because then the calculated column wouldn't work. So I would rather not pursue this option if possible.
Would it be possible to somehow run a macro that could automatically be triggered as soon as data is imported, and that macro would clip out the non-printing characters without having to copy anything to blank columns?
Or does Access have some built-in feature that could do this?
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.
thanks in advance for any help.
I have a main table that is imported weekly from another Access DB which I have no control of. I also have a new table with a notes field and a product ID field. The issue is the product ID field in the main table is constantly growing. When I created a query with all of the fields from the main table and the notes field from the notes table I could not enter any data into the notes field unless the product ID was already listed in the notes table. Is there a way to make a query update the notes table or a macro to add the missing product IDs from the main table to the notes table?
View 3 Replies View RelatedI 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!!!!!
Hi
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?
Thanks
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!
MAIN_IMPORT_TBL
ID
FNAME
LNAME
E-MAIL
PHONE
DOMAIN_TBL
DOMAIN_ID
DOMAIN_NAME
AP
I have a table, which contains many fields, around 90. Out of these 90 Fields, around 40 will be imported from an Excel sheet, which has same headers as the 40 field names in my table, in which they have to get copied.
So when I import data, it copies them as new records, instead what I am looking at is, the records in Excel and in Access table has a Unique Key, so when I import data, matching with this unique key, the fields should be overwritten into those records from Excel, instead of adding new records.
E.g:
Say I have 2 records in my access:
ID(auto generated) Fab_name Issue_ID Location Remarks Data_1 Data_2 Data_3
1 Fab1 193 NY Bug OC DC MC
2 Fab2 641 DRS Error AC KC FC
Now when I am importing data from Excel with the following records:
Fab_name Issue_ID Location Remarks
Fab1 193 NY - GRM Solved
Fab2 641 DRS - SGP Forwarded
So when I import the above data into my Access from Excel, based on Issue ID, it should update the values of different fields that are imported from Excel, leaving other fields (data_1, Data_2, Data_3) as it is.
And I need this to be written as a macro, so that user just has to choose the file, which he has to import.
When I import a column from Excel into a new table in Access using the Docmd.transferspreadsheet function, Access automatically assigns a field name of "F1"; and if I try to import a range of columns (Eg A1:B10), then Access saves them into a table with 2 columns with field names "F1" and "F2". The table is created as part of the Docmd function, and so I don't have control over the field names.
Is this going to always be the case? Can I depend on Access to always use this naming convention? Because I am about to start writing more code that will depend on these field names.
i'm in the process of creating an Access database to import data in text files and then export the data as fixed width text files, this is now working fine
My next step is to be able to setup an "Archive" Table which will hold all the data i have imported across multiple import proceudres for the last 30 days, i then want to be able to "De-Dupe" any files i import against this to ensure i never load duplicate data.
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 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
Nik
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
Hi,
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.
Regards
Justin
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 & "%'"
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.
kwc
Hi
I am after a MS Access Database that will allow me to do up quotes and invoices for my small computer business does anyone know of any free access databases that will allow me to do this
If you know of any can you please let me know via email matthew01@gmail.com
Thanks
Matthew
As a basic user of Access I believe I have identified a use for the program for the purposes of a reporting task which I am charged with at the moment. We undertake the weekly tracking of proposals, which involves in brief, the action required for the bigger proposals, the teams involved in writing the proposals and follow up information as well as registration of wins and losses etc.
At the moment we use Excel but I am finding it extremely labour intensive with entries having to be removed and captured by only one person on a weekly basis and when you are talking about 40 to 50 new proposals registered nationally in a week and about 100 updates on existing jobs it becomes a perpetual case of chasing your tail.
Am I right in thinking that if we were to design a simple, effective database with a form design that all our research directors could use with ease, and we could merely run reports off each week that this would not produce a far quicker reaction time when it comes to pooling international resources but also far more effective reporting system with which to produce our management summaries?
I am aware that this will probably require quite a bit of consultation on design but before I go shouting the merits of Access to any of my superiors I wanted to make sure I could be confident of Access's capabilities first?
Can anyone advise? point me in the right direction?
Many thanks!
Lisa H.:confused:
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
anyone know how to remove the table link (not delete linked table) at run time?
View 1 Replies View RelatedHi Folks,
I'm sorry to be stupid, here, but I can't get this to work. I have a parameter query that forms the basis for a report. To get the value for the query, I have a form on which the user selects the value they want from a combo box (cboProgram). The values in the combo box include all of the programs used in the file plus a value called "All" which means - just include all the records. My problem is that when "All" is selected in the combo box, no records are returned.
Things to mention:
"All" has a value of 1 in the lookup table.
I tried the query with the actual values and it works fine. The problem is when I try to do it from the combo box.
My first attempt was to put:
iif([forms]![frmFormName]![cboPrograms]=1, "*", [forms]![frmFormName]![cboPrograms]) in the criteria for that field in the criteria box for lngPrograms.
Access' response was that the criteria value was too complicated to resolve.
So, I noticed that I wasn't saying 'Like "*"' in the first part, but if that's the problem then I can't get the numbers of quotes right. I also tried switching the iif statement around and saying <>1,[forms]..., else some version of 'Like "*", but that didn't work either.
Then I queried around the forum and found a recommendation to make a function to return the value, so I did the following:
In the criteria for the field in the query:
IfAll([forms]![frmFormName]![cboPrograms])
And in a module:
Public Function IfAll(ProgSel)
Select Case ProgSel
Case 1
IfAll = "Like ""*"""
Case Else
IfAll = ProgSel
End Select
End Function
I'm still not getting it. If "All" is selected, value = 1, then no recorsd are returned. Can anyone help me out please?
Gratefully,
-D
I've been searching all the forums and unable to find my answer. What I want to do is show on a form the average quote for the specific model number.
On the form there is customer info, product info, etc. I want to see the average of what we have quoted every other time we have seen this product (we do repairs). The identifer for the product is the model number, Model#, the quoted cost is PRICE. I've used this function before, DAvg("[Price]","Repairs","[Model#]='013003020'") - which of course gives me the average for one specific model number (013003020). How would I use a similar function but have it do the average for the model number that I am viewing on the form?
If I am able to do that - I also need to find a way to not include nulls and zeros from the Price field.
I am creating an access database to store customer details as well as quote information. I have created a form to input quote details that our office can complete when a customer calls to complete a quote. I need to create a form that opens as a new form every time we click on the form to input a new quotation. How do I do this? At the moment when I click on the form, the form opens but the details of the last quotation are stored on the open form.
I have no training in access at all and am self taught so far so by no means an expert.I also want to create a form that allows you to search for particular quote numbers, so if someone calls and asks about a particular quote number we click on a page that says 'search quotes', input the quote number and the form opens with all the details of that quote.
I know that you can insert comments and notes in VBA code using a singular quote, like the following:
Code:
'Comment about what the code does
Is there a similar way to insert comments in the SQL of queries? I found other forum posts that say this isn't possible but those were all for pre-2010 Access. Has this changed at all?
I try to make my second database in Access. I need to summary 5 numbers, and then minus the lowest and the highest of them. For example (1+2+3+4+5)-1-5=9
In excel it looks like this: =SUM(C2:G2)-MAX(C2:G2)-MIN(C2:G2)
In access (Expression builder) i made this code: [J1]+[J2]+[J3]+[J4]+[J5] - it's the summary of five marks, but how can i erase the highest and the lowest one?
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?
Thanks,
David