Hi all, couple of quick questions that the newbie cant figure out.
1. How can I make a new column in an existing table and then fill it with the same value, at the moment I have to copy and paste every record?
2. How can I convert the contents of a column to lowercase other than doing a find and replace for every letter of the alphabet?
Any help will save me hours of time and help prevent my chronic copy and paste cramp.
Thomas
Now,
I have a big table with sales data downloaded from DB2.
We have branches codes of 1 digit only.
Because of too many branches we have branch codes in upercase and lowercase. So Q ans q are different branches.
I'd like to substitute all lowercase codes like q with something like q_ to make them searchable.
How I can substitute all lowercase letters in one column of the large table in one shot?
Or may be you know any solution how to make Access queries sensitive to the case.
I've been stumped on this and could use some help...
Fields in my table contains some words that are in uppercase and some in lowercase. I would like to delete all the lowercase letters and keep only the uppercase ones.
For example, if a field contains the following text:
CHLOROSULFONIC ACID with or without sulfur trioxide
I would like to change this to:
CHLOROSULFONIC ACID
Can this be done via an automated process?
Thanks!
Is it possible to search a field for a Lowercase letter.All data should be capital, but occasionally a user may ann a record in lowercase, and was looking for a query to pull back all records in lowercase
View 2 Replies View RelatedI'm looking for a way to have a text box auto fill based on the selection of a combo box on the same form. I cannot use the method i find all over the internet of using multiple columns in the combo box and basing the text box on that because the combo box already has multiple columns being used to determine its own possibilities and other combo box possibilities.
I would really just like the text box to work like this, but im still kinda inexperienced in VBA...
If combo box is "F004-001", then text box is "237"
If combo box is "F004-003", then text box is "280"
I know how to add in an "after update" thing, but i do not know how to do If/then statements.
I was able to use the UNION ALL qry. But, when I have another file (like original2) that does NOT have all the columns listed in the UNION ALL qry, I get a Parameter value box asking for the missing columns when I run the qry.
Example:
original1IDDateGroupChristianJohnnySteve 18/5/2013A1528/5/2013B338/5/2013C2348/5/2013D2358/5/2013E5
original 2IDDateGroupChristianJohnny18/6/2013A212528/6/2013B2338/6/2013C2248/6/2013D22
The UNION ALL qry includes all the possible resources ( includes all the possible column fields Christan, Johnny, and Steve).
When I run the UNION ALL qry with the original2 file, An "Enter Parameter Value" box is displayed with the mssing column name "Steve".
Is there a way to Map the original2 table into a working table with all the columns, or use VBA code to construct the UNION ALL qry to only include the existing columns? My data has variable columns and I'm trying to avoid the parameter popups.
I have a MS ACCESS 2010 database with a data table which i am trying to create a query from. I have 6 columns of data( one with an ID Field and 5 Name Fields). Below i have made examples of how it first appears as a simple query and the second will show you what i would like it to look like.
What the simple query looks like: [URL] ...
Second what I want the query to look like: [URL] ....
Currently I'm building tables and forms. My first table (called Clients) lists the details of fictional clients. My second table is for invoices.
In my invoices table, I wish to link the column for client reference (note: stored in the Clients table) to the column that precedes it. This column will list the clients’ names and is selected from a drop down list that is linked to the Clients table.
What I want to do (if its possible) is to have the respective client ref. automatically show up in the next cell once I've selected the client to whom the invoice relates?
Am I making sense? Is that possible? If so, how do I do it?
Secondly, how do I do a sum of selected columns for my “totals” column? Basically, I want to add the figures found in several cells that precede it?
I'm affraid my confusing topic title is an indicator of how confused I am by this. I can't even understand the variables well enough to fully utilize Access Help or the Search function here...
What I have is a database hat has column headers that look something like this:
Customer_Name, Order_Date, Qty_Ord, Unit_Price, Total_Price
What I'm trying to get is a query output that will have
Customer_Name, Total Orders (in Dollars) for January, Total Orders (in Dollars) for February, Total Orders (in Dollars) for March, etc.
I've been able to set it up to SUM for one month, but not multiples.
I know I'm totally lame (for proof read any of my previous posts) but you guys totally bailed me out the other time I asked a lame question.
Thanks in advance!
Hi,
I got a Date field[Provdate-ontv] which only is allowed to set if another field, a Yes/No field[Prov-ontv] is set to Yes (default is no)
How do I do that ??
I've tried googling this, but I can't find anything helpful. I have five columns in my 'Main' table. In each row, column B, C & D are solely dependant on column A. And column E is completely independant of all of them. I want to be able to select a choice in column A using a drop-down menu (which I know how to do) and have columns B, C, & D automatically fill themselves in (which I don't know how to do). Column E I would just manually enter.
How do I get columns B, C, & D to automatically fill themselves in my 'Main' table. I would select a value from a drop-down menu in column A and want everything else to fill in. The values for A,B,C,& D are all in seperate tables with an ID and linked to their respective fields in the 'Main' table. Appreciate any help.
Hi
Does anyone know how I can auto fill all the records of a field by just entering it once
Thanks
I am looking to find the experssion that will allow me to put a date in a query under date completed and have it fill another field for schedule.
I know that in Excel it would be =sum(A1 +90 ) and this will give a date 90 days after the date inputed into field A1 and so on down the column. and it would auto fill the next column for me
Hello,
I'm new to access and i need help in filling a certain field with the same value.
In fact i have data representing one quarter of data and i need to add a column called "Quarter" in which i paste the same value: the current quarter : "Q4".
How can i do that?
I think this is a real simple issue.
Thanks in advance for your help.
Regards
sam
I have a table with three columns
Column A, Column B, Column C
in these 3 columns i have different values and in two fo these columns there are blanks. I want to search for this blanks and fill them in with a static value. But only the two columns.
How can i do this? Update Queries?
Thanks you
all I have this code that opens a table and fills in a blank field from the previous record.
Private Sub CopyField_click()
'Fill blank fields with previous record
Dim i As String
Set myDb = CurrentDb()
Set MyRs = myDb.OpenRecordset("ACHClosedAccts")
MyRs.MoveFirst
Do While Not MyRs.EOF
MyRs.Edit
For Each myfld In MyRs.Fields
If myfld.Name = "ClientNo" Then
If MyRs(myfld.Name).Value <> "" Then
i = MyRs(myfld.Name).Value
Else
MyRs(myfld.Name).Value = i
MyRs.Update
End If
End If
Next myfld
MyRs.MoveNext
Loop
End Sub
I need to use this code with multiple field names Where
myfld.name = "ClientNo"
I need it to also reference another field to do the same thing. Like:
myfld.name = "ClientNo" and "AcctNo"
It won't work with "And" or "Or". What can I do? I thought about an array but I don't know how to rewrite the code unsing an array.
Please help Thanks
In using a form to input new information, I want to utilize the autofill feature, but I don't know how.
For example. When someone inputs a month, I want the season to be automatically entered in, in the following entry.
Can someone please give me some direction or help me to do this!?
:D Thanks!
Hi
I have created a form with one unbound textbox and a command button.
How do I make sure that the query only runs if text is entered in the textbox?
I do not want the command button to run unless the textbox has data to query.
At the moment the Query runs and displays all records.
Hi
Newbie to your forum and a bit of a novice in access too.
I am building a db and a form that was previously able to be filled. When I altered the form and added some fields from a different table now just dings when I try to type in any of the old fields, but I can enter new data into the new field from the second table any ideas whats gone wrong.
I have searched through the properties and compared the new to old fields and can't really find any discrepency between the two.
Would it help if I posted the mdf file? (its only tiny)
Cheers
Mike
I have an access database with information that is posted to through a form. On the form I have a dropdown box with a list. After choosing an options I want a text box to auto fill from data in a separate table in the same access file. I have been trying different things for about a week, and I have done alot of reading.
View 2 Replies View RelatedI am trying to convert a table that looks like the following...
Customer Name SumofSum of Bill Rate Reviewer
000462 John 500 Mike
000224 Mike 900 Jeff
I would like to covert it to....
Customer 000462 000224
Name John Bill
SumOfSum.. 500 900
Reviewer Mike Jeff
I don't think Access has any functions for this but I am not 100% sure. I know this can be done in a Module but I am not sure of how to code it.
I have a subform and when the controls on it are updated, changed from previous info, then I need a control(called dtmUpdate) to show the current date (=Date()).
How would I go about doing this?
Thank you
I am trying to auto fill feilds when I select from a combo box. I have tried to use help, but I can't get it right.
View 1 Replies View RelatedHello,
I need to be able to take information from a table in access, use this information to populate a PDF file, and then save the PDF file.
i.e. I have leads in a database and I want to pre-fill in application forms (PDF) for the clients.
I did some searching pertaining PDF, but didn't find exactly what I was looking for.
Where should I start?
I have an order form that requires me to change the current promotion we're running continually. I need to populate all rows based on the update changes of one textbox. Is this possible? Right now when I update the promotion text box, it only updates the text for row one.
View 1 Replies View RelatedHello,
I have a very complex issue.
First of all there is a table called "tblParTeam" wich contain the teamname and ID of teams partissepating in a certain Tournament. The tournemant data is kept in the table tblTournament.
Each tournament contains a couple of variables: "Number of teams per tournament", "Number of Rounds" wich reside in the tblTournament.
Here is the thing. I also have a form based on the tblTournament. Each tournament had a number of partisipating teams (stored in the variable "Number of teams per tournament"). Then I have a table called tblTournamentDetail wich containt the Tournament_ID, TeamPro_ID (looks in the table "tblParTeam"), TeamContra (also looks in "tblParTeam").
When I pusch a button on my form, wich is based on the tblTournament, tblParTeams and tblTournamentDetail, I would like the X (Number) of particepating teams to be filled in randomly in the tblTournamentDetail.
Lets say I have 10 teams I want 5 to be filled in randomly in the TeamPro_ID and 5 in the TeamContra_ID.
How do I go about this?
Thanks in advance.