|
Thanks again for the great info Rob!
Yes, I should have been using 2 for the decimal place to round.
It apprears that with MS Access, I can't use the "c" parameter (I get an error that I'm using too many parameters).
I really don't want Bankers Rounding (even # to round up):
Round(0.005,2) = 0.00
Round(7.125,2) = 7.12
I want old school rounding  )
Round(0.005,2) = 0.01
Round(7.125,2) = 7.13
Do you think I'll have to round the SQL result using something like this:
Floor(x * 100 + 0.5m)/100
to get what want?
thanks,
Ron
|
|
|
|
|
myNameIsRon wrote: really don't want Bankers Rounding (even # to round up):
SQL server uses "round up" not Banker's rounding (I was wrong about that in the first post). I think if you just change the 3 to a 2 you'll get what you want from SQL. Access may be a diffewrent matter, check the link in the previous post: it details rounding for all microsoft products (they are very inconsistent).
We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest.
Patricia Aburdene
|
|
|
|
|
Hi Rob,
I did a bunch of tests and unfortunately Access does use the Banker's rounding when using ROUND. I was able to solve both my issues (SUM truncating and ROUND up only on even) though by using FORMAT on the calculations that could cause values to go past the second decimal place. I didn't think I could SUM on a FORMAT because I thought FORMAT converts the value to a string... but it works ok.
SUM ( FORMAT( price * percent, 'Standard' ) )
I really appreciate all your help, I'm new to programming and find almost everything a challenge… but a lot of fun too ).
Say, I have a different question about pulling data from a database...
Sometimes I want to use \n to give me a new line in a MessageBox.Show().
This works ok as a string to use:
string info = "Name\nAddress\nPhone";
but when I try to use a string that has been pulled from a database, the \n does not give me a new line (just shows up). Is there a way to change the string to work with \n again?
Thanks again,
Ron
|
|
|
|
|
Yea, "\n" is an "escape sequence" that the compiler understands to replace with the non-printing newline character(s), but embedded in a db string it is just \n...
You might be able to pass the db string through a format function and get the newlines replaced. If you're using C# try String.Format(dbString). C++ CString.Format(dbstring).
I haven't tried this, and I'm just guessing, but that is what I would try first.
We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest.
Patricia Aburdene
|
|
|
|
|
|
Hey, "Formula Fields" in Reporting Service (SQL 2005), is it in VBScript?
Norman Fung
|
|
|
|
|
 I have a table on Microsoft Access and I need to export it to a CSV file when I press a button on an Access form. I'm using some Transact-SQL code inside VBA, but nothing yet. Any commentaries?
|
|
|
|
|
Microsoft Access does not use TRANSACT-SQL. That dialect is specific to SQL server.
If you post the relevant parts of the VBA code, someone might be able to help...otherwise you're asking us to guess, which most of us won't wate our time on 
We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest.
Patricia Aburdene
|
|
|
|
|
 ok...let me re-phrase the question...this is the statement that I'm using to export the table to a excel file...
