I've read the posts regarding not store calculations. However, I was told by IS to store the mileage IRS deduction as it changed 3 times last year.
Any idea's how to handle this? I need the table to remember how much $$ was paid out in mileage expense. I had set up a field called mexpense and mileage, where =[Mileage]*0.375.
I have solved the problems getting values on the subform. I have not in getting values on the subform.For instance, on the one titled phone use the formula in the tutorial is:
=[sbfCustomerRoomUse].[Form]![txtTotalPhoneUse]
#Error results when the doc is put into a form mode.
Now when I input each value in the equation above separately.I still get no entry.
For " = sbfCustomerRoomUse" , I get #Error;
and for "= txtTotalPhoneUse", I get #Name.
CustomerRoomUse and txtTotalPhoneuse are from the subform that was dropped into the customer form in a previous step. It shows that explicitly when designed sbfCustomerRoomUse on the main that CustomerRoomUse come from a subform. This does not seem to need to be done with txtTotalPhoneuse, and I am not sure why. Neither one gives me a desired calculation result.
I have an odd problem that has stumped me for several days. I'm working on a form that contains a chart. The chart is based on a query, which is based on another query, which is based on a table. In the top query, I need to put some calculated fields that operate on other fields. But when I try to multiply two fields together, I get Null. I'm pretty confident that the fields I'm operating on are numerical.
The fields of the query should be:
MaxOfEverInSchool
MaxOfSurvivalToGrade
MaxOfEverInSchool
With these being the calculated fields:
MaxOfSurvivalToGradeCum: MaxOfEverInSchool*MaxOfSurvivalToGrade/100
MaxOfCanReadCum: MaxOfEverInSchool*MaxOfSurvivalToGrade*MaxOfCanRea dCum/10000
Any insight would be greatly appreciated--I just can't understand why Access would suddenly refuse to do math.
Hi,
I need to write a stored procedure that sits between sql server and MS access. This procedure will make sure that the queries run in access against the data in sql server will not access data for more than two years back. This is so that the queries run against the sql server do not hang up the server for too long.
Thank you.
Hi,
I am switching from sql server to msaccess and cannot find an equivalent of a stored procedure in access.
The task I want to do is the following.
1. I have a table with many field.s
2. I want to loop through all the fields and replace records which have a value of 0 with NULL.
How could I do this without writing seperate update statements for each of the fields.
Thanks.
What are the benefits of each a stored query or sql? I suspect that the stored query would run quicker>
View 1 Replies View RelatedI apologize that I am not a very good VBA programmer yet but I'm really trying....
I am attempting to calculate a value by adding two fields and storing the result into a third field - - My code only works if both of the fields are not null.
Private Sub PartialDepositAmount_AfterUpdate()
Dim PartialDepositAmount As Currency
Dim FinalDepositAmount As Currency
Dim DepositAmount As Currency
Me.DepositAmount = Me.PartialDepositAmount + Me.FinalDepositAmount
End Sub
If either field is null my result is null but I need to be able to add field one (or field two) to a null and store the result in the totals field.
Any suggestions will be greatly appreciated.
JohnJ
How do you make stored procedures in microsoft access 2003, can you actually do this or is it not part of access?
View 4 Replies View RelatedI have an Access mdb that has a bunch of tables linked from a SQL Server. On the SQL Server I have some stored procedures that do inserts into various tables. In the Access db I have some local (lookup) tables, queries, and other things that for various reasons can't/don't need to be stored in the SQL server. I'm using Windows authentication to connect to the SQL server because the security is managed through our domain.
I want to write a form that has unbound text fields that, when I submit the form passes all of those values as parameters to a stored procedure in the SQL server. I have written just such a form in an .adp file, which handles the integrated security nicely.
However, I'd rather do this in a regular .mdb file. I've read up on how I can use an ADO connection to do this, then create a SQL string to EXECUTE the stored proc, grab all the values off the form, and build the SQL.
But the problem with this solution is the authentication - I can't use the SA user and password in this connection string, I'd have to utilize the logged-in user's information.
Is there an easier way to execute the stored procedure from an .mdb? Using the integrated authentication?
Failing that, is there a way that I can create "local" (to the .adp) tables, queries, and so on in an .adp?
I come from a SQL Server background and am very familiar with store dprocedures and input parameteres.
Is there a way to use a similar method in Access. or example, passing parameters from a button on a form to a query, which can change depending on the input parameter? If so, how ?
Also, how much scope do you have in this for running SQL IF statements and such in the query? It seems that many Access queris can only have one statement at a time.
I'm running a access 2000 adp front end working from a SQL2000 database. I've got a sproc P_insertsessions i can get this work from a cmd button on a form but i'm having problems setting the paramerters i want it to use controls on the form.
@contractid = contractid
@start_date = startdate
@end_date = enddate
@hours = duration
Can anyone help.
I'm used to doing the following with Foxpro:
mSetup = "Replace table.desc with alltrim(table2.code)+" ~ "+table2.vno+" ~ "+table2.po+" ~ "+table2.vendor
To run the process I use a macro substitution:
&mSetup
What can I do as an equipvalent with VBA??
Thanks in advance,
Steve
Sometimes when I send a copy of an Access 2003 application to a remote user, a stored procedure or function will disappear. What causes this, and how can I get the entire application over to the users?
Thanks in advance!
Hi,
I have combo boxes on a form which are pulling values from a one table and being stored in another table. However they are being stored as '1' or '2' in the other table once selected on the form, as instead of being stored as their literal values ie 'car' or 'van'. Is there any way of making this happen, as it makes reporting a nightmare! Thanks in advance...
Is there any way to count how many lines of text are stored in a particular table field? How about counting how many times was the "enter" key pressed?
Thanks
Mafhobb
Hi,
I am calculating two fields (qtyorder-qtydesp) in a stored procedure.
The second field is from another query (view) which may not have a record relating to the main record (stored procedure).
The problem is the second field is simply left blank but I need it to be 0.
It makes the calculation in turn result in a blank field, i.e. 1-0 should equal 1 but because of the blank field it returns a null value.
I found that the good old Nz function wont work with stored procedures, is there any alternative?
Thanks,
James
I have and access form that has a list box with dates in it. I need to pick a date and then run a stored procedure in SQL based on that date. Can someone help me with the login to pass on the date?
So if I have listbox1 which is a list of dates from another table
and my procedure is simply
select * from main
where date = *******
How can I pass the list box value to the stored procedure? My procedure works with WHERE date = (SELECT(LEFT(GETDATE(),11))) but I have since learned that todays date will not always be used so I need to pass a value.
Thanks,
Dave
I'm working on and Access 2003 front end with a SQL Server Back End. I would like to take advantage of the speed of Stored Procs to populate a list box on a form. Here's the code I'm trying to use.
Set cmd = MakeStoredProc("StoredProcName")
Set prm1 = cmd.CreateParameter("ParamName", adInteger, adParamInput, , Me![ID])
cmd.Parameters.Append prm1
rstSource.CursorLocation = adUseClient
rstSource.CursorType = adOpenKeyset
rstSource.LockType = adLockOptimistic
Set rstSource = cmd.Execute
Me![ListBox].Recordset = rstSource
Access doesn't like this. What is wrong. I get the message "Object doesn't support the property or method"
Thanks for your help.
Sorry if this is an easy one and I just missing it, but I have a form in an Access2003 database with option groups for ratings for 18 individual tasks ie:
Safety: 1. Satisfactory 2. Marginal 3. Unsatisfactory 4. N/A
The value stored in the table is 1,2,3, or 4 for each one, obviously.
How can I change the stored value in the table to "Sat" "Marginal" "Unsat" etc?
Or, if easier, make the report convert the numerical values to names?
Hi All
Im having some Major issues with this problem, i only hope someone can help me!!
Firstly, let me set the scene. I have 1 Form, And 1 Subform within that form. The form gets the Main Data, the SubForm gets all the Related Data (in this case, its all the Items Of Equipment this client has) and then lists them in the subform. So it could return 1, or it could return 20 records.
What we want from this is to be able to record what the client thought of the piece of equipment, so there is a Option Group on the SubForm, which allows the user to choose 1 of 6 options saying whether it was useful or not and things.
BUT here lies the problem, if the SubForm has returned 20 records, i change the OptionGroup on record one, and every other 19 records become the same. if i change Record 2, record 1 and 3 to 19 are the same.
I want to be able to have a seperate choice for each record returned, but tis not working!!
The subform is populated by calling a SQL SP, i cant set the form to link directly to a view or SP because access doesnt allow me, by saying the recordset is not updatable!
I hope all that makes sense!!
Hello,
can we create a stored prodecure in Access 2000 and call it from VB. I've created in SQL Server with ASP but have no idea in Access. Can anyone please tell me how to do it. If so kindly specify some resources from where i can learn more related to this. thank you.
Not sure which forum this was under, but I figure Access might be it
I have a stored procedure already written which works fine, if I supply the criteria to it before or at manual execution. I want it to use a field on a form in an Access Data Project as it's criteria (as a form is built off the results of the procedure). I can't find any documentation on how to pass criteria to a stored procedure for use in SELECT WHERE statements.
Can somebody point me in the right direction? Here is my stored procedure:
spGetContact:
Code:CREATE PROCEDURE dbo.[spGetContact](@parHomePhone numeric)AS SELECT dbo.tblContactAddress.AddressLine1, dbo.tblContactAddress.AddressLine2, dbo.tblContactAddress.City, dbo.tblContactAddress.Zip, dbo.tblContactAddress.State, dbo.tblContactEmail.PrimaryEmail, dbo.tblContactEmail.SecondaryEmail, dbo.tblContactPhone.HomePhone, dbo.tblContactPhone.BusPhone, dbo.tblContactPhone.CellPhone, dbo.tblContact.ContactID, dbo.tblContact.FirstName, dbo.tblContact.LastName FROM dbo.tblContact INNER JOINdbo.tblContactAddress ON dbo.tblContact.ContactID = dbo.tblContactAddress.ContactID INNER JOINdbo.tblContactEmail ON dbo.tblContact.ContactID = dbo.tblContactEmail.ContactID INNER JOINdbo.tblContactPhone ON dbo.tblContact.ContactID = dbo.tblContactPhone.ContactIDWHERE (dbo.tblContactPhone.HomePhone = @parHomePhone)GO
When i execute this manually I get the dialog prompt to enter the value for @parHomePhone, which is what I want to automatically pull from txtPhone on the frmSearch form.
I would like to run a report that uses a stored procedure with parameters. Is there a way I can pass the parameters from the report to the stored procedure? I am NOT running it from a form.
I want to call the report from VBA code and pass it the parameters that are necessary to run the stored procedure. Any ideas?
hey everyone!
Is there an option to use Triggers and stored procedures in access 2003?
thanks!!!
I have a MS SQL 2000 stored procedure that acepts a parameter and returns a recordset.
I want to run this Stored procedure from an Access 2000 report and use the recordset for the report.
I want to pass a field off of a form that is user entered to the stored procedure.
Is this possible. Any help is appreciated.
I can figure out how to attach the stored procedure to the report as the dataset but cannot seem to figure out how to pass the dynamic parameter to the Pass-Through Query.
Thanks in advance,
Knight.
Hi,
I have combo boxes on a form which are pulling values from a one table and being stored in another table. However they are being stored as '1' or '2' in the other table once selected on the form, as instead of being stored as their literal values ie 'car' or 'van'. Is there any way of making this happen, as it makes reporting a nightmare! Thanks in advance...