By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,136 Members | 1,712 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,136 IT Pros & Developers. It's quick & easy.

Text criteria weirdness

P: n/a
I have a query that uses another query as a data source (Access 97). One
of the fields is a one-character string (it's parsed from an
eight-character string in the underlying table using the Left$ function)
that is always either "R" or "N". For this query I only want to view the
records where that field is "N", but using

"N"

or

="N"

in the criteria box for that field it doesn't work -- I get a data type
mis-match error when I run the query. When I use

In("N")

it works.

Is this normal behavior?
--
Please remove the under_scores if sending me mail.

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Wed, 11 Feb 2004 01:12:54 GMT, "Roald Oines" <r_*******@bresnan.net> wrote:
I have a query that uses another query as a data source (Access 97). One
of the fields is a one-character string (it's parsed from an
eight-character string in the underlying table using the Left$ function)
that is always either "R" or "N". For this query I only want to view the
records where that field is "N", but using

"N"

or

="N"

in the criteria box for that field it doesn't work -- I get a data type
mis-match error when I run the query. When I use

In("N")

it works.

Is this normal behavior?


No, that's not normal. Can you give us more details?
Nov 12 '05 #2

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote:
: On Wed, 11 Feb 2004 01:12:54 GMT, "Roald Oines"
: <r_*******@bresnan.net> wrote:
:
:>I have a query that uses another query as a data source (Access 97).
:>One of the fields is a one-character string (it's parsed from an
:>eight-character string in the underlying table using the Left$
:>function) that is always either "R" or "N". For this query I only
:>want to view the records where that field is "N", but using
:>
:>"N"
:>
:>or
:>
:>="N"
:>
:>in the criteria box for that field it doesn't work -- I get a data
:>type mis-match error when I run the query. When I use
:>
:>In("N")
:>
:>it works.
:>
:>Is this normal behavior?
:
: No, that's not normal. Can you give us more details?
Sure, let's see... The underlying table is created from importing a
mainframe-created text report using a fixed-width import spec. All of
its fields are text data types. The query that serves as this query's
data source is a totals query that uses another query as its data
source. The first query in the chain 1) checks to make sure the row has
valid data (it only returns valid rows -- there are a lot of garbage
rows in reports like this one) and 2) creates a few new fields by
applying built-in functions (CCur, Left$) to some of the table's fields.
The totals query uses Group By (the field I'm trying to filter to "N"
only is one of the two Group By fields) and Sum. I'm certain it's the
text field that's returning the "data type mismatch" error -- changing
only the criteria for that field as above lets the query run. There are
no other tables involved in any of the queries, so there are no joins.
--
http://rec-sport-golf.com/?rc=oinesroald
Please remove the under_scores if sending me mail.

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.