DoCmd.TransferSpreadsheet acExport,8, "Table2", "C:\Table2", True
This is after making this query...
DoCmd.RunSQL "SELECT [Fields] INTO Table2 FROM Table1;"
All I want is to export it to csv, or using the excel file that I already have...thanks for your time 
|
|
|
|
|
Using SQL Server 2000, does the SQL Reporting tools allow you to output in RTF format?
|
|
|
|
|
I'm looking for a function in SQL Server to find the first occurence of a given character within a given string and return the position as int.
Really don't feel like re-inventing the wheel today, help would be appreciated!
you can't forget something you never knew...
|
|
|
|
|
You can use Patindex or Charindex in sqlserver
|
|
|
|
|
Hello Friends,
I have a field of description which holds data like this
F001 - xyz
H001 - abcd
FG0003 - lmn
...
...
...
...
I want to remove that par of data before this hyphen sign including hyphen "-" and want to represent data after hyphen sign.
Help will be appriciated.
Naveed Kamboh
|
|
|
|
|
query
use pubs
select substring (phone,charindex('-',phone)+1, Len(phone)-charindex('-',phone)+1)
from
authors
|
|
|
|
|
Thank you verey much its 100%.
Naveed Kamboh
|
|
|
|
|
I have to create stored procs for insertions and updates for a new project with lots of parameter (around 20 to 35), i wanted to know the advantange and disadvantages of the two methods below
Method 1. --- the usual way
Create Procedure SaveNewSettings
@param1 varchar(20),@param2 varchar(20),@param3 varchar(20),
@param4 varchar(20),....etc
---- insert into table values (
Method 2. -- the xml way
Create Procedure SaveNewSettings
@XMLParam As varchar(200)
AS
EXEC sp_xml_preparedocument @hDoc OUTPUT , @XMLParam
--- Do the openxml stuff and update
Which method of using parameters is better wrt performance and usability.
Please suggest.
I am using SQL Server 2000 & ASP.Net (VB.net as code behind) for my project.
|
|
|
|
|
I would like to know if I can install both MSDE and MS SQL 2005 Express together such that I have an MSDE Database and also a MS SQL 2005 database to use.
|
|
|
|
|
Kuira,
You can run both side by side.
Paul
|
|
|
|
|
Can you SQL gurus help me craft an UPDATE statement that will insert a modified clone of a set of rows into a table? For example, my table FIELDS_IN_VIEW has 2 columns VIEW and FIELD and contains several thousand rows:
Row VIEW FIELD
--- ---- -----
1 23 100
2 23 701
4 23 459
...
6500 99 804
I'd like to clone all the rows that apply to view 23 for a new view (eg: 24). View 24 is guaranteed to not already exist in the table.
Thanks,
/ravi
My new year's resolution: 2048 x 1536
Home | Music | Articles | Freeware | Trips
ravib(at)ravib(dot)com
|
|
|
|
|
try it
insert into FIELDS_IN_VIEW select 24, field from FIELDS_IN_VIEW where view = 23
|
|
|
|
|
|
as a newbie, I have developed a simple administrative app that lists members of our mailing list. ON the viewing page, checkboxes are dynamically listed along with the member info. When one checks a box and clicks 'edit', i want the item checked to open into the edit page with it's appropriate data. Problem: I can get the checkbox associated to an id number on the viewing page. I just can't seem to figure out how to send that id and data to the edit page. any help or suggestions would be great. can provide code examples if needed.
karinb
-- modified at 11:42 Thursday 4th May, 2006
|
|
|
|
|
Cross posting - very annoying... 
|
|
|
|
|
just put that id in session state or pass it as query string to the edit page.
|
|
|
|
|
I am trying to connect to MDP provider using
OLE DB For OLAP following these steps:
1-i'v got the List of providers..and choose one of them
2-i called ParseDisplayName() ,..the i called BindMoniker() method as written in the code below...
3-When i Call ppIDBInitialize->Initialize(); ..i got Access voilation exception
can any one help me please....
Here is the code:
ULONG chEaten = 0;
hr = pIParseDisplayName->ParseDisplayName(pIBindCtx,rgEnumInfo[i].wszParseName, &chEaten, &pIMoniker);
ppIDBInitialize=NULL;
hr = BindMoniker(pIMoniker, 0, IID_IUnknown,(void**)&ppIDBInitialize);
rgProps[0].dwPropertyID = DBPROP_INIT_LOCATION;
rgProps[0].vValue.vt = VT_BSTR;
rgProps[0].vValue.bstrVal = SysAllocString(L"server");
rgProps[1].dwPropertyID = DBPROP_INIT_DATASOURCE;
rgProps[1].vValue.vt = VT_BSTR;
rgProps[1].vValue.bstrVal = SysAllocString(L"FoodMart 2000");
rgProps[2].dwPropertyID = DBPROP_AUTH_INTEGRATED;
rgProps[2].vValue.vt = VT_BSTR;
rgProps[2].vValue.bstrVal = SysAllocString(L"SSPI");
DBPROPSET PropSet;
PropSet.rgProperties = rgProps;
PropSet.cProperties = 3;
PropSet.guidPropertySet = DBPROPSET_DBINIT;
IDBProperties *pIDBProperties=NULL;
hr=ppIDBInitialize->QueryInterface(IID_IDBProperties,(void**)&pIDBProperties);
hr=pIDBProperties->SetProperties(1, &PropSet);
hr = ppIDBInitialize->Initialize();
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
