使用条件查询数据类型转换问题 [英] Query Data Type Conversion issue with Criteria
问题描述
大家好，
我有一个小问题似乎没有任何意义。我正在使用
Access 97，并且有一个从文本字段中选择数据的查询，
将其转换为Lng类型。这似乎工作正常。当我尝试申请
a过滤条件是获得数据类型不匹配的垃圾
Access喜欢盯着你。
这是SQL：
SELECT CLng（修剪（左（RawImport.PACKSIZE，Len（RawImport.PA CKSIZE）-2）））
AS尺寸
来自RawImport
尺寸大小< = 500
也试过：
WHERE CLng（修剪（左（RawImport.PACKSIZE，Len（RawImport.PA CKSIZE）-2）））
< = 500
关于我做错了什么的想法？
干杯
青蛙
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
推荐答案
如果PackSize为null，则CLng（）将失败。使用Nz（）或IIf（）。
同样，如果PackSize包含非数字字符，则CLng（）将失败。
使用Val（）。
尝试这样的事情：
CLng（Val（IIf（Len（RawImport.PACKSIZE）2，Left（RawImport.PACKSIZE，
Len（RawImport.PACKSIZE） - 2），0）））
-
Allen Browne - 微软MVP。西澳大利亚珀斯
访问用户提示 - http：// allenbrowne .com / tips.html
回复群组，而不是mvps dot org的allenbrowne。
The Frog < Mr ************ @ googlemail.com写信息
新闻：11 ****************** ****@g4g2000hsf.googlegro ups.com ...
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...
大家好，
我有一个小问题这似乎没有任何意义。我正在使用
Access 97，并且有一个从文本字段中选择数据的查询，
将其转换为Lng类型。这似乎工作正常。当我尝试申请
a过滤条件是获得数据类型不匹配的垃圾
Access喜欢盯着你。
这是SQL：
SELECT CLng（修剪（左（RawImport.PACKSIZE，Len（RawImport.PA CKSIZE）-2）））
AS尺寸
来自RawImport
尺寸大小< = 500
也试过：
WHERE CLng（修剪（左（RawImport.PACKSIZE，Len（RawImport.PA CKSIZE）-2）））
< = 500
关于我做错了什么的想法？
干杯
青蛙
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
9月20日凌晨4:11，The Frog< Mr.Frog.to .... @ googlemail.comwrote：
On Sep 20, 4:11 am, The Frog <Mr.Frog.to....@googlemail.comwrote:
这似乎工作正常。当我尝试申请
时，过滤的标准是获得数据类型不匹配的垃圾
Access喜欢向你扔。
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和VBA站在他们的头上试图接受愚蠢，
他们的愚蠢，懒惰和粗心用户，比我熟悉的任何其他应用程序更加如此美元b / b，并且在某种程度上
严重降低其适用性作为严格的组件
和高效的应用程序开发平台。
访问从未向我扔垃圾，使其完全不合适
包含在我的气质红发表中。
垃圾输入=垃圾输出
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
你好再次艾伦和莱尔，
我已经尝试过IsNumeric，并且每条记录都会为相应的计算字段提供-1
（True）。 （IsNumeric（大小））。
Lyle，我确实理解垃圾输入=垃圾输出的情况，那就是
这里不是问题。我只是试图触及
的底部。
这似乎没有任何意义！所有返回的值都是
numeric，但我无法指定限制
返回行的条件。无论如何，这应该是有效的。在查询似乎运行了
之后，实际上是
出现数据类型错误，然后在查询运行后你可以看到结果grid
（显示有效结果）出现错误，然后所有
值变为#Name？ 。
我可以错过服务包吗？
干杯
青蛙
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
这篇关于使用条件查询数据类型转换问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
