473,320 Members | 1,802 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Text criteria weirdness

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
2 1548
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Tim Marshall | last post by:
Access 2003. The situation is this: I have a "criteria selection form" in which there are a many different criteria, mostly displayed as combo and text boxes in which a user can enter stuff,...
4
waynetheengineer
by: waynetheengineer | last post by:
Hi, I was wondering if anyone had any suggestions on my database: I have a form that accepts user input into a single text box. When the OK button is hit, a query should search for all records...
6
by: shantanu | last post by:
Hi All, I have a requirement to develop a search engine based on some search criteria that will search for the string or statement in all the documents uploaded in the website. The search result...
7
prn
by: prn | last post by:
Hi folks, Sometime in the last few days, I must have accidentally done something that changed some kind of switch in Access, but I can't seem to find what it was. This is Access 2003 under XP,...
10
by: nickvans | last post by:
I have a form in which users may search for a module based on a number of criteria including three check boxes (indicating which processes have been completed) as well as a text box for the user to...
2
by: SHAWTY721 | last post by:
I have a form that contains two combo boxes that are related to each other. I need to find a way to populate my text box based on the criteria of the two combo boxes so the appropriate number...
13
by: Robertf987 | last post by:
Hi, Yet another thing I need help with I'm affraid. I'll first explain what I want, then I'll try to explain what I have. I'm using Microsoft Access 2000. What I want is to be able to do a...
2
by: JYA | last post by:
Hi. I was writing an xmltv parser using python when I faced some weirdness that I couldn't explain. What I'm doing, is read an xml file, create another dom object and copy the element from...
2
by: Denise | last post by:
Front end is Access 2002, back end is linked Oracle tables. My users need to describe things in feet and inches and want to use the standard ' and " abbrevations. On a testing form I go to a...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.