Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?
Hi, I have secured my db with password. I want to know the technique/technology so that i can read my password. Access must be storing the password in an encrypted form in any password file. My idea is that if I know the file where its stored and in what form then I hope it can be retrieved. I find so many available sites who claims that they have their product which is able to retrieve access db passowrd. I want to learn on how to do the same. Any idea or suggestions is highly appreciated. Thanks.
I create the table1 in SQL Server and then link the table to MS Access using ODBC. But, when I write do this statement Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset("Select * from table1")
for i = 1 to rs.recordcount msgbox rs.field(0) next i
It only retrieve 1 record only, but the table have 10 records. What's wrong with it? I check rs.recordcount, it only loop one time.
Additionally, it doesn't work this function in the ODBC link table rs.addnew rs.field(0) = "hello" rs.update
And, when we use ODBC link tables, we cannot delete data in the table. currentdb.execute "delete * from table1"
it will give out error message, the table is read-only.
Suppose I have sharing drive, let's say "\server1sharefolderDatabasesdb1.mdb"
if I run this CurrentProject.Path inside the database file, it will retreive the current path with drive letter, but how can I retreive the server, and sharefolder information?
I am able to get the user name of the computer using a function. The function returns the user name in this format: first Name.last name. I wish to display only the first name ( the name before the dot. ) in a text box. I think I can use the len function. How to retrieve only the first name. I also wish to capitalize the first letter.
i want to retrieve some data from a table i have pieced together a bit of code but get an error to few parameters.
Code:
Dim db As dao.Database Dim Lrs As dao.Recordset Dim LSQL As String Dim Lname As String 'Open connection to current Access database Set db = CurrentDb()
I'm trying to build a form based on a query to use for data editing/entry purposes.
The only way I can think of to do this is to use a query to get data that is particular to my specified customer.
The first problem I have run into is that one query does not contain enough fields to retreive all the fields in my table.
I figure, perhaps I have made a flaw in my design and shouldn't go further with this until I verify it to be true. I don't think I've made a flaw, but the fact that a query has limited maximum number of fields leads me to think I might have.
Each record in the table I'm querying contains several pieces of numerical data that might be recorded during a service visit.
Obviously I could query the table twice but that doesn't feel like the right way to go about it.
i have got a database which consists of table, queries, forms and reports. I have one problem now is that I wan to do a query that will be able to bring the figure from another field to the current one. ok say i have got Ref_No, Event_Date, Balance_BF, Adjustment, Balance.
so if say this is the first event for this particular ref_no (1234) it will bring the amount from another table (Principle_Table) linking the same ref_no (1234) to the Balance_BF fields, If this is not the first event for this particular ref_no (1234) it will then bring the amount from the previous event Balance field to the current event Balance_BF field.
I am very lost how to do this. Is there a way doing this using query?
This is wat i mean Ref No Event Date Balance_BFAdjustment Balance 06/00014 31-May-06 115140-5,757.00 $109,383.00 06/00014 31-Aug-06 109383-5,757.00 $103,626.00 06/00014 30-Nov-06 103626-5,757.00 $97,869.00 06/00014 28-Feb-07 97869-5,757.00 $92,112.00
U can see that the Balance_BF is actually brought down figure from the previous Balance starting from the second events. For the first event, the Balance_BF is actually draw down from another table (Principles_Table)
Can someone please tell me what I'm doing wrong here. I have just added a record to a table. I would now like to get the record number (FamilyID), which as an AutoNumber field, and pass it to another form. My query is giving me an error that says, "A RunSQL action requires an argument consisting of an SQL statement."
Here is the code snippet: DoCmd.RunSQL "SELECT FamilyID FROM Families WHERE LastName = '" & _ txtLast.Value & "';"
Thanks, Lisa
P.S. No one ever replies to my posts. Please respond if you can help. :)
I have a query and using a [text string] as criteria in the Design Window - when the query runs, entering the appropriate search string retrieves the appropriate results. I have created a form using this query as the recordsource - and as such the user is asked for the string before the form loads and the results are displayed.
Is it possible to retrieve the string the user entered and display this value on the form?
Hello, Please tell me if there is a way to retrieve a value from a field in the previous form record in a query that don't have ID (autonumber), using data/time ascending.The function PrevRecVal() from QrySampl can only be used for query that include ID. Thanks
Searched birthday in threads and couldn't figure this out.
I have a select query and I am trying to retrieve a birthday that is between two dates.
This is how I set up my expressions:
chkBirthDay: Format([BirthDay],"m/d")
Criteria for chkBirthDay Between Format([LaneDate]-3,"m/d") And Format([LaneDate]+3,"m/d"
So if LaneDate = 9/7/2005 I should be able to retrieve all of the Birthdays that fall within 9/4 and 9/10. Correct?
I do retrieve records but they are dates such as 9/17/1921 and 9/18/1948 but of course they do not satisfy the criteria. There is a date in the table that does, 9/7/1981, but that record does not show up?
I have 2 tables, one called [Addresses] and the other called [2006_Contacts]. The table [Addresses] has a number of various fields relating to contact data of people. The [2006_Contact] table has 2 fields [Last Name] and [First Name]. I want to run a query that pulls the contact data from the main [Addresses] table, but only if the contact name ([First Name] and [Last Name]) are present in the table [2006_Contacts].
I have been working on a normalized db for the past few months and it works wonderfully. But now I need to find a way to create a form so that the rest of the company can update their data.
My Table has the 5 following fields. Also listed is an example of the types of data I have there
As you can see I have multiple StoreID's for one store, IN00001. My db goes up to IN00419 so it ends up displaying 29,664 records. There are 4 different categories (Japanese, Chinese, Local, and Other). Until now everything has been taken from a datasheet and put into an Update Query but now that other people have to quickly access the system I need a slick interface.
What I need is a way to have a form that can update the table and add new entries that looks like this in the form:
IN00001____2005________Jul__Aug__Sep__Nov__Dec__EC T. Japanese MixPercentage__.01___.02__.01___.03__.01 Chinese Mix Percentage___.35___.25__.26___.33__.30 Local Mix Percentage_____.60___.70__.69___.62__.65 Other Mix Percentage_____.04___.03__.04___.02__.04
IN00001____2006________Jul__Aug__Sep__Nov__Dec__EC T. Japanese MixPercentage__.01___.02__.01___.03__.01 Chinese Mix Percentage___.35___.25__.26___.33__.30 Local Mix Percentage_____.60___.70__.69___.62__.65 Other Mix Percentage_____.04___.03__.04___.02__.04
I hope this gives you a clearer picture. I need to be able to update 96 entries at a time (12months, 4 customer types, 2 years) but do it in an efficient manner. The table only has 5 fields so I need to figure out how to display multiple entries on the same form. I also need a way to add new entries. In other words for me to add another StoreID such as IN00420 then I would have to enter in a value for every field for 96 records in the table. When in reality I should only need to update the mix percentages since the months and years remain constant. HELP!!
Any help would be much appreciated. Due to the variable nature of the db I cannot establish very many one-to-one relationships. So therein lies another problem.
a form should open when a record in a listbox is double clicked.. the form doesnt open.. and when i open it independantly i am unable to edit any of the fields.the form is based on a multi table query.if anyone can suggest why it doesnt work i would be most grateful.2003 version on my webspace cos it didnt fit by 100k or something..
how can I retreive the value (and not the index order with listIndex) from a list box? I have a list-box based on a parameter query and I want to retreive only the selected record (in the list box) data from that query.
Hello, Im using a ASP and MS Access 2000 database. What im trying to do is: I insert a row into a table, in that table is a field that auto increments. after I insert this row I want to immediately after that be able to retrieve the value of the column containing the auto increment and hold it in a variable so i can use it for other things....
can anyone help me with this? I have been messing around with SQl cursors but cant seem to get it to work right..
Thank you very much -Justin B. Edit/Delete Message
using access 2010. I have a table where I need to pull out the highest value. But sometimes; there are two or more with the same high value. I need a way to retreive the first highest value. I've done this before a while ago and I can't make it work again. I started off with grouping highest value but are getting all three same values.
I have a query with the following criteria in one of the fields:
>=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom())
fom is a function for first of the current month. I need this query to be specific to what month it is when its ran so i want to only have this criteria if the month is > = october. If it isnt October or greater, i want the criteria to reflect this.
>=DateAdd("m",-12,fom()) And <=fom()
Which also works by itself. But when i add it to an iif statement it always produces no results. Below is the iif statement.
Iif(month(date())>=10, >=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom()),>=DateAdd("m",-12,fom()) And <=fom())
I have also added the column name to each expression and it still doesnt produce any results.
I am creating a multi-search form for a student database, where after I enter my search criteria I hit a "Run Query" command button and then it opens a query form with all of my criteria.So far I can search using last name, first name, and middle name. When I try to search with a start date and end date I am have issues.The start date and end date is for the class date. In the query form under the field, class date, for criteria I wrote:
Between IIf([Forms]![Search Form]![Start Date]="",1/1/10,[Forms]![Search Form]![Start Date]) And IIf([Forms]![Search Form]![End Date]="",4/25/15,[Forms]![Search Form]![End Date])
I want it when I write a date in the start date and end date I want it to give me a list of all the students who took the course between those dates. Also, if I leave the dates blank I want it to search all dates. The dates 1/1/10 and 4/25/15 are just the dates I gave because that is far back as my database goes.
Month and Days are pulled from a table called Calendar. How do I get PreviousMonth to display the Month from the previous record?
I was looking at a ranking formula, and it seems like it compares the field to the previous one, but I am not sure how to apply it to my situation. The ranking formula I was looking at:
(Select Count(*) from Data Where [ProductA1] > [Data1].[ProductA1];)+1
