Query Problems

[Kathy]

I am trying to run a query from a table (of vendors) where a column of Post
Codes has a blank field. The requirement is that we produce a report to show
which Vendor did not supply the Post Code. I have tried using SELECT, WHERE &
FROM using the IsNull function. Does anyone know where I might be going
wrong? Thanks
Kathy

 

[Ofer]

Try this

Select * From Vendors Where [Post Codes] is null or Trim([Post Codes])=""

 

[Kathy]

Thanks for a quick response, however I am receiving the following error
message

"You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's FROM clause. Revise the
SELECT statement of the subquery to request only one field. (Error 3306)"

Try this

Select * From Vendors Where [Post Codes] is null or Trim([Post Codes])=""

--
I hope that helped
Good luck

I am trying to run a query from a table (of vendors) where a column of Post
Codes has a blank field. The requirement is that we produce a report to show
which Vendor did not supply the Post Code. I have tried using SELECT, WHERE &
FROM using the IsNull function. Does anyone know where I might be going
wrong? Thanks
Kathy

 

[Douglas J Steele]

Or, simpler,

Select * From Vendors Where Len(Trim([Post Codes] & "")) = 0


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Try this

Select * From Vendors Where [Post Codes] is null or Trim([Post Codes])=""

--
I hope that helped
Good luck

I am trying to run a query from a table (of vendors) where a column of Post
Codes has a blank field. The requirement is that we produce a report to show
which Vendor did not supply the Post Code. I have tried using SELECT, WHERE &
FROM using the IsNull function. Does anyone know where I might be going
wrong? Thanks
Kathy

 

[Ofer]

Hi, Kathy
Can you post the SQL please

--
I hope that helped
Good luck

Thanks for a quick response, however I am receiving the following error
message

"You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's FROM clause. Revise the
SELECT statement of the subquery to request only one field. (Error 3306)"

Try this

Select * From Vendors Where [Post Codes] is null or Trim([Post Codes])=""

--
I hope that helped
Good luck

I am trying to run a query from a table (of vendors) where a column of Post
Codes has a blank field. The requirement is that we produce a report to show
which Vendor did not supply the Post Code. I have tried using SELECT, WHERE &
FROM using the IsNull function. Does anyone know where I might be going
wrong? Thanks
Kathy

 

[Kathy]

SELECT VENDORS.Vendno, VENDORS.Name, VENDORS.Postcode
FROM VENDORS;

Or, simpler,

Select * From Vendors Where Len(Trim([Post Codes] & "")) = 0


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Try this

Select * From Vendors Where [Post Codes] is null or Trim([Post Codes])=""

--
I hope that helped
Good luck

I am trying to run a query from a table (of vendors) where a column of Post
Codes has a blank field. The requirement is that we produce a report to show
which Vendor did not supply the Post Code. I have tried using SELECT, WHERE &
FROM using the IsNull function. Does anyone know where I might be going
wrong? Thanks
Kathy

 

[Kathy]

I'm not sure if you received the SQL. Here it is

SELECT VENDORS.Vendno, VENDORS.Name, VENDORS.Postcode
FROM VENDORS;

thanks

Hi, Kathy
Can you post the SQL please

--
I hope that helped
Good luck

Thanks for a quick response, however I am receiving the following error
message

"You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's FROM clause. Revise the
SELECT statement of the subquery to request only one field. (Error 3306)"

Try this

Select * From Vendors Where [Post Codes] is null or Trim([Post Codes])=""

--
I hope that helped
Good luck


:

I am trying to run a query from a table (of vendors) where a column of Post
Codes has a blank field. The requirement is that we produce a report to show
which Vendor did not supply the Post Code. I have tried using SELECT, WHERE &
FROM using the IsNull function. Does anyone know where I might be going
wrong? Thanks
Kathy

 

[Kathy]

Thanks Douglas but I received the same error message again

Or, simpler,

Select * From Vendors Where Len(Trim([Post Codes] & "")) = 0


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Try this

Select * From Vendors Where [Post Codes] is null or Trim([Post Codes])=""

--
I hope that helped
Good luck

I am trying to run a query from a table (of vendors) where a column of Post
Codes has a blank field. The requirement is that we produce a report to show
which Vendor did not supply the Post Code. I have tried using SELECT, WHERE &
FROM using the IsNull function. Does anyone know where I might be going
wrong? Thanks
Kathy

 

[Douglas J Steele]

SELECT VENDORS.Vendno, VENDORS.Name, VENDORS.Postcode
FROM VENDORS WHERE LEN(TRIM(VENDORS.Postcode & "")) = 0

although all you really should need is

SELECT Vendno, Name
FROM VENDORS WHERE LEN(TRIM(Postcode & "")) = 0

(no need to include Postcode in the SELECT part if you know it doesn't
exist!)

Both Ofer and I had [Post Codes]: do you have a table with that name?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

SELECT VENDORS.Vendno, VENDORS.Name, VENDORS.Postcode
FROM VENDORS;

Or, simpler,

Select * From Vendors Where Len(Trim([Post Codes] & "")) = 0


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Try this

Select * From Vendors Where [Post Codes] is null or Trim([Post Codes])=""

--
I hope that helped
Good luck


:

I am trying to run a query from a table (of vendors) where a column

of
Post

Codes has a blank field. The requirement is that we produce a report

to
show

which Vendor did not supply the Post Code. I have tried using

SELECT,
WHERE &

FROM using the IsNull function. Does anyone know where I might be going
wrong? Thanks
Kathy

 

[Ofer]

I can't see the where statement, also it sound like you creating a query
based on another query
Is that the case?
If so, does the first query runs with no errors

I need the full SQL with the where condition

--
I hope that helped
Good luck

I'm not sure if you received the SQL. Here it is

SELECT VENDORS.Vendno, VENDORS.Name, VENDORS.Postcode
FROM VENDORS;

thanks

Hi, Kathy
Can you post the SQL please

--
I hope that helped
Good luck

Thanks for a quick response, however I am receiving the following error
message

"You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's FROM clause. Revise the
SELECT statement of the subquery to request only one field. (Error 3306)"

:

Try this

Select * From Vendors Where [Post Codes] is null or Trim([Post Codes])=""

--
I hope that helped
Good luck


:

I am trying to run a query from a table (of vendors) where a column of Post
Codes has a blank field. The requirement is that we produce a report to show
which Vendor did not supply the Post Code. I have tried using SELECT, WHERE &
FROM using the IsNull function. Does anyone know where I might be going
wrong? Thanks
Kathy