Calculating A Stored Value

Jul 1, 2005

I 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

View Replies


ADVERTISEMENT

Stored Procedure

Jun 12, 2007

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.

View 2 Replies View Related

Stored Calculations

Nov 29, 2004

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.

View 5 Replies View Related

Stored Procedures

Sep 11, 2006

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.

View 5 Replies View Related

SQL Or Stored QUERY

Feb 3, 2008

What are the benefits of each a stored query or sql? I suspect that the stored query would run quicker>

View 1 Replies View Related

Stored Procedures

May 20, 2005

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 Related

Using Stored Procedures In Access

Nov 15, 2007

I 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?

View 5 Replies View Related

Parameters / Stored Procedures

Aug 25, 2006

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.

View 7 Replies View Related

Set Parameters For Stored Procedure

May 31, 2005

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.

View 1 Replies View Related

How To Run Instruction Stored In A Memvar??

Mar 10, 2005

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

View 1 Replies View Related

Stored Procedures Disappear

Feb 27, 2008

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!

View 6 Replies View Related

Combo Box Values Being Stored In Tables As 1 And 2 Etc..

Feb 12, 2008

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...

View 2 Replies View Related

Count How Many Lines Stored In A Field.

Jun 26, 2007

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

View 4 Replies View Related

Null Values In Stored Procedures

Aug 4, 2005

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

View 5 Replies View Related

Pass List Box Value To SQL Stored Proc

Mar 27, 2008

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

View 11 Replies View Related

Use Stored Proc As Recordset For Listbox

Mar 29, 2005

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.

View 14 Replies View Related

Changing Stored Value Of Option Groups

Aug 8, 2005

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?

View 4 Replies View Related

SQL Stored Procedure And A Form Recordset

Dec 19, 2005

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!!

View 1 Replies View Related

How To Creat Stored Procedure In Ms-Access

Oct 28, 2004

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.

View 5 Replies View Related

Passing Criteria To A Stored Procedure

Feb 22, 2005

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.

View 1 Replies View Related

Run A Report Using A Stored Procedure With Parameters

Jan 7, 2005

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?

View 4 Replies View Related

Triggers And Stored Procedures Question

Jan 12, 2006

hey everyone!
Is there an option to use Triggers and stored procedures in access 2003?

thanks!!!

View 3 Replies View Related

Access 2000 And SQL Stored Procedure

Oct 6, 2003

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.

View 8 Replies View Related

Combo Box Values Being Stored In Tables As 1 And 2 Etc..

Feb 12, 2008

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...

View 1 Replies View Related

Modules & VBA :: Returning A Value From Stored Procedure

May 11, 2015

I'm having an issue getting a return value from a stored procedure that I'm calling from VBA. This is what I have at the moment:

Code:
Dim strDate As String
Dim strWOStatus As String
Dim CurrentConnection As ADODB.Connection
Dim adoCMD As ADODB.Command
Dim adoRS As ADODB.Recordset
Dim ParamReturn As ADODB.Parameter

[Code] .....

The problem I am having is this error:
Error: 424
Description: Object Required

The line of code it errors on is:

Code:
Set .Parameters("@PartsUSedMTD").Value = ParamReturn

And the value of ParamReturn is always Null after it hits the line before it.

So it seems like it's not really creating the parameter variable SQL Server needs to run

View 4 Replies View Related

Modules & VBA :: Updating All SQL Stored Within All Forms

Mar 4, 2014

How would I update all SQL stored within all forms to change its syntax?I am returning all the row-sources for all the comboboxes on all forms in an Access db. Most work fine but some have syntax errors, for example double quotes instead of single. Is there any way of finding these stored SQL statements and updating them on mass, as I am doing it manually at the moment!

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved