Query Criteria Question

[RayMilhon]

I have a sub form in MS Access 2010 that is based on a couple of textboxes on the main form. One of those textboxes is for the user to enter a date value.



In the query I have a date field where the criteria is set for that text box. The issue is that sometimes the user doesn't know the date and leaves it blank. Instead of getting all dates for the person on the main form it returns nothing. I tried adding a column that referred to that textbox and set the criteria = Null and set that criteria in a different row then the date field so in SQL my query looks like

Select field1,field2,...
From MyTable
Where entereddate = forms!myform!dtvalue Or forms!myform!dtvalue Is null..

I have the main form and subform linked on the memberid but as soon as I add that to the query save and test MS Access crashes and exits.

Any thoughts?

[hertfordkc]

Ray,
I've had trouble with nulls in the record set and in the criteria. Suggest you use NZ in the query to set the fields/controls to empty or zero...in all of the fields if necessary.

[alansidman]

I've used this in situations where the criteria is a text data type but it is untested for date formats.

In your where clause;

WHERE entereddate Like forms!myform!dtvalue & "*"

Alan

[RayMilhon]

hertfordkc I know with 100% certainty that the date field I'm looking at has 0 nulls. The issue is that I want either a specific date or all dates depending on whether the text box has a date or not.

alan I'll try that and let you know.

[abeta2003a]

is it solve now?
if not..i have an example..SQL code below
SELECT E.ID, E.Date, E.Area, E.Name, [E]![R]-[E]![P] AS Net_Payment, E.BCF, E.Comment, P.Type
FROM E INNER JOIN P ON E.Area = P.List
WHERE (((E.Date) Between [Forms]![DRS]![1st_Day] And [Forms]![DRS]![1st_End_Day]) AND ((E.Area)=IIf([Forms]![DRS]![Area]<>"",[Forms]![DRS]![Area],[E].[Area])))
ORDER BY E.ID, E.Date;
---------------------
which in the where statement, use iif clause to loop back to the field..then if it is null, the query will show all

[RayMilhon]

I've been away for a week at my grandson's High School Graduation on the other side of the country.
As soon as I get caught up I'll look at this closer and reply