Problem With The Sum Function In Access

Oct 18, 2004

Hello All -

I have a database in Access with the following fields:
Portfolio Code
Trade ID <- Uniquie
Trade Shares
Order Decision Price
Trade Execution Price
Trade Date

I want to retrieve total traded market value for the 2nd quarter (months 4, 5 and 6) for specific portfolio's. When I retrieve a list of all individual trades (by trade ID which is unique for each), Trade Shares, Order Decision Price and Trade Execution Price and do the Multiplying in Excel (i.e. Order Decision Price * Trade Shares and Trade Execution Price * Trade Shares), the final aggregate total looks correct. However, when I try to do the calculation in Excel and group it so I do not get a list of all trades, just the total Traded Market Value ( using Sum([Order Decision Price]*[Trade Shares]) and Sum([Trade Execution Price]*[Trade Shares]) ) in design view as Expression, the total traded market value is larger than what it was in the first step when the calc was done in Excel. It seems that some of the reported values are exactly double of what I get while doing the calc in Excel.

This is only happening in the 2002 database. When I use the Sum method in all other databases, the results are 100% on.

Any ideas??

Example of the SQL created by design view:
SELECT Sum([Order Decision Price]*[Trade Shares]) AS Expr1, Sum([Trade Execution Price]*[Trade Shares]) AS Expr2
FROM TradeHist
WHERE (((TradeHist.[Portfolio Code])="852" Or (TradeHist.[Portfolio Code])="2CM" Or (TradeHist.[Portfolio Code])="2CN" Or (TradeHist.[Portfolio Code])="2WA") AND ((Month([Trade Date]))=4 Or (Month([Trade Date]))=5 Or (Month([Trade Date]))=6));

View 14 Replies


ADVERTISEMENT

Help Convert MS Access Function To MS SQL User Defined Function

Aug 1, 2005

I have this function in access I need to be able to use in ms sql.  Having problems trying to get it to work.  The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String   Dim strReturn As String   If IsNull(strField) = True Then      strReturn = ""   Else      strReturn = strField      Do While Left(strReturn, 1) = "0"         strReturn = Mid(strReturn, 2)      Loop   End If  TrimZero = strReturnEnd Function

View 3 Replies View Related

Access Val Function In SQL Server

Oct 5, 2005

What is the counterpart of this function when using SQL Server 2000? Im
getting this error "System.Data.SqlClient.SqlException: 'Val' is not a
recognized function name"

What do I have to change in my queryString?

SELECT ASP_MainForm.UserID, Val([FormNo]) AS Expr1,
ASP_MainForm.DateCreated, ASP_MainForm.DateNeeded,
ASP_MainForm.FormStatus, ASP_MainForm.Print, ASP_MainForm.PRNo,
ASP_MainForm.ForUse, [FirstName]+' '+[LastName] AS CompName FROM
ASP_MainForm INNER JOIN CEN_USERS ON ASP_MainForm.UserID =
CEN_USERS.UserBadgeNo WHERE
(((ASP_MainForm.FormStatus)<>'Approved') AND
((ASP_MainForm.ForUse)<>'Test')) ORDER BY Expr1 DESC

Pls help thanks

View 2 Replies View Related

I Am Looking For The Function That Is The Same As InStr In Access

Jan 29, 2001

Hello there...
I am looking for the function that is the same as InStr in Access for SQL server. I have a column that has format like this.. Lastname,Firstname Middlename...
This column doesn't separate each one of them. However I need to separate Lastname and Firstname and Middlename.. I was told that in Access there is function(InStr) that can find a position of comma and separate it as Lastname like that....
I was searching BOL but I couldn't find like this function in SQL Server..
So I need help:-))))
Because everybody has a different length of the lastname, I have a problem.
I can not use SUBSTRING or LEFT or RIGHT because of the varying position of comma ...

Thanks in advance
Jay

View 1 Replies View Related

Calling MS Access Function From DTS

Mar 10, 2004

Does anyone know if you can call an Access function from DTS?
I'm trying to delete data from an Access database, Compact the database, and load new data. My snag is calling a function in Access to compact the database.

Suggestions?

View 2 Replies View Related

Left() And Right() Function In MS SQL Vs MS ACCESS

Jun 29, 2006

