Sql statement

[momo]

Can someone tell me why my query returns nothing?

"Select EmployeeID, SSN, LastName, FirstName, Address, City, State, Zipcode,
Phone from Employee where MiddleName ='' "

However this works fine.
"Select EmployeeID, SSN, LastName, FirstName, Address, City, State, Zipcode,
Phone from Employee where MiddleName ='M' "

I have checked the Employee table and I have several records with no value
in the MiddleName column.

I am using MS Access'

Please help this is driving me crazy.

 

[Wayne]

Are you sure you don't need to use a LIKE

For Middle Names that begin with:
"Select EmployeeID, SSN, LastName, FirstName, Address, City, State, Zipcode,
Phone from Employee where MiddleName like 'M%' "

For Middle Names that contain (any where)
"Select EmployeeID, SSN, LastName, FirstName, Address, City, State, Zipcode,
Phone from Employee where MiddleName like '%M%' "

For Middle Names that end with...
"Select EmployeeID, SSN, LastName, FirstName, Address, City, State, Zipcode,
Phone from Employee where MiddleName like '%M' "


-Wayne

 

[momo]

Thank for replying Wayne.

No, I need to retrieve only the records without any MiddleName. I am looking
for blank MiddleNames.

 

[Guest]

Try using where len(ltrim(MiddleName)) = 0...

 

[sloan]

If the value is null (in the db), an empty string is NOT the same thing as
null.

I'm not super sure with access, but I think the query is

"Select EmployeeID, SSN, LastName, FirstName, Address, City, State, Zipcode,

Phone from Employee where MiddleName IS NULL"

that's the sql server syntax, I think access is the same.

 

[David Hogue]

Can someone tell me why my query returns nothing?

"Select EmployeeID, SSN, LastName, FirstName, Address, City, State, Zipcode,
Phone from Employee where MiddleName ='' "

Is MiddleName null?

Try "... where MiddleName = null" or "... where MiddleName is null".

I haven't used Access much, so I'm not sure on the syntax.

 

[Wayne]

Sorry, I misread that.

Try testing for length after triming trailing spaces. I believe the syntax
is:

"Select EmployeeID, SSN, LastName, FirstName, Address, City, State, Zipcode,
Phone from Employee where len(ltrim(MiddleName)) = 0"

 

[momo]

I tried that already and it did not work.

Is MiddleName null?

Try "... where MiddleName = null" or "... where MiddleName is null".

I haven't used Access much, so I'm not sure on the syntax.

 

[momo]

Still won't work

Sorry, I misread that.

Try testing for length after triming trailing spaces. I believe the
syntax is:

"Select EmployeeID, SSN, LastName, FirstName, Address, City, State,
Zipcode, Phone from Employee where len(ltrim(MiddleName)) = 0"

 

[momo]

Did not work

 

[Guest]

Jeesh!
Try replacing all the empty middle names with space (' '). That HAS to work.

Peter

 

[Jan Hyde]

Peter Bromberg [C# MVP] <[email protected]>'s
wild thoughts were released on Wed, 24 May 2006 18:10:02
-0700 bearing the following fruit:

Jeesh!
Try replacing all the empty middle names with space (' '). That HAS to work.

Yea, but how's he gonna write the WHERE clause for the
UPDATE statment ;-)

LOL.



Jan Hyde (VB MVP)

 

[momo]

No that will not work for me. So does this mean that there is no way to do
this? It seem so simple.

 

[Jan Hyde]

"momo" <[email protected]>'s wild thoughts were released

No that will not work for me. So does this mean that there is no way to do
this? It seem so simple.

It is simple. What your saying just doesn't add up though.

What database are you using?

J


Jan Hyde (VB MVP)

 

[momo]

MS Access

"momo" <[email protected]>'s wild thoughts were released


It is simple. What your saying just doesn't add up though.

What database are you using?

J



Jan Hyde (VB MVP)

 

[Jan Hyde]

"momo" <[email protected]>'s wild thoughts were released

MS Access

Well I don't know Access but assuming you do, can you
determine if the MiddleName field of the Emplyee table
allows NULL values?

"momo" <[email protected]>'s wild thoughts were released


It is simple. What your saying just doesn't add up though.

What database are you using?

J



Jan Hyde (VB MVP)

Jan Hyde (VB MVP)

 

[momo]

Yes it does and I have tried to use NULL in the statement and it will not
work either.

"momo" <[email protected]>'s wild thoughts were released

MS Access

Well I don't know Access but assuming you do, can you
determine if the MiddleName field of the Emplyee table
allows NULL values?

 

[Jan Hyde]

"momo" <[email protected]>'s wild thoughts were released

Yes it does and I have tried to use NULL in the statement and it will not
work either.

Post the exact SQL you used.

Also, when you say the query returned no rows, how did you
determine this?

J

"momo" <[email protected]>'s wild thoughts were released


Well I don't know Access but assuming you do, can you
determine if the MiddleName field of the Emplyee table
allows NULL values?




Jan Hyde (VB MVP)

Jan Hyde (VB MVP)

 

[momo]

I have a datagrid to is suppose to display the records found and it does not
display any record when I use

"Select EmployeeID, SSN, LastName, FirstName, Address, City, State, Zipcode,
Phone from Employee where MiddleName =''' '"

But works when I use

"Select EmployeeID, SSN, LastName, FirstName, Address, City, State, Zipcode,
Phone from Employee where MiddleName ='''M''"

Thanks for helping me with this.

Momo.

"momo" <[email protected]>'s wild thoughts were released

Yes it does and I have tried to use NULL in the statement and it will not
work either.

Post the exact SQL you used.

Also, when you say the query returned no rows, how did you
determine this?

J

 

[Jan Hyde]

"momo" <[email protected]>'s wild thoughts were released

I have a datagrid to is suppose to display the records found and it does not
display any record when I use

"Select EmployeeID, SSN, LastName, FirstName, Address, City, State, Zipcode,
Phone from Employee where MiddleName =''' '"

But works when I use

"Select EmployeeID, SSN, LastName, FirstName, Address, City, State, Zipcode,
Phone from Employee where MiddleName ='''M''"

Thanks for helping me with this.

Then the first thing we need to do is eliminate the SQL as
the cause of the problem. Presumably there is some way you
can pull back results for a given SQL in access itself?

See if you get any results, if you do get results then you
need to post more of your code.

J

Momo.

"momo" <[email protected]>'s wild thoughts were released


Post the exact SQL you used.

Also, when you say the query returned no rows, how did you
determine this?

J


Jan Hyde (VB MVP)

Jan Hyde (VB MVP)