Query help

[Reggie2004]

I have 2 queries. They are exact opposites. But I am losing 4 records. Can anyone help? I am just going to list the 2 queries.

First

FROM Filter
WHERE((((Filter.[TC-530])="TC-530"))
Or (((Filter.[TC-150])="TC-150"))
Or (((IsNull((Filter.DOD)))=False))
Or (((Filter.DOB)<#1/1/1929#))
Or (((Filter.DOB)>#1/1/1986#))
Or (((IsNull((Filter.[SSN S])))=False))
Or (((Filter.[TC-421])="TC-421"))
Or (((Filter.[TC-420])="TC-420"))
Or (((Filter.[TC-424])="TC-424"))
Or (((Filter.[TC-540])="TC-540"))
Or (((Filter.[LFRZ-RFRZ])="-C"))
Or (((Filter.[LFRZ-RFRZ])="-W"))
Or (((Filter.[LFRZ-RFRZ])="-O"))
Or (((Filter.[LFRZ-RFRZ])="-V"))
Or (((Filter.[LFRZ-RFRZ])="Z"))
Or (((Filter.[LFRZ-RFRZ])="-L"))
Or (((Filter.[TC-594])="TC-594"))
Or (((Filter.[TC-599])="TC-599"))
Or (((Filter.[TC-290])="TC-290"))
Or (((Filter.[TC-291])="TC-291"))
Or (((Filter.[TC-300])="TC-300"))
Or (((Filter.[TC-301])="TC-301"))
Or (((Filter.[TC-976])="TC-976"))
Or (((Filter.[TC-977])="TC-977"))
Or (((Filter.MODULE_BALANCE)>"0")));


Second
FROM Filter
WHERE( (((Filter.[TC-530])<>"TC-530"))
And (((Filter.[TC-150])<>"TC-150"))
And (((IsNull((Filter.DOD)))=True))
And (((Filter.DOB)>#1/1/1929#))
And (((Filter.DOB)<#1/1/1986#))
And (((IsNull((Filter.[SSN S])))=True))
And (((Filter.[TC-421])<>"TC-421"))
And (((Filter.[TC-420])<>"TC-420"))
And (((Filter.[TC-424])<>"TC-424"))
And (((Filter.[TC-540])<>"TC-540"))
And (((Filter.[LFRZ-RFRZ])<>"-C"))
And (((Filter.[LFRZ-RFRZ])<>"-W"))
And (((Filter.[LFRZ-RFRZ])<>"-O"))
And (((Filter.[LFRZ-RFRZ])<>"-V"))
And (((Filter.[LFRZ-RFRZ])<>"Z"))
And (((Filter.[LFRZ-RFRZ])<>"-L"))
And (((Filter.[TC-594])<>"TC-594"))
And (((Filter.[TC-599])<>"TC-599"))
And (((Filter.[TC-290])<>"TC-290"))
And (((Filter.[TC-291])<>"TC-291"))
And (((Filter.[TC-300])<>"TC-300"))
And (((Filter.[TC-301])<>"TC-301"))
And (((Filter.[TC-976])<>"TC-976"))
And (((Filter.[TC-977])<>"TC-977"))
And (((Filter.MODULE_BALANCE)<"0")));

Reg

 

[Reggie2004]

Neither works. 0 or "0" with your query. I get data type mismatch on both.

 

[lupins46]

You'll have to go into the file design and look at the field. What is the datatype in the top half of the design screen and what is shown in the first property in the bottom half?

 

[Reggie2004]

<"0" is all I see in the design view.

 

[lupins46]

I'm after the field definition details ( of MODULE_BALANCE) in the Filter table.

 

[Reggie2004]

Field Size 255
Required no
Allowed zero length yes
indexed no
Unicode Compression no
IME Mode No Control
IME Sentence Mode None

Data type text


I hope this is what you want.

 

[lupins46]

The query with >"0" should not have errored then.
OK.
More involved but do this.

Start a new query using the design view.
Close the Show Tables dialog without selecting any table.
Click the first button on the toolbar (labelled SQL).
This takes you to the SQL string view for a query.
You will see the word "Select" there (on its own).
Now paste the following text into the SQL panel, overwriting the 'Select' that is already there..

Select Module_Balance from Filter where module_balance >"0"

On the first toolbar button switch to datasheet view and see what you get.

If this does not error then its back to the original queries.

Post the last 2 lines of each query.

 

[Reggie2004]

And ((Filter.MODULE_BALANCE)<"0")
and ((Filter.[TC-590])<>"TC-590");


2nd

OR (((Filter.MODULE_BALANCE)>"0"))
OR ((Filter.[TC-590])="TC-590");

 

[lupins46]

I assume you have added another line in- the last line wasn't there before. You have got invalid brackets.

Change it to this:
1st
and (((Filter.[TC-590])<>"TC-590"))
And (((Filter.[TC-977])<>"TC-977"))
And ((val(Filter.MODULE_BALANCE)<0)));



2nd

OR ((Filter.[TC-590])="TC-590"))
Or (((Filter.[TC-977])="TC-977"))
Or ((val(Filter.MODULE_BALANCE)>0)));

BUT you have to put the '=' into the last line of ONE of these queries (whichever query you have added the '=' to in the DOB lines).
So you must have EITHER

And ((val(Filter.MODULE_BALANCE)<=0)));

OR

Or ((val(Filter.MODULE_BALANCE)>=0)));

but not both.

 

[Reggie2004]

Data type mismatch

 

[lupins46]

I don't believe it!!!!

Is this a saved query (listed in the Queries tab) or is it being run in VBA code?

 

[Reggie2004]

It listed in the Queries tab.

 

[lupins46]

I can't see what the problem is here.
After all this time I think I would need to see the db to diagnose the problem.
Would you be able to send me a database containing just the table and the two queries - I'm finishing for today now; but I could look at it tomorrow.
You would find an address on this web page

http://www.russellscott.co.uk

 

[Reggie2004]

One problem. I have the table linke to a .txt file. I would have to sanitize the data to send you the filter.txt file. Then after sanitization I don't think there will be any data to use. What do you think?

 

[lupins46]

I would only need a couple of records - make sure you have a copy and then delete all except two, modify any sensitive values and send that.