Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 20th, 2004, 03:03 PM
Authorized User
 
Join Date: Dec 2004
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default Re: type conversion fail, need help!!

I have a table with 4 columns: yr1(type:text), yr2(text), yr3(text), avg(number); I tried to calculate the value of "avg" by using the following query:
    UPDATE tbl_test SET avg = Val((yr1 + yr2 + yr3)/3);
The Access database couldn't update all the records. Does anyone tell me why I failed? Thanks so much!


flyfish
__________________
flyfish
 
Old December 20th, 2004, 03:27 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

It looks like you are trying to calculate a number using text values, which would result in a type mismatch error.

You need field types like this:
yr1(number), yr2(number), yr3(number), and then store the average in a query. At least it is a number too.

You should be able to convert them all to numbers.

mmcdonal
 
Old December 20th, 2004, 03:42 PM
Authorized User
 
Join Date: Dec 2004
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I tried to change the field type into number, but I got a error message reminding me I will lost some my data in corresponding field.

flyfish
 
Old December 20th, 2004, 04:37 PM
Friend of Wrox
 
Join Date: Oct 2004
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Is there a chance that some of of the values might contains alphabet characters? If so, that will cause errors no matter what you do. You could make a copy of the table, and then try the conversion, and see if there is any data that is actually lost. Or if you want to get fancy, you could export the table to Excel, make sure that all the values are numeric and then import them into a new table in Access.

Mike

Mike
EchoVue.com
 
Old December 21st, 2004, 10:31 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

The message that you will/may lose data if you convert the field type is a default message. You will get that message whether you will actually lose data or not.

Sort the field and see if you get letters coming to the top (z to a). If not, then convert and don't worry about the lost data message. Or take echovue's advice about a backup table.



mmcdonal





Similar Threads
Thread Thread Starter Forum Replies Last Post
Conversion from type 'DBNull' to type 'String' is GailCG ASP.NET 2.0 Basics 5 February 22nd, 2007 03:12 PM
Conversion from type 'DBNull' to type 'Boolean' is steve35719 VB Databases Basics 4 June 29th, 2006 06:13 PM
Its a problem about type conversion subhasis.chakraborty General .NET 0 May 6th, 2005 10:50 AM
Help with data type conversion please androoo Pro VB.NET 2002/2003 0 November 29th, 2004 09:06 AM
Data Type Conversion owain SQL Language 5 October 31st, 2003 12:31 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.