Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need to enter blank date in select query for update query

Status
Not open for further replies.

missinglinq

Programmer
Feb 9, 2002
1,914
US
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!
 
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!
 
try putting the criteria in the right place, first of all. Is Null should work.

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


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

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;
 
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!
 
IsNull" is the name of a function while "IS NULL" is an SQL construct. You can use either
Code:
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

Code:
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];
 
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!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top