Access to SQL Server conversion - format number question

In Access the following sql
select Format(0.12345,"#.00###") from tblA
returns
0.12345
select Format(0.123,"#.00###") from tblA
returns
0.123
In SQL Server
select convert(decimal(6,5),0.12345)
returns
0.12345
But, select convert(decimal(6,5),0.123)
returns
0.12300 instead of 0.123
Is there a way in SQL Server to convert the number so that if the number of
character after decimal point is 2, show only those 2 without training 0's,
and if the number of character after decimal point is 5 shows all the 5
numbers ?
Thanks.Paul fpvt2 wrote:
> In Access the following sql
> select Format(0.12345,"#.00###") from tblA
> returns
> 0.12345
> select Format(0.123,"#.00###") from tblA
> returns
> 0.123
> In SQL Server
> select convert(decimal(6,5),0.12345)
> returns
> 0.12345
> But, select convert(decimal(6,5),0.123)
> returns
> 0.12300 instead of 0.123
> Is there a way in SQL Server to convert the number so that if the number o
f
> character after decimal point is 2, show only those 2 without training 0's
,
> and if the number of character after decimal point is 5 shows all the 5
> numbers ?
> Thanks.
Your client application determines how numbers are displayed, not SQL
Server. You should refer to the formatting capabilities of whatever
client application or language you are using. Unfortunately you didn't
specify that so we can't easily help you with it.
To have control over the formatting from SQL you'd have to return a
string rather than a number, which is not usually recommended.
David Portas
SQL Server MVP
--|||Thank you.
> To have control over the formatting from SQL you'd have to return a
> string rather than a number, which is not usually recommended.
I am sorry, I posted the message incorrectly, it should be string, not a
number, like the following:
in Access:
select Format('0.123',"#.00###") from tblA
returns
0.123
in SQL Server:
But, select convert(decimal(6,5),'0.123')
returns
0.12300 instead of 0.123
The language we are using is VB, but unfortunately the VB application
expecting the result of the query already formatted.
Is there a way to do the above in SQL Server ?
Thank you.
"David Portas" wrote:

> Paul fpvt2 wrote:
> Your client application determines how numbers are displayed, not SQL
> Server. You should refer to the formatting capabilities of whatever
> client application or language you are using. Unfortunately you didn't
> specify that so we can't easily help you with it.
> To have control over the formatting from SQL you'd have to return a
> string rather than a number, which is not usually recommended.
> --
> David Portas
> SQL Server MVP
> --
>

Access to SQL conversion!

Hi Everyone,

To start off, can I just say that I know very little about SQL databases but I have a MS access database which we are outgrowing and I think we will need to make some changes. I was wondering if some one could offer some advice. There are many programs available for upsizing to SQL but I presume these are for the back end only, is this correct? Is upsizing a bad idea or a very difficult one?

I was also wondering about keeping my Access front end and connecting it to a SQL back end, is this a good idea?

Any alternative suggestions would be appreciated.

Regards

Nora.It depends on a lot of things...

It depends how the app is written...more than likely your form controls are all open record sets...

What's your data access model?

That's make access front end to sql server crawl...

How normalized is your data, if you have to rewite it'd be good to have a good data model...

How much data are we talking about? How big is Access in mb?

How many users?

Do you know how much sql server costs?|||How 'bout Visual Basic on a SQL Server back end? We are converting, albeit at a snails pace, our processes using Access backend to SQL Server. This is a different(another job) than the other issue I posted on re: Read Only in SQL Server Stored procedures. Here at least, I have full privileges, even updates on table than my a.m. job. Is there a good tutorial page on VB with SQL Server? I know both to some extent though my VB is rusty.

ddave|||I have upgraded Access apps to SQL Server a few times and been surprised how much the built-in Upgrade Wizard can do correctly for you. I'd give it a try. Note that you don't want to convert to an Access 'Project' file if you need to have front-end tables as well as a back-end database. You can't use front-end tables in the .adp files.

