convert text data type

[sillyr]

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

[ADezii]

I'm having a little trouble deciphering this one, sillyr. Can you Upload the DB with some sample data for us to look at?

[missinglinq]

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)>

[sillyr]

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

[missinglinq]

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]

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]

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?

[sillyr]

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]

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]

Are you looking for something like :

SELECT [Actual Weight3521], 
       [Estimated Weight3521],
       Val([Actual Weight3521])+Val([Estimated Weight3521]) AS TotWeight
       
FROM [Additional Species EFP Query]