Microsoft Query with Empty Values

[Mbowe]

Hello!
I need your help. I have field called 'country' which for US is blank. I want to select everything where country is not blank, not filtering.

Select Name.ID, Name.FirsName, Name.LastName, Name.Country
from tblMembers
where Name.Country<>'';

The code above does not filter, I also tried Name.Country Is Not Null.

 

[Stella740pl]

Try

... WHERE !Empty(Name.Country)

 

[Mbowe]

Unfortunately did not work.

 

[Dan Freeman]

From your subject and the semicolon at the end of the command you posted, I'm guessing this isn't a Foxpro question. Is that right?

 

[Mike Lewis]

Mbowe,

Stella's suggestion of using NOT EMPTY() is the obvious way to do this. In what way didn't it work?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[JRB-Bldr]

Before we continue to give you approaches to your problem which might not be applicable, you might help us with some clarification.

First, is the data source a non-Visual Foxpro table?
If so, these VFP commands which we are recommending might not work is the back-end data source does not recognize these commands.

Second, as Dan asked above, are you using Visual Foxpro or not for your application code?
Again, these VFP commands which we are recommending might not work.

Lastly, give us more detail when you say it "did not work".
* Do you get an error message upon execution?
* Or does the returned data records continue to erroneously contain blank Country field values?

Good Luck,
JRB-Bldr

 

[TamarGranor]

In VFP, you can do it like this:

SELE Invoices.PN,QTY,LATEST;
FROM INVOICES;
  JOIN (SELECT PN, MAX(DATEBOT) AS Latest ;
          GROUP BY PN) Recent ;
    ON Invoices.PN = Recent.PN ;
    AND Invoices.Datebot = Recent.Latest ;

In earlier versions, you can use this:

SELE PN,QTY,DATEBOT LATEST;
FROM INVOICES;
  WHERE DATEBOT = ;
    (SELECT MAX(DATEBOT) FROM Invoices INV;
       WHERE INV.PN = Invoices.PN)

Tamar

 

[AllanB1]

Tamar, I see you responded to my post 1252-1179733 here.

I will try these also.

Thanks.

Allan

 

[Mbowe]

I am not using VFP. And what I meant did not work is that it still returns the blank country. I will try to use

lcCountry = space(len(Name.Country))

and see if it works.

 

[Mike Lewis]

Mbowe,

I am not using VFP.[/u]

You would have saved us all a lot of time if you had mentioned that it in the first place. Or, better still, if you had posted your question in a more appropriate forum.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Mbowe]

Hi, just to let you know that, when I tried just to select few fields:


select city, country from Name_Address where country<>''


it filtered as I want, so now I have to check what are the fields from the other table makes the error, probably I do something wrong on grouping. I appreciate all for your efforts.