Need to enter blank date in select query for update query

[missinglinq]

I'm trying to select all records where a particular date hasn't been filled in yet, the object then being to do an update query on these records, and I can't figure out what criteria to use in the query grid for the blank date field. I've tried:

=""
IsNUll
#00/00/00#

and even #//# with no joy!

Thanks in advance for any help!




The Missinglinq

There's ALWAYS more than one way to skin a cat!

 

[simon551]

post the sql

 

[missinglinq]

SELECT ActivePatientsQuery.PtName, ActivePatientsQuery.NextVisit, ActivePatientsQuery.StartDate, ActivePatientsQuery.PtAddress
FROM ActivePatientsQuery
WHERE (((ActivePatientsQuery.PtAddress)=""));


Actually I need to select records where

ActivePatientsQuery.NextVisit & ActivePatientsQuery.StartDate

(both dates) are blank!

The Missinglinq

There's ALWAYS more than one way to skin a cat!

 

[simon551]

try putting the criteria in the right place, first of all. Is Null should work.

 

[simon551]

WHERE (((ActivePatientsQuery.PtAddress)=""));


WHERE (((ActivePatientsQuery.NextVisit) Is Null));

 

[JerryKlmns]

You said that you need both dates to be blank.

SELECT PtName, NextVisit, StartDate, PtAddress
FROM ActivePatientsQuery
WHERE StartDate IsNull AND
NextVisit IsNull;

--------------------
But if you also need the address to be null at the same time

SELECT PtName, NextVisit, StartDate, PtAddress
FROM ActivePatientsQuery
WHERE StartDate IsNull AND
NextVisit IsNull AND
PtAddress IsNull;

 

[missinglinq]

Thanks for all the help, but each of the above solutions give me the error:

Syntax error (missing operator in query expression)

I'll try a different approach.

The Missinglinq

There's ALWAYS more than one way to skin a cat!

 

[Golom]

IsNull" is the name of a function while "IS NULL" is an SQL construct. You can use either

SELECT PtName, NextVisit, StartDate, PtAddress
FROM ActivePatientsQuery
WHERE [red]IsNull([/red]StartDate[red])[/red] AND 
      [red]IsNull([/red]NextVisit[red])[/red] AND 
      [red]IsNull([/red]PtAddress[red])[/red];

or

SELECT PtName, NextVisit, StartDate, PtAddress
FROM ActivePatientsQuery
WHERE StartDate [red]IS NULL[/red] AND 
      NextVisit [red]IS NULL[/red] AND 
      PtAddress [red]IS NULL[/red];

 

[missinglinq]

Thanks, Golom! I finally got it to work my self with:

SELECT ActivePatientsQuery.PtName, ActivePatientsQuery.NextVisit, ActivePatientsQuery.PtAddress, ActivePatientsQuery.StartDate
FROM ActivePatientsQuery
WHERE (IsNull([ActivePatientsQuery].[PtAddress])) AND (IsNull([ActivePatientsQuery].[StartDate])) AND (IsNull([ActivePatientsQuery].[NextVisit]));

but your version works and is much clearer to me! This was my first trip "behind the scenes" so to speak with SQL. My needs had always been met before by simply using the Query Grid.

The Missinglinq

There's ALWAYS more than one way to skin a cat!