Make table query for empty records

[Texas Longhorn]

Hi all,

I'm new to Access, and I'm having trouble getting a make table query to work. I have a database with one table (RawData, which is quite large). One of the fields, PERMNO4, contains some records that are (or at appear to be) blank/empty. I am trying to make a new table with all of the fields in RawData but with the criterion that PERMNO4 be blank. To do this, I have gone into Create query in Design view, chosen Make Table Query in the query drop down menu, selected RawData, dragged all of the fields down, and then in Criteria under PERMNO4 I typed IsEmpty([PERMNO4]). I also tried IsNull. Both of these create a table with zero records. Any tips would be greatly appreciated.

Thanks,
Bill

 

[james_lankford]

select
  *
into
  new_table
from
  RawData
where 
(
  (
    len(trim(nz(PERMNO4, "")) ) = 0
  )
)

those are 2 double quotes

 

[Norie]

Try this.

SELECT RawData.PERMNO4, RawData.OtherData INTO new_table
FROM RawData
WHERE (((RawData.PERMNO4) Is Null));

 

[Texas Longhorn]

James - thanks for the reply. I am getting the error: Data type mismatch in criteria expression. I should have mentioned that PERMNO4 is stored as Text.

Thanks,

Bill

 

[Texas Longhorn]

Norie - that's it! Thank you both very much for your help.
Bill

 

[james_lankford]

i mistyped the where clause

but it should be fixed now

 

[james_lankford]

i mistyped the where clause

but it should be fixed now

l

and the way mine is different from Norie's is mine gets fields with only spaces in them as well as nul

 

[Norie]

And if you want to deal with spaces just add Trim.

SELECT [RawData].[PERMNO4], [RawData].[OtherData] INTO new_table
FROM RawData
WHERE ((Trim([RawData].[PERMNO4])) Is Null);

 

[Texas Longhorn]

Thanks to you both for the additional suggestions...much appreciated.