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

 

[lupins46]

Records where Module_balance = 0, DOB = 1/1/1929 or 1/1/1986 will not be included in either set.

 

[Reggie2004]

I added = to those sets ie <=. Still I am 4 records short.

 

[lupins46]

You haven't added the '=' to both queries have you?

 

[Reggie2004]

Yes I have.

 

[lupins46]

You only want to do it to one query, not both (otherwise you now include the records in both results)

I can't tell you which one - you have to decide that.

 

[Reggie2004]

But I still end up with 252 instead of 256 records.

 

[lupins46]

Post the queries again.

 

[Reggie2004]

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")));

2nd query

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")));

 

[lupins46]

As I said before you should make the changes to just one of the queries. You have changed both of them.

 

[Reggie2004]

Removed the change to 1 query only. Still missing 4 records.

 

[lupins46]

What does 'module-balance' contain?
You are doing a string comparison on this field.
With a name like that I would have thought it would be a number.
If it is a text field containing a number then you need to use:

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

with a similar mod for the other query.

 

[Reggie2004]

It is a number field.

 

[lupins46]

If Module_balance is a number field then this :
Filter.MODULE_BALANCE)>="0"
will produce a run-time error.

If you do not get an error then it cannot currently be defined as a number field.

 

[Reggie2004]

You are currect it is a text field. But when I use the query you gave me I get a data type error.

 

[lupins46]

I believe that the two last lines should be:

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

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

but without knowing exactly what sort of values you have in module_balance, I'm guessing a bit.

 

[Reggie2004]

Data type mismatch error. When I use your changes.

 

[lupins46]

Can you confirm you do not have quotes around the zero.

 

[Reggie2004]

When I used yours I did not have any quotes. A straight copy and paste.

 

[lupins46]

I'm baffled now...

Can you do this...
Create a new query based on th Filter table.
Just put the module_balance field into the grid and in the criteria cell put >0
Run the query.
If it reports an error, then change the criteria to >"0"
and rerun the query.
Whichever works ok, please post the SQL from the SQL view of that query.