I normally use MS ACCESS vs MS SQL,, which has a left() and right()function. I need to use MS SQL for this project but I am not familiarwith it. I have read a few books, but can not figure out how to dothis. Please help.If I need to compare the first 4 letters of a field, with the firstfour letters of another field, how can I do this?Select field1, field2 FROM table1 Where left(field1,4)=left(field2,4)(MS SQL does not have left() and right() functions)Please help.In addition, I have a CSV file with data like 10.20, which I importinrto a numberic field. Unforunately the value gets changed to 10.It's seems to get rounded. How can I fix this.The import SQL I use is....BULK INSERT dbo.tableFROM 'c:MYDATA.CSVWITH(FIRSTROW = 1,FIELDTERMINATOR = ',',ROWTERMINATOR = '')Thank you in advance!!!

View 9 Replies View Related

Convert Access Function To SQL

Jul 20, 2005

I'm going crazy trying to convert an Access Function to SQL.From what I've read, it has to be done as a stored procedure.I'm trying to take a field that is "minutes.seconds" and convert it to minutes.This is what I have in Access:Function ConvertToTime (myAnswer As Variant)Dim myMinutesmyMinutes-(((((myAnswer * 100)Mod 100/100/0.6)+(CInt(myAnswer-0.4))))ConvertToTime =(myMinutes)End FunctionWhen I tried to modify it in SQL:CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS ConvertToTimeFunction ConvertToTime(myAnswer As Variant)Dim myMinutesmyMinutes = (((((myAnswer * 100)Mod 100)/100/0.6)+9CInt(myAnswer-0.4))))ConvertToTime=(myMinutes)EndI get an error after ConverToTime.

View 2 Replies View Related

How To Get The Functionaliy Of &#39;format Function&#39; Of Access In Sql

Jan 2, 2002

Hi,

I am moving the database built in access to Sql 7 and i am unable to find any subsitute of format function of Access in sql. Please help me out ot find a suitable solution of it.

Thanks

View 1 Replies View Related

Data Access :: Function Using Select

Oct 15, 2015

if I have table XXXX with columns a,b,c,d,e,f,g,h,i and I need a function or stored procedure.If I use SELECT a,b,c,d from XXXX and the function returns the result set with columns e,f,g,h,i only Means the columns used in Select must not be included in the result set.

View 10 Replies View Related

MS Access' IIF Function In A MS SQL Server View

Oct 16, 2006

