473,836 Members | 1,999 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

convert text data type

37 New Member
Hi - I use Access 2007. I have a two data fields that I would like to add together in a query and then have the query sum the data for the entire table. The problem is that one of the data fields has a data type of number and the other data field has a data type of text. The data filed with the text data type is a combo box with two fields on of which is a number and t he other is text. I tried to use the functions Val and Cint, but am not getting the correct results.

The data fields are Actual Weight3521 and Estimated Weight3521. The Estimated Weight 3521 is the text data type. First I have to put a value into the Actual Weight3521 if the user does not put in a value so I did this:

3521: IIf([Discard Species Haul Log]![Actual Weight3521]="",[Discard Species Haul Log]![Actual Weight3521],0)

which works, but when I try to make the Estimated Weight3521 a numeric value I wither get a 6 which doesn't make any sense or #error.
Thanks for any help
Jan 2 '10 #1
9 5384
8,834 Recognized Expert Expert
I'm having a little trouble deciphering this one, sillyr. Can you Upload the DB with some sample data for us to look at?
Jan 3 '10 #2
3,532 Recognized Expert Specialist
Well, I guess you need to start by getting the Text/Number thing straight. One has to assume that with the name [Estimated Weight3521] this field only holds digits (0-9) and possibly decimal points. If this is true, what you really need to do is to go into Design View for your underlying table and change the DataType from Text to Number.

Next, you need to understand how fields that don't have data entered into them are handled in Access. If a field is left blank in Access, it's value is Null, unless you've set a Default Value for the field either in the Table Def or in the form. It should be noted that when a Number Datatype field is added to a table, Access automatically assigns a Default Value of 0 (Zero) to it. This is the easiest way to ensure that a field has a value of Zero if nothing is entered.

Your code

IIf([Discard Species Haul Log]![Actual Weight3521]=""

is not checking to see if the field has been left blank, it's checking to see if [Actual Weight3521] has a Zero-Length String as its value, and that's not the same thing at all!

If a field is left blank in Access, if it has a value entered and then deleted or if the user simply enters spaces, its value is Null! Even if you enter a ZLS, by actually entering "", Access converts it back to Null, and that's what you need to check for, using

IsNull([Discard Species Haul Log]![Actual Weight3521])

Lastly, you need to look, I think, at the values you're assigning with you IIF() function. What you're currently saying is, basically, if

[Actual Weight3521]

is left blank, assign it that blank value, and if it has a value entered, assign it Zero! And I think this is probably backwards. A even simpler way to do this would be to use the Nz() function.

3521:Nz([Discard Species Haul Log]![Actual Weight3521], 0)

As a general note, it is a very bad practice to have object names with spaces in them! It requires that you use square brackets that around them when referring to them and adds clutter.

Welcome to Bytes!

Linq ;0)>
Jan 3 '10 #3
37 New Member
Thank you for your remarks. I changed the IIf to Nz, but for the other issue I cannot change the data type for the one object because the object has a combo box with 2 columns:
one is numeric (i.e. 5, 10, 25, 50, 75, 101) and the other is (1-5, 6-10, 11-25, 26-50, 51- 75, >101). I have tried that in the past, but then on the form the user gets an error message that they cannot input data bee wrong data type. That is why I wanted to convert the text data type to a number.

The data is in the table as
Actual Weight Estimated Weight

The user enters either the Actual Weight or the Estimated Weight

Jan 3 '10 #4
3,532 Recognized Expert Specialist
Obviously you can't change a field holding values like

51- 75

into a Numeric field by any means; how would you? The values are in no way, shape or form "numbers!'

And I still have no idea what the combobox has to do with anything here. Nor the non-numeric field, since the code you actually for for help with never mentions it!

Perhaps we could help more if you gave a clearer explaation of exactly what you're trying to accomplish here or if, as ADezii suggested earlier, you zipped up your file and attached to a post here.

Linq ;0)>
Jan 3 '10 #5
32,584 Recognized Expert Moderator MVP

Now may be a good time to go into how important a clearly presented question can be. Without wishing to sound critical, your question appears to have been thrown together in some haste. I cannot stress too much the benefit of phrasing and expressing your question clearly and accurately. As an experienced expert I can assure you that most difficulties I see on here are a result of mix-communication and misunderstandin g rather than the complicated nature of the problem itself.

I know this is not as easy as it sounds. Some have a lot more trouble with it than others, but I'm here to tell you that you can certainly reap visible benefits from spending time and effort on the forming of clearer questions. There is no-one who will penalise for poorly worded questions, but you are certainly likely to miss out on some help if it is too difficult to decipher or make sense of.

If you're ever unsure of whether or not it is clear then try :
Reading it back to yourself.
Asking a colleague or friend to read it who doesn't already understand your need.
Jan 3 '10 #6
3,080 Recognized Expert Specialist
From your text
The data filed with the text data type is a combo box with two fields on of which is a number and t he other is text.
I read that you use a multi value field in your table.

