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
9 5377 ADezii 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?
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)>
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
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)> NeoPa 32,577
Recognized Expert Moderator MVP
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 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.
nico5038 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?
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. - SELECT [Additional Species EFP Query].[Actual Weight3521], [Additional Species EFP Query].[Estimated Weight3521]
-
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
nico5038 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.
Nic;o)
NeoPa 32,577
Recognized Expert Moderator MVP
Are you looking for something like : - SELECT [Actual Weight3521],
-
[Estimated Weight3521],
-
Val([Actual Weight3521])+Val([Estimated Weight3521]) AS TotWeight
-
-
FROM [Additional Species EFP Query]
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
|
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: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
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: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
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: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |