I have a date field in my query and

[backer]

I have a date field in my query and I want to use a parameter criteria. When I enter [ ] and run the query, it works fine when I enter an actual date. But if I try to enter "Is Null" or "Is Not Null", it doesn't return any records. Without the parameter in criteria, if I enter "Is Null" or "Is Not Null" it works fine. Please tell me what I need to do to make the parameter criteria work with an actual date, is null or is not null criteria. Thanks a lot.

 

[tlbroadbent]

I hope I understand your question. You want to be able to search by date or find rows with Null dates. If that is correct, the following will work.

Select * From YourTable
Where DateCol=[Enter Date]
Or (IsNull(DateCol) And IsNull([Enter Date])) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[backer]

Thanks for the response. But, it isn't giving me what I am looking for.

In a normal query, in my "date completed" field, if I enter a specific date, I get all records with that date. If I enter "is null", I get all records that have no dates.

I would like a parameter query in which I could have the option of entering a specific date (and getting only those records with that date) OR enter "is null" (and getting only those records with no dates). But when I enter "is null" in a parameter query, it doesn't act the same way as in a normal query, i.e., it doesn't produce the records.

 

[tlbroadbent]

If you just leave the parameter blank and hit OK the query I provided will work. However, if you want to type in "Is Null" then use this query.

Select * From YourTable
Where DateCol=[Enter Date]
Or [Enter Date]="Is Null" Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[tlbroadbent]

Ignore the previous post. This is the query you need.

Select * From YourTable
Where DateCol=[Enter Date]
Or (IsNull(DateCol) And [Enter Date]="Is Null&quot

Alternate:

Select * From YourTable
Where DateCol=[Enter Date]
Or (DateCol Is Null And [Enter Date]="Is Null&quot
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[backer]

Thanks Terry, but I must still be doing something wrong. I am still not getting the records I should be getting when I enter Is Null in my Date_completed field parameter query. As I mentioned before, if I enter Is Null as the criteria for this field in a normal query it works (it gives me the records that have no dates). When I make it a parameter query and enter Is Null, I don't get any records. Any other ideas what I am doing wrong?

 

[WildHare]

Because there is (probably) no date with a value of "IS NULL" - what you need to do is set it so that if the parameter prompt is left blank, "is null" is passed as an OR'ed criteria...

=[Enter Date: ] or is null

or something like that....




Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[tlbroadbent]

I agree with Jim. I made the same recommendat. To find nulls just hit OK on the parameter without entering anything. However, the other queries (corrected) should work if you want to go to the trouble of typing "Is Null" in the parameter.

The parameter can not be directly interpreted as if you type "Is Null" in the query criteria. The parameter will be a text column that the query attemptsa to compare to a date. It won't work.

Please open the SQL View of your query, copy it to the clipboard and paste in message for us yo examine. We should be able to tell you why it doesn't work. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[backer]

Terry and Jim, I think your (Terry) 2nd paragraph answers my question, i.e., "The parametr can not be directly interpreted as if you type "Is Null" in the query critera.....

When I use the variation that you two have suggested, it works fine for entering nothing in the parameter and getting all records with no dates. However, when I enter a specific date, it gives me all records that have that date and all records with no dates (which looks like what it should be doing but not what I want it to do).

Maybe what I want, can't be done from a paramter. Thanks again.

Thanks again for being so patient.

 

[patriciaanne]

That's my understanding also... you have to ask for what is actually there.. That is also the reason wildcards don't work.

 

[tlbroadbent]

Can you post the query? I want to comapre what you're actually running to what I've suggested.

Select * From YourTable
Where DateCol=[Enter Date]
Or (IsNull(DateCol) And IsNull([Enter Date]))

The first condition should select records that match the parameter if you enter one. If you don't type anything the parameter will be Null so the 2nd conidtion will select rows with a Null date only when the parameter is Null. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[backer]

Below is the SQL view of the query I am running - specific date works OK - null gives me no records.

SELECT PL_Items.Pre, PL_Items.Date_Cmpl, Loc2.Order2, PL_Items.S1, PL_Items.PL, PL_Items.Loc1, PL_Items.Item_no, PL_Items.Date_ent, PL_Items.Loc2, PL_Items.Ryan_Rmks, PL_Items.Spec, PL_Items.Desc, PL_Items.Rmks1, PL_Items.Rmks2, PL_Items.ICWL, ([Desc]) & " " & ([Rmks2]) AS Desc_Full, PL_Items.ID
FROM Loc2 INNER JOIN PL_Items ON Loc2.Loc2 = PL_Items.Loc2
WHERE (((PL_Items.Pre)=Yes) AND ((PL_Items.Date_Cmpl)=[Enter Date] Or ((PL_Items.Date_Cmpl)=IsNull("Date_Cmpl&quot And (PL_Items.Date_Cmpl)=IsNull([Enter Date]))));

 

[tlbroadbent]

Your criteria is incorrect. It doesn't match the sample I provided. Modify your query as below. The changed portion is highlighted.

SELECT
PL_Items.Pre, PL_Items.Date_Cmpl,
Loc2.Order2, PL_Items.S1,
PL_Items.PL, PL_Items.Loc1,
PL_Items.Item_no, PL_Items.Date_ent,
PL_Items.Loc2, PL_Items.Ryan_Rmks,
PL_Items.Spec, PL_Items.Desc,
PL_Items.Rmks1, PL_Items.Rmks2,
PL_Items.ICWL,
([Desc]) & " " & ([Rmks2]) AS Desc_Full,
PL_Items.ID
FROM Loc2
INNER JOIN PL_Items
ON Loc2.Loc2 = PL_Items.Loc2
WHERE PL_Items.Pre=Yes
AND (PL_Items.Date_Cmpl)=[Enter Date]
OR (IsNull(PL_Items.Date_Cmpl)
AND IsNull([Enter Date]))); Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[backer]

I am getting closer! Now the specific date works as it should, but leaving the parameter criteria blank gives me all records with no dates instead of records with no dates and "pre"=YES.

 

[tlbroadbent]

Looks like I'm on a roll. Change the WHERE cluase slightly.

WHERE PL_Items.Pre=Yes
AND (PL_Items.Date_Cmpl=[Enter Date]
OR (IsNull(PL_Items.Date_Cmpl)
AND IsNull([Enter Date]))); Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[backer]

BINGO!

You got it!

Thanks a lot for all your help and PATIENCE. It is people like you that keep people like me from giving up in frustration.

You are a great asset to Tek Tips!

Thanks again