473,320 Members | 1,876 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

convert text data type

37
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 5340
ADezii
8,834 Expert 8TB
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
missinglinq
3,532 Expert 2GB
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
sillyr
37
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
1-5
2
>101
6
26-75

The user enters either the Actual Weight or the Estimated Weight

Thanks
Jan 3 '10 #4
missinglinq
3,532 Expert 2GB
Obviously you can't change a field holding values like

1-5
6-10
11-25
26-50
51- 75
>101

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
NeoPa
32,556 Expert Mod 16PB
Sally,

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 misunderstanding 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
nico5038
3,080 Expert 2GB
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
sillyr
37
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].
Thanks
Attached Files
File Type: zip example data for Bytes.zip (3.9 KB, 134 views)
Jan 18 '10 #8
nico5038
3,080 Expert 2GB
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.

Nic;o)
Jan 18 '10 #9
NeoPa
32,556 Expert Mod 16PB
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
  4.  
  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

1
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...
5
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...
5
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...
5
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? ...
2
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...
1
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 <%@...
6
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...
3
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; ...
5
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...
0
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...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.