When the lookup (see field definition) points to a table the way to solve your problem is to place both tables in the query editor and to JOIN by the "bound" column.
Now you can sum the value from the JOINed table.

For entered values in a list I can't find a solution, other than the creation of a table and to change the lookup type into "table".

Clear enough?
Jan 4 '10 #7
37 New Member
Sorry for not making myself clear - sometimes to me it seems like I am stating things clearly, but that is obviously not the case. I attached an excel spreadsheet with an example of the data in the table. I also included the SQL statement for the query.
Expand|Select|Wrap|Line Numbers
  1. SELECT [Additional Species EFP Query].[Actual Weight3521], [Additional Species EFP Query].[Estimated Weight3521]
  2. FROM [Additional Species EFP Query];
What I would like to do is add these two fields together, but because [Estimated Weight3521] is a text field I get data type mismatch in criteria expression. I cannot change the data type of [Estimated Weight3521].
Attached Files
File Type: zip example data for Bytes.zip (3.9 KB, 135 views)
Jan 18 '10 #8
3,080 Recognized Expert Specialist
When the text field should be added you can use the Val() function, but this will only extract the "leading" numbers. Any space or non numeric character will "interrupt" the number.
In the sample excel I only see "empty" text fields and there should return 0.

Jan 18 '10 #9
32,584 Recognized Expert Moderator MVP
Are you looking for something like :
Expand|Select|Wrap|Line Numbers
  1. SELECT [Actual Weight3521], 
  2.        [Estimated Weight3521],
  3.        Val([Actual Weight3521])+Val([Estimated Weight3521]) AS TotWeight
  5. FROM [Additional Species EFP Query]
Jan 18 '10 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

by: SD | last post by:
Hi, This is driving me nuts, I have a table that stores notes regarding an operation in an IMAGE data type field in MS SQL Server 2000. I can read and write no problem using Access using the StrConv function and I can Update the field correctly in T-SQL using: DECLARE @ptrval varbinary(16) SELECT @ptrval = TEXTPTR(BITS_data)
by: Andrew V. Romero | last post by:
At work we have an excel file that contains the list of medications and their corresponding strengths. I would like to save the excel file as a text list and paste this list into a javascript function and have JS put this into an array. Then JS would use this array to create a selection list which displays only the names of the drugs. When the user selections one of the drugs, another selection list will be loaded with the avaiable...
by: manmit.walia | last post by:
Hello All, I am stuck on a conversion problem. I am trying to convert my application which is written in VB.NET to C# because the project I am working on currently is being written in C#. I tried my best to convert it, but somehow the app does not work. I am also not getting any errors when I complie thus, letting me know I am on the write track. Basically what I want to do is edit,add,delete, and update an XML file in a DataGrid. Below...
by: 2redline | last post by:
I am trying to write a query in access that will pull results that are in the database in a text field and convert to where I can get a average including decimals. Any Ideas? -----------------------Start SQL-------------------------------------- SELECT AVG(CAST(dbo_sur_response_answer.answer_text AS int)) AS avg_correct FROM (dbo_sur_response_answer INNER JOIN dbo_sur_subitem
by: Learner | last post by:
Hello, I am trying to store the data entered in a webform in the database. I have few Int and one SmallDateTime filed in my table in SQL Server 2005 database. I have made a storedproc to store the values. My parameters in the database are like this @DealerShipID int, @ReturnDate smallDateTime,
by: priyakollu | last post by:
i have developed code in jsp but the problem is i need to convert this to asp.........pleasehelp me regarding this im sending code which i've developed....with file names.......... code.jsp <%@ page import = "java.util.*" %> <%@ page import = "java.io.*" %> <%@ page import = "java.lang.*" %> <%@ page import = "org.apache.commons.fileupload.*" %> <%@ page import = "java.sql.*" %> <%
by: Justin | last post by:
Hi all, i need some help over here... i found the solution to export file from mysql db into *.csv. but is there anyway to convert the contents into *.doc and save in my webserver and providing a link for the end users to download the word file? FYI, the database records are obtained by end users submitting the forms themselve and i saved it in my db... Thanx.
by: mrajanikrishna | last post by:
Hi Friends, I am accepting a number from the user entered in a textbox. I want to assign to a variable in my code and assignt this to that variable. double num1 = (double)txtNum1.text; this produced an error
by: sonu | last post by:
hey good morning ...... how to convert a video file in .flv format in php for linux hosting......is there any package whis provide this facility . Can i use ffmpeg for linux hosting project.......... plz help me.. thanks & regards Prabhat
Debadatta Mishra
by: Debadatta Mishra | last post by:
Introduction In this article I will provide you an approach to manipulate an image file. This article gives you an insight into some tricks in java so that you can conceal sensitive information inside an image, hide your complete image as text ,search for a particular image inside a directory, minimize the size of the image. However this is not a new concept, there is a concept called Steganography which enables to conceal your secret...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.