I am translating some of my Access queries to SQL views. In one of those, I had a very convenient function called "IIF" (e.g. IIf(IsNull([Remark]),"NULL","NOT NULL").

How is this function called in the MS SQL Server 2000? Apparently I cannot use either "IIF" nor "CASE" in the query/view.....

View 10 Replies View Related

SQL XML :: XML Local Name Function For Attribute Value Access

Jun 8, 2015

I am trying to get the details of an xml. For this I am using below code. I am not able to get the desired output.I want to add 1 more columns AnimalID and DetailID.I want these column to interlink the other values. Below is the desired output.

declare @x xml
set @x=
'<config>
  <Animal name="Baboon" ref="Ape">    
    <detail name="Ape detail" ref="Monkey" typo="animal" required="true">

[Code] ....

View 7 Replies View Related

Access Linked Server System Function

Mar 16, 2007

Could anyone shed some light on the syntax of accessing system function on a linked server?I'm trying to get the recovery models of databases on a linked. However using databasepropertyex locally generates wrong results.e.g. select databasepropertyex(name, 'recovery') RecoveryModel from [server/databasename].master.dbo.SysDatabases I tried select [server/databasename].databasepropertyex(name, 'recovery') RecoveryModel from [server/databasename].master.dbo.SysDatabases which does not work.  Thanks. 

View 1 Replies View Related

Function In Access Vs. Stored Procedures In SQL Server

Mar 7, 2001

I am used to working in Access and just recently became somewhate proficient using custom functions in modules.
I am trying to figure out what the equivalent of functions is in SQL Server. I mean, does a Stored Procedure in SQL Server replace a module in Access? Can you declare different functions in SQL Server like you can in Access?
Thanks for your help.
Mike

View 1 Replies View Related

SQL 2012 :: Allowing Access To DECRYPTBYKEY Function

Nov 6, 2015

How to grant users the right to use the DECRYPTBYKEY function to decrypt the data. I have seen some people talk about using a stored procedure or view to surface the decrypted data, but how would you implement that when trying to pull back a single dataset? It would be best to use an inline function to allow the row to be returned decrypted, but opening the keys isn't allowed in the function construct.

So, I know I have to be missing something, but how do you let basic users (db_reader types) decrypt the data they need based on a custom database role? What do I need to give the user permission to?

My setup is simple:
- I have my SMK
- I have a DMK encrypted by password
- I have my self signed certificate
- I have my symmetric Key encrypted by the certificate

View 0 Replies View Related

Using Access Form Control As SQL Function Criteria

Jul 28, 2006

I have a combo box named [myControl] on an Access form that I can use to select/enter a site name (ABC). There is a button on the same form that runs a report. The underlying record source for the report is a SQL Function. Is there an easy way to pass the combo box value to the SQL Function so that only records for site 'ABC' are displayed in the report? I tried Forms![myForm]![myControl] in the criteria box of the Function and it did not work. THANKS!

View 3 Replies View Related

C# User Defined Function: Is There A Way To Access The Name Of The Sql Server?

Apr 26, 2006

This is my problem: I do not know how to get the servername from a C# user defined function . Is this possible?

I am writing a User Defined Function (UDF). Inside of this user defined function I need the name of the databaseserver that it is running on. Does anyone have an idea how I might do this? Is there an enviromental variable that I could access within the C# code I use to write the UDF?

I could always use a parameter to pass in the name of the server, but I would like to have as few parameters as possible.

Thanks in advance,

Sean

View 4 Replies View Related

Problems With MOD-Function By Accessing MS-Access Database Via SQL In Delphi

Aug 23, 2005

Hello,the following problem:I use Delphi 6 to access a MS Access Database.In short, the Delphi Code looks like this:ADOConnection1.Open;ADOQuery1.Close;ADOQuery1.SQL.Text := 'SELECT * FROM database1 WHEREvalue1=1 AND value2= ' + inttostr(array[3]);ADOQuery1.Open;Label5.Caption:=ADOQuery1.Fiel*dByName('value4').A sString;Now I only want to select these rows, where the Integer value3 ends on1. So I need the Modulo-Function. I found in the Internet these twofunctions:MOD(x,y) und x % yBut I get an error if I want to use one.For example, If I changeADOQuery1.SQL.Text := 'SELECT * FROM database1 WHEREvalue1=1 AND value2= ' + inttostr(array[3])+' AND MOD(value3,10) = 1;Delphi shows me a Syntax Error in this statement.Thx for help!

View 2 Replies View Related

What Is Equivalent Of Format(date) Function Of MS Access In MS Sql Server 2000

Jul 20, 2005

Hi All,I am facing a problem with a sql what i used in MS Access but its notreturning the same result in MS Sql Server 2000. Here i am giving thesql:SELECT TOP 3 format( MY_DATE, "dddd mm, yyyy" ) FROM MY_TAB WHEREMY_ID=1The above sql in ACCESS return me the date in below format in onecolumn:Friday 09, 2003But in Sql server 2000 i am not getting the same format eventhough iam using convert function, date part function etc.Please if you find the solution would be helpful for me..ThanksHoque

View 3 Replies View Related

What's The Equivalen Function Of StrConv ([FirstName] , 3 ) In ACCESS In Sql Server 2000?

Jul 20, 2005

HI,i got a problem while using StrConv function in sql server.My requirement is:-suppose in a name field i have "jhon smith" Now i want to run a sqlwhich will give me the result like "Jhon Smith"--which means uppercase first.Now i am running a query like below which is giving me "Jhon smith"but i want "Jhon Smith".SELECT [CustomerID] ,upper(left(firstname,1)) + lower(right(firstname,len(firstname)-1)) AS [FirstName_] from my_table;But the above query is not giving me the right result. Pleasesuggest...ThanksHoque

View 2 Replies View Related

Passing Parameter Query From SQL Function To Access Project Report

May 20, 2006

I can pass a parameter from an Access Query to an Access Report (MDB) by entering [Select Date] in the Query criteria and by placing an unbound control with a control source =[Select Date] on the report. I can't get this to work from a SQL Function Criteria to an unbound control on the Access Data Project Report. In the Function Criteria, I enter @SelectDate. In the Report control, I enter @SelectDate and it gives me an 'Invalide Column Name' error. Any idea how I can pass a parameter from a SQL Function to an ADP report?

THANKS!

p.s. I tried searching for other postings on this without any luck.

View 1 Replies View Related

ODBC Link From Access To SQL Server 2005 Stored Function

Sep 20, 2006

If I define a table-valued function in a SQL Server 2005 database, can I link to it from Access 2003 using ODBC?

I've defined the function successfully, and I can link from Access to tables in the database (so my ODBC link is basically functioning), but I can't see the table-valued function in the Linked Table Manager in Access.

I can define a pass-through query to grab the table, but with a pass-through query I have to provide the ODBC password every time.

What am I missing?

Suggestions?

View 1 Replies View Related

In-Line Table-Valued Function: How To Get The Result Out From The Function?

Dec 9, 2007

Hi all,

I executed the following sql script successfuuly:

shcInLineTableFN.sql:

USE pubs

GO

CREATE FUNCTION dbo.AuthorsForState(@cState char(2))

RETURNS TABLE

AS

RETURN (SELECT * FROM Authors WHERE state = @cState)

GO

And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.

I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:

shcInlineTableFNresult.sql:

USE pubs

GO

SELECT * FROM shcInLineTableFN

GO


I got the following error message:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'shcInLineTableFN'.


Please help and advise me how to fix the syntax

"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.

Thanks in advance,
Scott Chang

View 8 Replies View Related

A Function Smilar To DECODE Function In Oracle

Oct 19, 2004

I need to know how can i incoporate the functionality of DECODE function like the one in ORACLE in mSSQL..
please if anyone can help me out...


ali

View 1 Replies View Related

Using RAND Function In User Defined Function?

Mar 22, 2006

Got some errors on this one...

Is Rand function cannot be used in the User Defined function?
Thanks.

View 1 Replies View Related

Pass Output Of A Function To Another Function As Input

Jan 7, 2014

I need to be able to pass the output of a function to another function as input, where all functions involved are user-defined in-line table-valued functions. I already posted this on Stack Exchange, so here is a link to the relevant code: [URL] ...

I am fairly certain OUTER APPLY is the core answer here; there's *clearly* some way in which does *not* do what I need, or I would not get the null output you see in the link, but it seems clear that there should be a way to fool it into working.

View 5 Replies View Related

I Want A Function Like IfNull Function To Use In Expression Builder

Jul 24, 2007

Hi,

I wonder if there a function that i can use in the expression builder that return a value (e.g o) if the input value is null ( Like ifnull(colum1,0) )



i hope to have the answer because i need it so much.



Maylo

View 7 Replies View Related

ROW_NUMBER() Function Is Not Recognized In Store Procedure.(how To Add ROW_NUMBER() Function Into SQL SERVER 2005 DataBase Library )

Feb 4, 2008

Can anybody know ,how can we add  builtin functions(ROW_NUMBER()) of Sql Server 2005  into database library.
I get this error when i used into storeprocedure :
ROW_NUMBER() function is not recognized in store procedure.
i used MS SQL SERVER 2005 , so i think "ROW_FUNCTION()" is not in MS SQL SERVER 2005 database library.
I need to add that function into MS SQL SERVER 2005 database library.
Can anbody know how we can add that function into MS SQL SERVER 2005 database library?
 

View 4 Replies View Related

Function To Call Function By Name Given As Parameter

Jul 20, 2005

I want to write function to call another function which name isparameter to first function. Other parameters should be passed tocalled function.If I call it function('f1',10) it should call f1(10). If I call itfunction('f2',5) it should call f2(5).So far i tried something likeCREATE FUNCTION [dbo].[func] (@f varchar(50),@m money)RETURNS varchar(50) ASBEGINreturn(select 'dbo.'+@f+'('+convert(varchar(50),@m)+')')ENDWhen I call it select dbo.formuła('f_test',1000) it returns'select f_test(1000)', but not value of f_test(1000).What's wrong?Mariusz

View 3 Replies View Related

Error While Creating Inline Function - CREATE FUNCTION Failed Because A Column Name Is Not Specified For Column 1.

Apr 3, 2007



Hi,



I am trying to create a inline function which is listed below.



USE [Northwind]

SET ANSI_NULLS ON

GO

CREATE FUNCTION newIdentity()

RETURNS TABLE

AS

RETURN

(SELECT ident_current('orders'))

GO



while executing this function in sql server 2005 my get this error

CREATE FUNCTION failed because a column name is not specified for column 1.



Pleae help me to fix this error



thanks

Purnima

View 3 Replies View Related

Using A Scalar Valued Function As A Parameter Of A Table Valued Function?

Feb 1, 2006

Ok, I'm pretty knowledgable about T-SQL, but I've hit something that seems should work, but just doesn't...
I'm writing a stored procedure that needs to use the primary key fields of a table that is being passed to me so that I can generate what will most likely be a dynamically generated SQL statement and then execute it.
So the first thing I do, is I need to grab the primary key fields of the table.  I'd rather not go down to the base system tables since we may (hopefully) upgrade this one SQL 2000 machine to 2005 fairly soon, so I poke around, and find sp_pkeys in the master table.  Great.  I pass in the table name, and sure enough, it comes back with a record set, 1 row per column.  That's exactly what I need.
Umm... This is the part where I'm at a loss.  The stored procedure outputs the resultset as a resultset (Not as an output param).  Now I want to use that list in my stored procedure, thinking that if the base tables change, Microsoft will change the stored procedure accordingly, so even after a version upgrade my stuff SHOULD still work.  But... How do I use the resultset from the stored procedure?  You can't reference it like a table-valued function, nor can you 'capture' the resultset for use using the  syntax like:
DECLARE @table table@table=EXEC sp_pkeys MyTable
That of course just returns you the RETURN_VALUE instead of the resultset it output.  Ugh.  Ok, so I finally decide to just bite the bullet, and I grab the code from sp_pkeys and make my own little function called fn_pkeys.  Since I might also want to be able to 'force' the primary keys (Maybe the table doesn't really have one, but logically it does), I decide it'll pass back a comma-delimited varchar of columns that make up the primary key.  Ok, I test it and it works great.
Now, I'm happily going along and building my routine, and realize, hey, I don't really want that in a comma-delimited varchar, I want to use it in one of my queries, and I have this nice little table-valued function I call split, that takes a comma-delimited varchar, and returns a table... So I preceed to try it out...
SELECT *FROM Split(fn_pkeys('MyTable'),DEFAULT)
Syntax Error.  Ugh.  Eventually, I even try:
SELECT *FROM Split(substring('abc,def',2,6),DEFAULT)
Syntax Error.
Hmm...What am I doing wrong here, or can't you use a scalar-valued function as a parameter into a table-valued function?
SELECT *FROM Split('bc,def',DEFAULT) works just fine.
So my questions are:
Is there any way to programmatically capture a resultset that is being output from a stored procedure for use in the stored procedure that called it?
Is there any way to pass a scalar-valued function as a parameter into a table-valued function?
Oh, this works as well as a work around, but I'm more interested in if there is a way without having to workaround:
DECLARE @tmp varchar(8000)
SET @tmp=(SELECT dbo.fn_pkeys('MyTable'))
SELECT *
FROM Split(@tmp,DEFAULT)

View 1 Replies View Related

Import Access Tables (set Up As Pass-through Table Types To Oracle )--OLE DB Connection To Access Cannot See Them

Mar 17, 2008

Access Connection

create a new Connection Manager by right-clicking in the Connection Managers section of the design area of the screen. Select New OLE DB Connection to bring up the Configure OLE DB Connection Manager dialog box. Click New to open the Connection Manager. In the Provider drop-down list, choose the Microsoft Jet 4.0 OLE DB Provider and click OK.
Browse to the Access database file and connection set up---all good!!!

Dataflow task
Add an OLE DB Source component
Double-click the icon to open the OLE DB Source Editor. Set the OLE DB Connection Manager property to the Connection Manager that I created . Select Table from the Data Access Mode drop-down list.
I cannot see the tables set up as set up as pass-through table types to a Oracle 9i db

Any ideas please help

thanks in advance
Dave

View 2 Replies View Related

Data Access :: Server Rejected The Connection - Access To Selected Database Has Been Denied

Jun 10, 2015

I have recently upgraded to SQL2014 on Win2012. The Access front end program works fine.

But, previously created Excel reports with built in MS Queries now fail with the above error for users with MS 2013.  The queries still work for users still using MS 2007. 

I also cannot create any new queries and get the same error message. If I log on as myself on the domain to another PC with 2007 installed it works fine, so I don't think it is anything to do with AD groups or permissions.

View 6 Replies View Related

Data Access :: Insert Rows To MS Access 2013 Without Listing Column Names

Nov 12, 2015

We need to insert data/rows from a SQL Server 2014 database into MS Access database.  The problem is, there are so many columns (100+) in the table and there are so many insert transactions of this kind (from different tables) that it is not very easy to write the code in VB.NET that lists all column names.

Both the Access and SQL Server tables have the same number of columns and the equivalent data types, so inserting is not really the problem.  It's just that is there a way to do an insert statement in T-SQL that does not name all the columns?

View 3 Replies View Related







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