Query: date is null

  • Thread starter Thread starter barrynichols
  • Start date Start date
B

barrynichols

I am trying to write a query to call up records where no date is
entered (i.e. null). However, when I go to run the query it gives an
error of data type mismatch.

Any ideas?

Thanks
 
What have you tried?

Field: SomeDateField
Criteria: Is Null

IF the above doesn't help, please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
SELECT tbl_Visit.[Shopper ID], tbl_Visit.[Visit Number],
tbl_Visit.KFI, tbl_Visit.[KFI Date]
FROM tbl_Visit
WHERE (((tbl_Visit.KFI)="Yes") AND ((tbl_Visit.[KFI Date]) Is Null))
ORDER BY tbl_Visit.[Shopper ID], tbl_Visit.[Visit Number];

Also trying "" in criteria provides the same result.

thanks
 
The mismatch could be that the KFI is a boolean (Yes/No) field. Try the
following and see if that works

SELECT tbl_Visit.[Shopper ID]
, tbl_Visit.[Visit Number],
tbl_Visit.KFI
, tbl_Visit.[KFI Date]
FROM tbl_Visit
WHERE (((tbl_Visit.KFI)=TRUE)
AND ((tbl_Visit.[KFI Date]) Is Null))
ORDER BY tbl_Visit.[Shopper ID], tbl_Visit.[Visit Number];

If you still get the mismatch error- simplify the query by trying only one
criteria at a time. So you can identify the specific field causing the
problem

In the query design view
Field: KFI
Criteria: True


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

SELECT tbl_Visit.[Shopper ID], tbl_Visit.[Visit Number],
tbl_Visit.KFI, tbl_Visit.[KFI Date]
FROM tbl_Visit
WHERE (((tbl_Visit.KFI)="Yes") AND ((tbl_Visit.[KFI Date]) Is Null))
ORDER BY tbl_Visit.[Shopper ID], tbl_Visit.[Visit Number];

Also trying "" in criteria provides the same result.

thanks



What have you tried?

Field: SomeDateField
Criteria: Is Null

IF the above doesn't help, please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.








- Show quoted text -
Click to expand...
Click to expand...
 
Perfect - thanks for your help. I knew that date fields could be a bit
funny, but the boolean caught me out!

The mismatch could be that the KFI is a boolean (Yes/No) field. Try the
following and see if that works

SELECT tbl_Visit.[Shopper ID]
, tbl_Visit.[Visit Number],
tbl_Visit.KFI
, tbl_Visit.[KFI Date]
FROM tbl_Visit
WHERE (((tbl_Visit.KFI)=TRUE)
AND ((tbl_Visit.[KFI Date]) Is Null))
ORDER BY tbl_Visit.[Shopper ID], tbl_Visit.[Visit Number];

If you still get the mismatch error- simplify the query by trying only one
criteria at a time. So you can identify the specific field causing the
problem

In the query design view
Field: KFI
Criteria: True

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.




SELECT tbl_Visit.[Shopper ID], tbl_Visit.[Visit Number],
tbl_Visit.KFI, tbl_Visit.[KFI Date]
FROM tbl_Visit
WHERE (((tbl_Visit.KFI)="Yes") AND ((tbl_Visit.[KFI Date]) Is Null))
ORDER BY tbl_Visit.[Shopper ID], tbl_Visit.[Visit Number];
Click to expand...
Also trying "" in criteria provides the same result.
Click to expand...

- Show quoted text -
Click to expand...
 
Back
Top