Query criteria Type Mismatch

[wolfm]

I have a calculated field (Shortage Qty) in my main table [Load Lists]. (Shortage Qty)=(Returned Qty)-(Sent Qty). This works great. It calculates my shortage (what I sent out vs what I got back). I was surprised, considering my sent and returned qtys are both formatted as text.


Now, I want to have a query show only the negative numbers in (Shortage Qty), to isolate shortages only. Problem is, when I enter the expression "<0", it gives me a type mismatch error. (Shortage Qty) is set to be a calculated field type, with an integer result.


Is there a way around this, without converting (Returned Qty) & (Sent Qty) to numeric types? I really don't want to do that, as some of my imported data contains text, and changing these fields to numeric throws me a bunch of errors with other parts of my database.

I would attached a sample, but my database is huge!

Any help would be appreciated! Thanks.

[Bob Fitz]

Try:
CInt((Shortage Qty)=(Returned Qty)-(Sent Qty))

[John_G]

Wolfm -

When you entered "<0" as the criteria, did you use the quotation marks, or are you just using them in this discussion? If you did use them, that is your error - just put <0 as the criteria, without quotes.

John

[wolfm]

Try:
CInt((Shortage Qty)=(Returned Qty)-(Sent Qty))

Do I use this expression in my query, or in my original table containing the value?
When I use CInt([Shortageqty]=[ReturnedQty]-[Qty]) in my query, I get the error:
“Invalid Use of Null”

When I use CInt([ReturnedQty]-[Qty]) or CInt( [Shortageqty] = [ReturnedQty] - [Qty] ) in my calculated field in my original table, I get the error:
“The expression CInt([ReturnedQty]-[Qty]) cannot be used in a calculated column”

Thanks!

[John_G]

You have to apply the CInt function to each value individually, I think.

Try it like this: CInt([ReturnedQty]) - CInt([Qty])

If either [ReturnedQty] or [Qty] can be Null, use this:

CInt(Nz([ReturnedQty],"0")) - CInt(Nz([Qty],"0"))


John

[wolfm]

John-- When I just enter <0, it says "type mismatch". I have tried with and without quotation marks.

[John_G]

Wolfm -

Using the Cint / Nz to force the expression to integer type should fix that. If it doesn't, can you post the SQL of the query?

One other thing you can try is to do the calculation in the query, rather than use the calculated field from the table, with the expression for the query field being:

CInt(Nz([ReturnedQty],"0")) - CInt(Nz([Qty],"0")) (as before)


John

[wolfm]

When I tried this, I get "Type Mismatch"

"One other thing you can try is to do the calculation in the query, rather than use the calculated field from the table, with the expression for the query field being:

CInt(Nz([ReturnedQty],"0")) - CInt(Nz([Qty],"0")) (as before)"

[wolfm]

You have to apply the CInt function to each value individually, I think.

Try it like this: CInt([ReturnedQty]) - CInt([Qty])

If either [ReturnedQty] or [Qty] can be Null, use this:

CInt(Nz([ReturnedQty],"0")) - CInt(Nz([Qty],"0"))


John

When I try this in the original field in my table, it says I cannot use Cint in a calculated field.
When I try in the query it says “Invalid Use of Null”

[wolfm]

Sample definitions.accdb

Wolfm -

Using the Cint / Nz to force the expression to integer type should fix that. If it doesn't, can you post the SQL of the query?

One other thing you can try is to do the calculation in the query, rather than use the calculated field from the table, with the expression for the query field being:

CInt(Nz([ReturnedQty],"0")) - CInt(Nz([Qty],"0")) (as before)


John

My data file is attached, less records.

[Bob Fitz]

Can you post a copy in A2003 mdb format.

[wolfm]

Can you post a copy in A2003 mdb format.

Acess will not allow me to convert to mdb. It says database requires newer features.
Anyway, attached is a reduced version with some data, and the relevant table and query. All I really need to do is have the query filter out records that contain zero or positive numbers in the shortage qty field. In other words, show only items with a shortage, shown as a negative number. Thank you!Sample definitions.mdb.accdb

[Bob Fitz]

Acess will not allow me to convert to mdb. It says database requires newer features.
Anyway, attached is a reduced version with some data, and the relevant table and query. All I really need to do is have the query filter out records that contain zero or positive numbers in the shortage qty field. In other words, show only items with a shortage, shown as a negative number. Thank you!Sample definitions.mdb.accdb

The db you attached is not A2003 format

[John_G]

"When I try in the query it says “Invalid Use of Null” "

That's odd - the expression I used should prevent that error. Is there something else in the query that might be causing the error? Can you post the SQL of the query?

John

[wolfm]

Will this work?

SELECT [Load Lists].Qty, [Load Lists].Description, [Load Lists].[Job Name], [Load Lists].[Job #], [Load Lists].DateOut, [Load Lists].Trackable, [Load Lists].DateBack, [Load Lists].ReturnedQty, [Load Lists].ShortageqtyFROM [Load Lists]
WHERE ((([Load Lists].Trackable)=1))
ORDER BY [Load Lists].Shortageqty;