Depending on the complexity of your relationships in the DB you can get almost everything converted over with the Wizard. Some basic testing will show you where things flopped. You won't have too much trouble figuring out how to re-create things in SQL Server--it's very user-friendly.

If you will keep your front end in Access I highly recommend the enormous Access 2000 Developer's Handbook, Volume 2: Enterprise Edition (Litwin, Getz & Gilbert) Hopefully they have a 2002 version out by now but the 2000 version saved my you-know-what many times and gives great info on how to work with SQL Server from Access.

We have several large production apps running in Access to SQL Server. In terms of SQL Server and Access playing nicely, they generally do. Using the upsizing wizard will help ensure that the back end is set up to talk to Access properly.

Good Luck.

Access to SQL conversion

I'm trying to convert an Access Query to SQL. I am using a "Mid Statement" to select a portion of a value:

Mid([ITEM_NUMBER],2,7)

When I try to parse this statement in SQL I always get an error "Does not recognize Mid as a valid procedure."

Is there an SQL statement I could use to accomplish this?the sql server equivalent is the SUBSTRING function

instead of
Mid([ITEM_NUMBER],2,7)

use
substring([ITEM_NUMBER],2,7)

Access Table conversion

how difficult is it to convert access tables to SQL that can continue to be
linked to an Access GUI.
Mike,
trivial...there is an Access Upsizing tool in Access that will convert
the data to MS SQL and then you can change your app to read these
tables via ODBC. If you have hard coded access to the tables in your
access app you need to change them to recordsets accessed via
ODBC...that's about it...when we did this on a small system many
years ago we got it done by a fresh uni grad and he only took a couple
of days....
Peter

Access Table conversion

how difficult is it to convert access tables to SQL that can continue to be
linked to an Access GUI.Mike,
trivial...there is an Access Upsizing tool in Access that will convert
the data to MS SQL and then you can change your app to read these
tables via ODBC. If you have hard coded access to the tables in your
access app you need to change them to recordsets accessed via
ODBC...that's about it...when we did this on a small system many
years ago we got it done by a fresh uni grad and he only took a couple
of days....
Peter

Access SQL Conversion Issues?

Hi there, apologies if i have entered this into the wrong forum.

I am currently building an application using microsoft access and coding in VBA (what i can!)

When i have got it working as i like after user testing i would like to create an MDE file to distribute to users and i would like to convert the access database held on a file server to a SQL server but have no experience of doing this.

In what order would i do this and would i suffer a loss of functionality in changing from access to SQL i.e. would some of the VBA code not work in SQL?

Thanks for your time,

Rhys.

Yes, you are right, if you do not use the GUI "only", you *could* get into problems after migrating. If you already know that you will switch to the SQL Server option, you should build your application directly on top of that.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Hello

Have you tried importing the Access database using SQL Server Integration Services (or Data Transformation Services if you use SQL Server 2000)?
I'm not sure if it works with MDE files but I would give it try.

Let us know if it works

Worf

|||

one thing you have to watch out are the stored procedure.

in access it looks like this

select * from employees where employee_id=?

while in SQL server it looks like this

Select * from employees where employee_id=@.employeeid

Access query conversion

I have this query:
SELECT Trim(Left([Display Name],InStr([Display Name],",")-1))+Trim(Mid([Display Name],InStr([Display Name],",")+2,1)) AS UserName, dbo_Employee.[Display Name]
FROM dbo_Employee;
Works good in access, but when trying to put it un sql server, it comes up with errors.
I know mid is substring, I just don't know what trim and instr are in mssql.

Any ideas?TRIM->LTRIM and RTRIM
MID->SUBSTRING
INSTR->CHARINDEX

All these functions are documented in SQL Server Books Online.|||Thanks for the quick response. Where can I find SQL Server Books Online?|||http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

It is already installed if you have SQL Server client installed.