|
In actual fact you are not taking any notice of what people are telling you. THE DATA IN THE FILE WILL BE CORRECT IF YOU TAKE THE TROUBLE TO LOOK. The problem is the default format for the data in EXCEL.
Yes, you can format the data to decimal, look in help for how to do it, but it will make no difference.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Here is the code.
'Still in VBA in MS Access
strFileName = "qryAggregationGroupEntityMatching"
strSQLString = "SELECT tblBSMappingGroup.COUNTRY, " & _
"tblBSMappingGroup.ENTITY, tblBSMappingGroup.ACCOUNT_LABEL, " & _
"tblBSMappingGroup.GL_CATEGORY, qryAggregationRollingTotals.ACTUAL " & _
"FROM qryAggregationRollingTotals INNER JOIN tblBSMappingGroup ON " & _
"qryAggregationRollingTotals.GL_CATEGORY=tblBSMappingGroup.GL_CATEGORY"
Set qdfLinkedTables = dbsBalanceSheet.CreateQueryDef(strFileName , _
strSQLString)
'Exports to CSV
strOutputCSV1Path = strOutputFilePath + " - Grouping File.xls"
strMakeTableUpdateQuery = "SELECT qryAggregationGroupEntityMatching.ENTITY, " & _
"qryAggregationGroupEntityMatching.ACCOUNT_LABEL, qryAggregationGroupEntityMatching.ACTUAL " & _
"INTO tblExportCSV1 FROM qryAggregationGroupEntityMatching"
DoCmd.SetWarnings False
DoCmd.RunSQL (strMakeTableUpdateQuery)
strReplacementOutputString = "csv"
strNewOutputString = Replace(strOutputCSV1Path, _
strRemovedOutputString, strReplacementOutputString)
DoCmd.TransferText acExportDelim, "Standard Output", _
"tblExportCSV1", strNewOutputString, False
|
|
|
|
|
OK, one last time. A CSV file is NOT an Excel file, it does NOT contain formatting information and there is no way under the sun you are gong to make Excel put the fields in any format except its default one. The ONLY reason a csv file open in Excel is because of the file associations on your computer.
If you want, you could make Word the default association for a csv file - would you expect it to input and format the text then?
It does not matter what you do with decimal places in the csv file, Excel will format the column with its default settings.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
The question then is, how do I set it explicitly in Excel using code to decimal of 2 digits? I believe that it is something like:
Convert.Decimal(tblInputGroupFile.GL_AMOUNT, 2)
I know that this is not correct, but could you give me some idea as to how to convert it using an auxiliary function?
|
|
|
|
|
Please see the answers you have been given. YOU ARE NOT SETTING ANYTHING IN EXCEL, YOU ARE WRITING A FLAT FILE. For the last time, the problem is the default format for Excel is not 2 decimal places, and there is NO way you can set it from a csv file as it is NOT Excel
Bob
Ashfield Consultants Ltd
|
|
|
|
|
The Selection.NumberFormat function will do it in Excel.
E.g. Range("A1:A5").Select
Selection.NumberFormat = "0.00"
will format cells A1 to A5. To format all of column A use Columns("A:A").Select instead of Range.
This has to be a macro or VBA subroutine in Excel. If you use a subroutine you could trigger it with the the WorkBook_Open event.
|
|
|
|
|
|
I think query have to be something like this:
select table1.*,table2.*,table3.*<br />
from table1,table2,table3<br />
where table2.id = table1.id and table2.id = table3.id
Hope it will help you.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
|
You can do it like this:
select t1.*, t2.*, t3.*
from table1 t1
inner join table2 t2 on t2.table1_id = t1.id
inner join table3 t3 on t3.table2_id = t2.id
where t1.field1 = <something>...</something>
These are just standard joins.
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Do not delete messages after they are answered!
The idea is that people can find answers from these forums based on keywords in both questions and answers.
|
|
|
|
|
Mika Wendelius wrote: Do not delete messages after they are answered!
The idea is that people can find answers from these forums based on keywords in both questions and answers
This why, if possible, I'll highlight their inquiry so it shows up in the {so-and-so} wrote:. If it is in the post of the first person who answers, then the question pretty much stays there unless Chris gets a hamster to zap the entire thread.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
Good point. Personally I must learn to use that technique more often (started immediately to quote on a new thread  )
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Like Mika said, don't delete your message even if it has been answered. Others may want to chime in their thoughts/ideas later on, or learn from a question you have that was answered.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
hi,
I have created C++ application which create DSN with ODBC Microsoft text driver to use .csv file
as Database .
I want to change data type of some columns.So I tried
cmdstr = "ALTER TABLE table1 ALTER COLUMN RATE char(20)";
SQLExecDirect(hstmt, cmdstr, SQL_NTS);
But its give error as "syntax error in Alter Statement".
When we create DSN using ODBC wizard schema.ini file generate which contains the schema
information.But When I generate DSN by programmatically Schema.ini file not generate.
Pointer in this direction will be helpful for me.
Sunil
|
|
|
|
|
|
try using:
ALTER TABLE table1 modify COLUMN RATE char(20)
|
|
|
|
|
I wan to search sql server table to find any word contains some all the letter what should i do.
I'm using Asp.net vb to create web page. I create a text box I want to search for special letters.
I'm using sqldatasource to connect to sql server with the following procedure:
SELECT Name, Emai FROM Item WHERE (itemName = @itemName)
What should i do instead of (itemName = @itemName)
Thanks
|
|
|
|
|
If it is letters in a series (rather than any word containing any of these letters) you can use
WHERE ItemName like '%xyz%'
|
|
|
|
|
As in previous post use LIKE for simple pattern matching or if you need more logic for the search, use CONTAINS[^] predicate.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Thanks for this help.I'm trying apply it on the asp.net web page there was an error occur.
cannot use a contains or free text predicate on table or indexed view "item" because it is not full-text indexed
What should i do
thanks
|
|
|
|
|
y_mmohd wrote: cannot use a contains or free text predicate on table or indexed view "item" because it is not full-text indexed
therefore you can either create a full-text index on the column OR use the LIKE expression - as you have been told several times.
y_mmohd wrote: I'm trying apply it on the asp.net web page
is totally irrelevant, its your sql query that is wrong.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
|
I've got a query like this:
WITH CTE1 AS
(
SELECT * FROM TABLE WHERE A = @A
),
CTE2 AS
(
SELECT * FROM TABLE WHERE B = @B
)
SELECT * FROM
CTE1
CROSS JOIN
CTE2
The above query works fine for me and always returns cross join result of the two queries. The problem happens when one of those CTEs doesn't return any row and therefore the CROSS JOIN returns nothing as well. When only one of those CTEs return something, my expectation is to return the result of that CTE cross joined with null values. Do you know how I can achieve this purpose?
|
|
|
|
|
Since cross join doesn't have any 'OUTER' option, the result is always all combinations from all sources. If any of the sources have 0 rows the size of the result set is 0.
I think you could use simple outer joins and unions to get desired rows. Something like:
SELECT *
FROM Table A LEFT OUTER JOIN Table B ON 1=1
WHERE ...
UNION
SELECT *
FROM Table A RIGHT OUTER JOIN Table B ON 1=1
WHERE ...
However, I would re-check the design of the data model since normally the need to cross join is very rare and may indicate a design problem.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
