Hi,
Im getting an odd result returned & I don't know why.
expression:=[lookup]*[yards]+[drop]:=0.296*30+-11.838
Result:=1.20E-27
If I replace value [drop] with -11.838 then the result is correct (-2.958)
ie:lookup]*[yards]+-11.838
The porblem seem to be when [drop] is neg.
I am new to MS Access, when I am making any calculation using some expression, then I have to save the result to table, it is not saved. Please anyone suggest the solution for my problem..... and mail at rupedhiman@gmail.com.
Rupinder
I have two forms...frm1 has a text box with an expression in it and I need frm2 to display the result of the expression. I'm using the DLookup expression and it either gives me #Name? or #Error? message in the text box frm2.
=Dlookup("[loan#]","tbl_loan","[Days] =" & Forms![frm1]!Days) <that gives me #Name? message
I've got these expressions in a query to extract parts of an mp3 file full path from a field and just let the name of the song.
exp: Mid([imported];[exp3]-[exp2];Len([imported]))
exp2: InStr([imported];"")-4
exp3: InStrRev([imported];"")
exp4: Left([exp];Len([exp])-4)
"exp4" is the clean name of the song.
Now how can I update my "song name" field (which is empty) to be same as "exp4" . Is it an update query? If so how can i do it?
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
I want to add a number to my results within a query depending on the month and how many results. For example I have 10 results in my query 3 from January, 5 from March and the rest from April. The 3 from January would be 1,2,3. The five in March would be 1,2,3,4,5 and so on. Is it possible to do?
I'm using access 2003.
I there is no result in query, I need the default result zero in my form field. I only use query wizard to create queries.
View 5 Replies View RelatedIs there an easy way of entering a value in a text box, passing to a query to do a count function and then return the value of count function in to anther text box?
View 5 Replies View RelatedI am creating a customer database for an independent insurance agency. Within that customer database, we have a lookup field that references another table "Carriers". This is where we select the insurance carrier we have placed the clients insurance with (ex. Geico, progressive, etc). This field allows multiple entries since clients can have policies with different companies.
We also have contact detail forms for each of our insurance carriers where we store information like web logins, contact phone numbers, and other notes. These forms are based on the same table as the lookup field.
I would like to create a hyperlink so, if we are looking at a clients form with all of their contact info we can click the insurance company in the carrier field and it will bring us to the contact details form of that carrier.
First, I am a beginner with Access. I am a graphics designer that has been assigned to cover for a db programmer that quit!
Here is my problem:
I have a database that we use to hold customer information. There are 22 fields in each record, and we are now well over 3000 records.
Once upon a time, to find a specific customer, we would just go to the bottom of the page, and use the arrow buttons to scroll through them all. This is no longer possible as the size is too big to manually search.
What I would like to do:
Upon Access startup, display a form that has a single input field and a button titled "Search". The input field is titled [UserName], as this field is the unique key identifier for the record. When the user types in the UserName and clicks the SEARCH command button, another form appears to display JUST THAT RECORD in the easy to read form!
If this is really basic, I appologize. I have 20 years experience with commercial illustration and only 7 days experience with Access. I have purchased books, and hit the forums, but I am not a VB programmer, and my skills this area are REAL weak!
My resourse books include the following:
Wiley - Access 2003 Bible [Prague, Irwin & Reardon]
Osbourne - How to do Everything with Access 2002 [Anderson]
O'Reilly - Access Cookbook [Getz, Litwin & Baron]
Thanks in advance for any assistance.
I have a simple database I am trying to put together. One table, one form, very vanilla. I created a query that ranks values that I enter in a table. I would like to put the result of the query (the rank) next to the value in the form itself. I thought if I created a text box and entered the rank field in the text box, that would be it, but when I run the form, I get "#Name?".
Any thoughts? Is this even possible?
Thanks in advance.
I have a query the SQL view is
SELECT Max([customerID]) AS Total
FROM tblCustomer
WHERE (((Left([CustomerID],3))=[forms]![frmNewCustomer].[txtFirst3]));
I run the query and the result is
Total
BIS007
How do I get Total into a text box on my form so that I can use it?
Does anyone know if it's possible to show the results of a query on a form? I have 4 people entering various "outcomes" into a table. There are a possible 8 different outcomes and the one i'm interested in is the "completed" outcome. I would like to be able to show on the form used to enter the data, the total number of "completes" so far.
View 3 Replies View RelatedHello,
I'm creating a database to enter Tests that have been done. I am recording the date that the test was performed and now I need Access to tell me when the next test is due (i.e. 5 years from the latest test date). I used the expression dateadd("yyyy",5,[NextTestDue]). But when I put this under Control Source, it only displays it on the form but does not save it in the table. How do I get it to save the info to the table?
I need the NextTestDue field to automatically populate when I enter a test date (The date the test was performed). Please help.
I am creating a form for 3rd-party service contracts, and I have two tables called tblServiceProviders and tblServiceDescriptions. tblServiceProviders has a 1-to-many relationship with tblServiceDescriptions; i.e., each Provider can have multiple Descriptions (real world: product offerings). So for example, ServiceProvider "Acme Corp" might have ServiceDescriptions "AcmeCare", "AcmePremium" and "AcmeInstall". My database is normalized, so tblServiceProviders has an ID field that is related to a field called ProviderID in tblServiceDescriptions.
Each ServiceContract includes, in addition to customer name and some other stuff, one and only one ServiceDescription. The ServiceDescription field in the ServiceContract form is a drop down box called comboSvcDesc and it contains all of the ServiceDescriptions in the database. I also have a non-editable text field on the form called txtSvcProvider.
What I want is to have the name of the ServiceProvider ("AcmeCorp") automatically populate in txtSvcProvider whenever the comboSvcDesc box is changed. So for example, if I change comboSvcDesc box from "WidgetCare" to "AcmeCare", I want txtSvcProvider to change from "WidgetCorp" to "AcmeCorp".My thought is that I should be running a SQL join query to get the related value "AcmeCorp",but I can't figure out how to make this work in VBA.
I have created a search query that allow users to search existed record in database. The query work completely fine, and it gives me the data that I want when I fill in my criteria. However, the query result is not showed in a form, but show in a query window. The form is like that
Company (drop down with a list of company)
Employee (drop down with a list of employee)
Program (drop down with a list of program)
Others (some other fields)
...
Go Query (a button)
How do I pass the query result into a form that I made? So the users can edit and update the record.
Hi Experts,
Novice here,
I managed to create a FORM that has fields that calculates basic additions/subtractions formula but for some reason the fields on the TABLE does not update?? Any suggestions?
Thank you,
jola
:confused:
i'm a begginer at access, so please excuse me if this question is very basic.
i have a multiple table select query which shows me which user has which piece of a equipment at that given time.
I also have a form that logs any problems the users have with the equipment, what i want that form to do is show the results of the query based on inputting the users name. ie put the serial number and model number into the relevent fileds on the form.
I can get the form to run the query but that just shows it in the query window, i need it to show on the correct feilds on the form.
Hope that makes sense and that somebody can help me.
Cheers
Burty
I would like to allow users create a query and then display the result in a new table. Just like the regular way in Access.
I know how to do it using DAO. However, I am working on a ADP, and want to use ADO for this. I cannot find a solution in ADO. So frustrated!
Any help is appreciated!
I have a form that contains 3 text boxes, 1 Combo Box and a Command Button
txtLastName
txtFirstName
txtDOB
cboGender
cmdFind
I want the user to be able to enter at least one of these criteria and
for a form to pop up with a list of individuals that match the criteria
example
user enters Male under cboGender and Young in txtLastName
a Form will pop-up that will give a list of user that match those criteria
LastName, FirstName, DOB and Gender data are stored in tblPatientDemo
Any ideas on how I can accomplish this goal??
Hi
I have a Form called Products. Each product is uniquely identified with a primary key called [ProductID].
Products contains a calculated field called [UnitsOnHand]. [UnitsOnHand] gets its values from a subform called ProductTransaction that exists within the Products Form.
ProductTransaction contains a field callled [Received] . At the moment [UnitsOnHand] will add up all the values it finds on the [Received] field to derive the [UnitsOnHand] value for that product at any particular time ie =Sum(nz([Received])) on [UnitsOnHand] .
I also have a Query AutoSell that outputs a value for each [ProductID].
How can I use these values for each [ProductID] generated by the Query to substract from the values on the [UnitsOnHand] calculated field on the Product form.
ViRi
It's written a Function which takes an Integer as Arg, runs a Select Statement and returns a String.
Such that the Control Source of the Forms reads....
=ConvertCompanyNumbers([12]) = where [12] is the Name of the field. Not My choice to have numbers as Fields.. but there it is.
Anyway.. I'm getting an #Name? error... also tried [Ctl12] but again same error.
I know the function works, as I can use something like =ConvertCompanyNumbers(12) and it displays the appropriate result.
What is up with this?
Hi,
I have some queries in MS-Access, that I run twice every day.
I store the query result in excel, with specific name and slot-code like HSC_25-Dec-04_S2.xls
( Exmple : High_Score_Customer_25-Dec-04_Slot-2) .
First few letters of the file name is static, but I want to select the Date, Slot_Code from the controls.
I have designed the form, 'CmdExport' is the command-button name, on clicking the button I need to export query result in MS-Excel.
I need help to proceed.
Can anyone guide me....................please help me...
Regards,
Hi,
I have a problem to make a search form in access, I want to divise my form, at the top will be the search criter (8 fields) and in details section will appear the result. I use a continuous subform with a query on the searched fields.
But I can't actualise or open the subform with the new results.
I would like a button to start my search or a system to automatically show the result on AfterUpdate event.
Can you help me, please? i trying to solve this for a long time...
Thank you,
Mrflo
I am creating a driving school database and have four different tables. Student, Instructor, Lesson and Lesson Type.
In the footer of my subform which works out what lesson type the student has taken and from which instructor, I have created a calculation to multiply the number of hours a student does to what type of lesson they take.
=([LengthOfLesson]*[cost]) (This works ok)
I then want to add all of these options together. I have tried:
=sum([OverallTotal]) in the footer (This doesn't seem to work)
and then
=[Booking].[Form]![OverallTotal] to show it in the form
The name of the subform is correct (booking) and name OverallTotal is also correct but I keep getting #error message.
I'm trying to use the job number field, which is my primary key in my table, to auto assign the new job number on my Forms. Currently, I have to enter a new job number manually, and it has to be unique because that is the way I have it set. I can't use autonumber because Access does not allow you to select what number you would like to start from, which would not play well with my current job numbers.
Basically I need Access to get the job number from the last record and add 1 to it, or just find the last / highest current job number and add 1 to it.
I created a query (qryFindJob#s) that list all of the Job Numbers, but I'm not sure how to add the query results to my Form to display the Job number + 1. I created a text box, typed a simple expression in the control box to see if I was on the right path (=[qryFindJob#s]) but I keep getting a #Name? error in the txt field.