Sql statement

  • Thread starter Thread starter momo
  • Start date Start date
M

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.
 
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
 
Thank for replying Wayne.

No, I need to retrieve only the records without any MiddleName. I am looking
for blank MiddleNames.
 
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"
Click to expand...

that's the sql server syntax, I think access is the same.
 
momo said:
Can someone tell me why my query returns nothing?

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

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.
 
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"
 
I tried that already and it did not work.

David Hogue said:
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.
Click to expand...
 
Still won't work

Wayne said:
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"
Click to expand...
 
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.
Click to expand...

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

LOL.



Jan Hyde (VB MVP)
 
No that will not work for me. So does this mean that there is no way to do
this? It seem so simple.
 
"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.
Click to expand...

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

What database are you using?

J


Jan Hyde (VB MVP)
 
"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.
Click to expand...

Post the exact SQL you used.

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

J
Jan Hyde said:
"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)
Click to expand...


Jan Hyde (VB MVP)
Click to expand...
 
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.

Jan Hyde said:
"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.
Click to expand...

Post the exact SQL you used.

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

J
Click to expand...
 
"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.
Click to expand...

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.

Jan Hyde said:
"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)
Click to expand...


Jan Hyde (VB MVP)
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Return value parameter error 12
Speeding up GridView 3
Gridview not updating data 1
Combining Cell Info 6
Can't concatenate for data adapters 5
intermittant error on web server 2
MS ACCESS Insert statement problem using Visual Basic 2010 0
Combo Box Problems 1

Back
Top