I have a database that I imported as a comma deliminated csv file. To keep my original text comas intact I changed all the commas to semicolons before I created the csv file and imported the data. I now have a data base with semicolons in place of the original commas.
I am trying to use the update query function to change all the semicolons back to commas in one of the database fields called File_name
I have a data entry where a record in the File_name field looks like this for example
Chevrolet cavalier; red
and I want to change it so that it looks like
Chevrolet cavalier, red
I run an update query with the criteria set as Like "*;*"
and this finds all the records which have a semicolon in them
I have set the update field to "*,*"
but all this does is to change the record from
Chevrolet cavalier; red
to
*,*
Is the query update the way to go and what is the syntax that I should be using in the Update field just to change the semicolon to a comma and leave the other text intact?
I'm a new Access user and i couldn't figured out how to implement the following:
- I've got a form with buttons on it. These buttons refer to different querries which either displays or changes specific data on the predefined table.
- With a text box (or another thing) I want the user to enter a table name, so that the querries gain portability to work table-independent.
Briefly this is what i want;
-- User should enter a text -- This text should be used to change the TABLENAME in each of the querry
But I don't know how :(
Is there anyone who has time to explain this implementation in detail??
Hello, This may sound too common a question, but I really need help on it. Prior querries in the same do not seem to solve it.
I have two tables, table1 and table2 with the following fields table1 ( ACID, name, address, Salary, MStatus,Age) table2 ( ACID,salary, Age)
ACID is the common field in the two tables ( primary key in table1)
Required
I need to update table1 with the colums in table2 for all the similar ACID in in table1.
Basically, after teh update, table1 will have all the information it had before the update, but a few fields will have changed coz of teh new information coming from table2.
Please help with the code. I'm using access, and the table structures are all the same.
I have these three tables A, B, C and D. IN table A, SSN is the primary key. Tables B, C, and D have other columns, but the also have SSN as one of the columns. Also SSN is NOT required in other tables.
What I need to do is to be able to run an update querry that will update the value of the SSN in table A, and in any other table that will have that SSN.
I have a table in access. The columns are State, FICO, LTV, DTO,LBI and Grade need to update the column (Grade) so that it gets the values A, B, C or D based on the following conditions
If FICO >525 and LTV <100 AND DTI <50 then grade = D IF FICO >600 AND LTV BTWN 90 -100 AND DTI <50 Grade =c If FICO >600 AND LTV <90 AND DTI<45 Grade =B IF FICO >680 AND LTV <90 AND DTI<45 grade =A IF FICO >720 AND LTV <100 AND DTI<50 Grade =A
Please help me figure out the ACCESS select querry for this.
UPDATE DISTINCTROW indicateur set libelle_court='% revues tenues / revues prévues sur projet', description='',cle_tri='',id_priodicite=1,calcule= '0',resitute='0',origine='',seuil_min=9,52,seuil_m ax=1 WHERE id=2;
This gives me an error...I found out that if I put 9,00 (this is the symbolic representation of 9.00 in French system) then it works fine. It does not take any thing after the decimal...
I'm new to access (wiz at excel) and I'm having a problem updating some records. I have table named "Updated 01-22-07". Within that file I'm trying to updated the Shipper_Name field. The file I'm looking to update it from is called "Party City Offical Vendor List 01-22-07".
The common thread in both files is the Vendor #. Where the vendor # is the same I would like the Shipper_Name field in "Updated 01-22-07" to up to the data in the Vendor Name fileld of table "Party City Offical Vendor List of 01-22-07".
I have banged around several queries and can't seem to get it to work. Any thoughts?
I have a form where I want a textbox [txtMaxOrdLimit] to be visible only if another text box on the same form [PaNumber] contains the letter D in the string. This is the code I have on the forms On Current property but I'm missing something because textbox [txtMaxOrdLimit] doesn't show on the form at all.
If Me.PaNumber = "*D" Then Me.txtMaxOrdLimit.Visible = True Else Me.txtMaxOrdLimit.Visible = False End If
I've search the forums...If you can find a thread that discusses this, or can give me some advice on this topic, let me know.
Is there any built-in function that will allow me to delete any text before a certain point in a record... Or perhaps a way to use the REPLACE function to do so?
All of my records look like this (They're ticker symbols): AMZN:Amazon, Inc WMT:Wal-Mart Inc. A:Agilen Tech Inc MSFT:Microsoft Inc.
I just want the company name...Unfortunately, the ticker symbols aren't always the same length, so I can't use the left or right function.
I need to delete the colon and anything to the left of the colon. I've thought of a great use of the replace function for this purpose...I used wildcards...Too bad it didn't work!
Just The Company Name: Replace([Ticker Symbol and Name],"*" & ":","")
I have a form to capture client information. I would like to have the the first character of the name fields (First and Last) be automatically capitalized. How can I set the field to do this?
What can i put on a text box to limit the amount of characters entered like the 'field size' property for a table. I want to limit entry to 40 characters. Thanks in advance for any help,
i would like to make a query in ma access can extract text between first and second character "/" and when there are not "/" in field it returns null.now data in my table are like below
Quote:
No option1 1 100 2 145/Mechanical/0800 3 120/electrical/1620 4 131/mechanical/0200/dw-001
Now I like to make a query can extract text between first and second character "/" like below: No option1 discipline
I'm having trouble converting text to proper text in Access 2010 using Windows 8.1 as OS.
The message I keep getting is:
The expression you entered contains invalid syntax. You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding it in quotation marks.
I am using Access 2007. I recently converted my table field from text to memo. When I run a query and report linked to the qry the memo text is displayed as Chinese character or square box (if field is empty). In some forum posts this is shown as a font bug e.g. with Calibri. I changed from Calibri to Arial as this was reported to correct the bug. It did not work.
My field is set to Grow and Shrink, and table font is Rich Text also as mentioned in other posts. Still not working out.
I have a file that I need to import on a regular basis. There are two different issues with the import:
1. the first digit in the file indicates if it is a record I want to keep. In this case a 1 indicates a repair record and an 8 indicates it is just information such as email, contact info etc.
2. I have a field in the file that may change the import specifications
For example:
If the record is a Non wheel repair then column 167 is a 50 character description If the record is Wheel repair then column 167 is a 28 character description and the remaining 22 characters are broken down into 10 different fields
So I need to do the following
1. Read the first character in the line and determine if it is a number 1 and if not discard it 2. Read a field in column 109 that is two characters long and if it says it is a wheel repair the import will break column 167 -216 down to the appropriate 11 fields and if it is a non wheel repair it will import columns 167 - 216 as one big description field.
I have used the import specifications with Access but it seems this will have to be done in code in a module or something and I am not very good with VB. (only know the basics)
One other issue is I have dates in the file that are 130225 and 1302 (so full date and then Year/Month) if i take out the / date separator in the import spec the full date works but the Year/Month doesn't.
I have a Form Display Data in my Access Database, which is working really well. However, users was asking if there is a way we can make Font Color Could/would change if The text in A field or Any field in my display form contained the word "SAD or MAD". Is there code for such thing in display form?..
Before you start yelling, use the search. I did.;) But the solution mentioned on this forum (place me.lastupdated = Date() after BeforeUpdate event) doesnt work for me. My situation: I have two forms. On the first you are asked for an registrationnumber. This is used to open the second form with that registration number so you can fill in data. I want the date on which things are changed on the second form to be saved. If I use the BeforeUpdate event, I get an error when I try to open the second form. When I use the AfterUpdate event, the date is changed but not stored. Any solutions? :confused:
Firstly, I am very much self-taught on access, so there are several gaping holes in my knowledge, and my database structure is probably not wonderful! I have been learning as I go along, so in the initial stages I have built in several problems which I am now discovering!
My db is designed to track suggestions - i have a table where the idea, progress etc is stored [tblIdeasBank]. This contains an 'originator' (person's name) which is linked to a second table, where the names are stored [tblPeople].
Originally I set up tblPeople with the Primary Key as FullName (e.g. Joe Bloggs) (guaranteed to be unique with the small number of people that would be involved), and a second field called InitialLastName (e.g. J Bloggs). It is this second field that is stored in the tblIdeasBank. Data verification came from the user having to select a name from a drop down box, so there is no actual 'relationship' between the tables. (Didn't realise how useful they were at the time!)
The db has been so successful :confused: that it is now going to be rolled out across the company, which gives me two problems. To populate all the names, I need to import them from Excel, where I will have FirstName and LastName. I have now realised that it would be more sensible to just store these, and create InitialLastName where needed. Also, I may well have duplicate names, so I need to create a unique ID, and a proper relationship. (Yes, I know I should have done that in the first place).
tblIdeasBank: .IdeaID (Primary Key) .Originator (stored as PersonID)
With a One-Many relationship from PersonID to Originator
Any ideas how I would go about doing this and changing the Originator for each idea from InitialLastName to the relevant PersonID number, without corrupting the data?
I have searched through Google and various groups, but cannot find a useful answer, so any pointers gratefully appreciated!
G'day, I'm trying to replace a date on a table using an update query. I have the criteria set to [enter date] but it won't put it in the update to block. I know this has to be one of those easy one but after a search and using help I can't nail it down. Much appriciated, Tim
Guess I should add that I'm going to use a command button on a form to run the query and I want it to prompt me for the date.
So i have a field called [LastUpdate] that i want to have change to the current date to show when the form was last updated. which event do i use to make this happen and should this be a form event or this field event? I tried using the On Data Change even for the form and then just assigning [LastUpdate] = Date, but that didn't do anything. any ideas?
I have a list of codes that need to be changed if it is part of the a list that need to be updated after being entered into the database. I created a function that holds the old values and what they need to be updated to. To get this accomplished on a form do I just need to write a Update query and then reference the query to the appropriate field I am trying to update with new codes for the 11 codes needing to be changed and leaving the other codes the same.
I am trying to update values in an unbound text box based on a selection in the combo box on the same form; however whilst I am easily able to display the information for the first selection the values do not update when I change the value in the drop down.
I'm working on a report that highlights employees when they are leaving on travel and returning on travel and my problem is that the report is only run on the weekdays. It highlights all employees that returned yesterday so for instance on Monday's report it only shows people that returned Sunday and not Friday and Saturday.
My first thought was to make an update query convert those days to Sunday in a new column on my table which would then still cause the employee to be highlighted on monday. I made a table with all Fridays and Saturday's in the year and then in the second column is the Sunday Date to update the new field.
I haven't been able to get the update query to work correctly and was wondering if there would be a much easier solution so a person wouldn't continually extend the weekend table manually.
