Query Data Type Conversion issue with Criteria

[The Frog]

Hi Everyone,

I have a small problem that doesnt seem to make any sense. I am using
Access 97, and have a query that selects data from a text field,
converts it to type Lng. This seems to work fine. When I try and apply
a criteria for filtering is get the data type mismatch rubbish that
Access likes to trow at you.

Here is the SQL:

SELECT CLng(Trim(Left(RawImport.PACKSIZE,Len(RawImport.PA CKSIZE)-2)))
AS SIZE
FROM RawImport
WHERE SIZE <= 500

have also tried:

WHERE CLng(Trim(Left(RawImport.PACKSIZE,Len(RawImport.PA CKSIZE)-2)))
<= 500

Any ideas on what I am doing wrong?

Cheers

The Frog

[Allen Browne]

If PackSize is null, the CLng() will fail. Use Nz() or IIf().

Similarly, if PackSize contains non-numeric characters, CLng() will fail.
Use Val().

Try something like this:

CLng(Val(IIf (Len(RawImport.PACKSIZE) 2, Left(RawImport.PACKSIZE,
Len(RawImport.PACKSIZE) - 2), "0")))

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"The Frog" <Mr************@googlemail.comwrote in message
news:11**********************@g4g2000hsf.googlegro ups.com...

Hi Everyone,

I have a small problem that doesnt seem to make any sense. I am using
Access 97, and have a query that selects data from a text field,
converts it to type Lng. This seems to work fine. When I try and apply
a criteria for filtering is get the data type mismatch rubbish that
Access likes to trow at you.

Here is the SQL:

SELECT CLng(Trim(Left(RawImport.PACKSIZE,Len(RawImport.PA CKSIZE)-2)))
AS SIZE
FROM RawImport
WHERE SIZE <= 500

have also tried:

WHERE CLng(Trim(Left(RawImport.PACKSIZE,Len(RawImport.PA CKSIZE)-2)))
<= 500

Any ideas on what I am doing wrong?

Cheers

The Frog

[lyle]

On Sep 20, 4:11 am, The Frog <[e-mail]:

This seems to work fine. When I try and apply
a criteria for filtering is get the data type mismatch rubbish that
Access likes to trow at you.

Access Jet and VBA stand on their heads to try to accept the inane,
the stupid, the lazy, and the careless from their users, much more so
than any other application with which I am familiar, and to an extent
that seriously degrades their suitability as components of a rigorous
and efficient application development platform.

Access has never thrown rubbish at me, making it entirely unsuitable
for inclusion in my table of temperamental redheads.

Rubbish In =Rubbish Out

[The Frog]

Hi again Allen and Lyle,

I have tried the IsNumeric, and each record is coming up with a -1
(True) for the corresponding calculated field. ( IsNumeric(Size) ).

Lyle, I do understand the Rubbish In =Rubbish Out scenario, that is
not really the issue here. I am simply trying to get to the bottom of
this.

This doesnt seem to make any sense! All the returned values are
numeric, and yet I am unable to specify a criteria to limit the
returned rows. By all reason this should be working. It is actually
coming up with the data type error after the query appears to have
run, and then after the query has run and you can see the result grid
(with what appear to be valid results) the error appears and then all
the values change to #Name? .

Could I be missing a service pack or something?

Cheers

The Frog

[Fred Zuckerman]

"The Frog" <Mr************@googlemail.comwrote in message
news:11**********************@k79g2000hse.googlegr oups.com...

Hi again Allen and Lyle,

I have tried the IsNumeric, and each record is coming up with a -1
(True) for the corresponding calculated field. ( IsNumeric(Size) ).

Lyle, I do understand the Rubbish In =Rubbish Out scenario, that is
not really the issue here. I am simply trying to get to the bottom of
this.

This doesnt seem to make any sense! All the returned values are
numeric, and yet I am unable to specify a criteria to limit the
returned rows. By all reason this should be working. It is actually
coming up with the data type error after the query appears to have
run, and then after the query has run and you can see the result grid
(with what appear to be valid results) the error appears and then all
the values change to #Name? .

Could I be missing a service pack or something?

Cheers

The Frog

Is "Size" a reserved word?
Try changing the fieldname in your query stmt to "...As lngSize..."
Just a guess,
Fred Zuckerman

[The Frog]

Hi Fred,

Thankyou for your suggestion. I have tried renaming the field to
"Blap" - which I am fairly certian is not a reserved word, but
unfortunately it doesnt seem to make any difference. Thanyou for
taking the time to try and help, I do appreciate it.

Cheers and Thanks

The Frog

[The Frog]

Hi Everyone,

I have noticed that it is possible to construct another calculated
column based on the first, with a simple IIF statement that does the
evaluation I seek, and returns true or false. This works and I get the
appropriate True or False value in the column. If I then try and apply
a criteria to this column I get the same Data Type Mismatch error as
before only that this time the query doesnt show any results at
all.....Access never seems to leave the Query Designer. Wasnt sure if
it was important, just though I'd mention it.

Cheers

The Frog

[lyle]

On Sep 20, 4:37 am, "Allen Browne" <[e-mail]:

If PackSize is null, the CLng() will fail. Use Nz() or IIf().

Similarly, if PackSize contains non-numeric characters, CLng() will fail.
Use Val().

Try something like this:

CLng(Val(IIf (Len(RawImport.PACKSIZE) 2, Left(RawImport.PACKSIZE,
Len(RawImport.PACKSIZE) - 2), "0")))

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org."The Frog" <[e-mail] in message

news:11**********************@g4g2000hsf.googlegro ups.com...

Hi Everyone,

I have a small problem that doesnt seem to make any sense. I am using
Access 97, and have a query that selects data from a text field,
converts it to type Lng. This seems to work fine. When I try and apply
a criteria for filtering is get the data type mismatch rubbish that
Access likes to trow at you.

Here is the SQL:

SELECT CLng(Trim(Left(RawImport.PACKSIZE,Len(RawImport.PA CKSIZE)-2)))
AS SIZE
FROM RawImport
WHERE SIZE <= 500

have also tried:

WHERE CLng(Trim(Left(RawImport.PACKSIZE,Len(RawImport.PA CKSIZE)-2)))
<= 500

Any ideas on what I am doing wrong?

Cheers

The Frog

Len(Null) is Null?

[lyle]

On Sep 20, 7:48 am, The Frog <[e-mail]:

Hi again Allen and Lyle,

I have tried the IsNumeric, and each record is coming up with a -1
(True) for the corresponding calculated field. ( IsNumeric(Size) ).

Lyle, I do understand the Rubbish In =Rubbish Out scenario, that is
not really the issue here. I am simply trying to get to the bottom of
this.

This doesnt seem to make any sense! All the returned values are
numeric, and yet I am unable to specify a criteria to limit the
returned rows. By all reason this should be working. It is actually
coming up with the data type error after the query appears to have
run, and then after the query has run and you can see the result grid
(with what appear to be valid results) the error appears and then all
the values change to #Name? .

Could I be missing a service pack or something?

Cheers

The Frog

Nulls, Strings, Variants, and SQL are very involved.
JET SQL treats everything as a variant string and then coerces it into
whatever it thinks is wanted. This is why it so frequently errors when
the first value in a field is null, or can be misinterpreted.

If I were doing this I would probably use a UDF (air code example
below) that returned zero on error. Later I might try to translate it
into an inline expression which could be used in the sql string but
until that time I'd just use SIZE(RawImport.PackSize)

Public Function Size(ByVal PackSize As Variant) As Long
Dim PackSizeString As String
On Error GoTo SizeErr
PackSizeString = Nz(PackSize, "")
Size = CLng(Left(PackSize, Len(PackSize) - 2))
SizeExit:
Exit Function
SizeErr:
Resume SizeExit
End Function

[The Frog]

Lyle,

Thankyou for getting me on the right track. It was absolutely the null
handling issue. Even though the actual selected data did not have a
null in it when looking at the query results, testing this with a UDF
shows that the query was assessing each record in the entire table and
not just the ones I was 'selecting' (I had some other criteria on
other columns to limit the results).

This then makes me ask the question: Would this be better solved with
a subquery to retreive the desired subset from the table, then perform
the calculated field in the main query? Just a passing thought..., I
was trying to not use UDF's if I could avoid it.

Thankyou for your guidance, it is very much appreciated. I gets me
through this hurdle for now. I am still curious to discover a pure SQL
solution for this.

The code I used is below:

Function SizeofPack(PackSize As Variant) As Integer

Dim tmp As String

If IsNull(PackSize) Then Exit Function

tmp = IIf(InStr(1, PackSize, "X", vbTextCompare), Left(Mid(PackSize,
4, Len(PackSize) - 4), 3), Left(PackSize, Len(PackSize) - 3))

SizeofPack = CInt(tmp)

End Function

I set up the string handling for this specific query, and will have to
make different UDF's for the others that have the same requirements
but different data to work with.

Cheers

[The Frog]

Hi Lyle and Allen,

I have answered it in a pure SQL form. The trick was to include
another IIF statement as follows:
IIF(not IsNull(RawImport.PackType), string handling goes here)

I could then place the criteria on the calculated column and it worked
like a charm

Just thought I would share. I am also going to have a crack at the
SubQuery approach but wont have time till the weekend.

Thankyou both for all your help, I really appreciate it. Without you
guys I would have been completely lost.

Kind Regards and Many Thanks

The Frog

[lyle]

On Sep 20, 10:37 am, The Frog <[e-mail]:

Lyle,

Thankyou for getting me on the right track. It was absolutely the null
handling issue. Even though the actual selected data did not have a
null in it when looking at the query results, testing this with a UDF
shows that the query was assessing each record in the entire table and
not just the ones I was 'selecting' (I had some other criteria on
other columns to limit the results).

This then makes me ask the question: Would this be better solved with
a subquery to retreive the desired subset from the table, then perform
the calculated field in the main query? Just a passing thought..., I
was trying to not use UDF's if I could avoid it.

Thankyou for your guidance, it is very much appreciated. I gets me
through this hurdle for now. I am still curious to discover a pure SQL
solution for this.

The code I used is below:

Function SizeofPack(PackSize As Variant) As Integer

Dim tmp As String

If IsNull(PackSize) Then Exit Function

tmp = IIf(InStr(1, PackSize, "X", vbTextCompare), Left(Mid(PackSize,
4, Len(PackSize) - 4), 3), Left(PackSize, Len(PackSize) - 3))

SizeofPack = CInt(tmp)

End Function

I set up the string handling for this specific query, and will have to
make different UDF's for the others that have the same requirements
but different data to work with.

Cheers

I never use the IIf VBA function.
In VBA this raises an error:
IIf(1 = 1, 7, 7 / 0)
although our expectations are that it should not, that is that the
second (false) part of the expression should not be evaluated as 1 = 1
always..

But in SQL it does not raise an error. The Iif behaves as we expect it
to.

I do not care to use the same syntax for (ostensibly but not really)
the same function which operates one way in VBA and another way in
SQL.

Similarily I do not use IsNull.

IsNull(Null) returns true.

So does (IsNull(Not Null)) because virtually any function or operator
acting on Null returns null.

UDFs help me to control what's going on. I always go

If expression

--

Else

--

it's fast and clear and almost fool proof.

Null is not Null. Null is not ANYTHING!

Blah blah blah ... shut up lyle!

[Sky]

That is not pure SQL, since your are using IsNull().

Try: IIF(RawImport.PackType Is Not Null, etc.)

- Steve

"The Frog" <Mr************@googlemail.comwrote in message
news:11********************@57g2000hsv.googlegroup s.com...

Hi Lyle and Allen,

I have answered it in a pure SQL form. The trick was to include
another IIF statement as follows:
IIF(not IsNull(RawImport.PackType), string handling goes here)

I could then place the criteria on the calculated column and it worked
like a charm

Just thought I would share. I am also going to have a crack at the
SubQuery approach but wont have time till the weekend.

Thankyou both for all your help, I really appreciate it. Without you
guys I would have been completely lost.

Kind Regards and Many Thanks

The Frog

[David W. Fenton]

lyle <ly************@gmail.comwrote in
news:11**********************@n39g2000hsh.googlegr oups.com:

IsNull(Null) returns true.

So does (IsNull(Not Null)) because virtually any function or
operator acting on Null returns null.

Seems to me that you're completely misinterpreting the meaning of
Null. Null means "unknown" and any operation on "unknown" must
propagate the "unknown" or it is doing something wrong.

Confusing "Not Null" with a non-null value seems a pretty basic
mistake.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Tom van Stiphout]

On Thu, 20 Sep 2007 13:46:42 -0000, lyle <ly************@gmail.com>
wrote:

Except that you want to be careful about using error handlers in
functins called from queries. They - often unnecessarily - slow things
down or return tedious errors.

-Tom.

>On Sep 20, 7:48 am, The Frog <[e-mail]:

>Hi again Allen and Lyle,

I have tried the IsNumeric, and each record is coming up with a -1
(True) for the corresponding calculated field. ( IsNumeric(Size) ).

Lyle, I do understand the Rubbish In =Rubbish Out scenario, that is
not really the issue here. I am simply trying to get to the bottom of
this.

This doesnt seem to make any sense! All the returned values are
numeric, and yet I am unable to specify a criteria to limit the
returned rows. By all reason this should be working. It is actually
coming up with the data type error after the query appears to have
run, and then after the query has run and you can see the result grid
(with what appear to be valid results) the error appears and then all
the values change to #Name? .

Could I be missing a service pack or something?

Cheers

The Frog

Nulls, Strings, Variants, and SQL are very involved.
JET SQL treats everything as a variant string and then coerces it into
whatever it thinks is wanted. This is why it so frequently errors when
the first value in a field is null, or can be misinterpreted.

If I were doing this I would probably use a UDF (air code example
below) that returned zero on error. Later I might try to translate it
into an inline expression which could be used in the sql string but
until that time I'd just use SIZE(RawImport.PackSize)

Public Function Size(ByVal PackSize As Variant) As Long
Dim PackSizeString As String
On Error GoTo SizeErr
PackSizeString = Nz(PackSize, "")
Size = CLng(Left(PackSize, Len(PackSize) - 2))
SizeExit:
Exit Function
SizeErr:
Resume SizeExit
End Function

[The Frog]

Thankyou for that Sky,

Didnt think of this. Much appreciated. Works like a charm :-)

Cheers

The Frog

[The Frog]

Thanks Guys for the guidance once again.

I understand the potential benefit of using a UDF, and for debugging
purposes its certainly something I have found useful. I am one who
tends to want to lean more towards the 'pure' SQL route simply for
portability's sake. Although there are differences between
implementations of SQL on different platforms, I have found it easier
to tweak the SQL than to re-write a stack of UDF's if DB platform has
to change. It also opens the sharing possibilities up considerably for
distributing the workload (at least in my little corner of the world).

I thankyou all for the time and help that you have provided, as well
as the lively discussion. I cant tell you how much I do appreciate the
efforts of all who contribute. If you are ever in Germany and need a
beer then drop me a line :-)

Cheers and Thanks

The Frog