Query help

[superrob5]

I have a query that allows me to enter a person's last name. Then Between [Enter From Date] And [Enter Thru Date]


If I leave the dates blank it won't find anything. I thought you can leave these blank and it will find anything.

 

[Jack Cowley]

Add this code in the line directly below your current one:

Between [Enter From Date] And [Enter Thru Date] Is Null

hth,
Jack

 

[superrob5]

can you explain what that does, becasue I didn't have to do that with the name?

 

[Jack Cowley]

I don't know how you are doing the name, but if it is [Enter A Name] in the criteria line of the query then it should not work. The code I suggested works because the query doesn't have a criteria for the Dates field if you leave the entries blank. Take a look at the query and you will see that Access has changed it from what you entered on the second line...

hth,
Jack

 

[Jon K]

For this kind of composite criteria, it would be easier to type the criteria in the Where Clause of the SQL Statement in query SQL View:-

SELECT ....
FROM TableName
WHERE [LastName]=[Enter a Last Name] and ([DateField] between [Enter From Date] and [Enter Thru Date] or [Enter From Date] is null);

(replace with the correct field names)

Now when [Enter From Date] is left blank, the criteria for the DateField is ignored, so all the dates for the entered LastName will be returned.

Note   If you subsequently edit the query in query Design View, Access will re-arrange the Where Clause and add some brackets and AND and OR.

 

[superrob5]

the way I did the name is
Like "*" & [Enter your last name] & "*"

now for the dates I have

WHERE ((JOBS.[PC-LAN REP] Like "*" & [Enter your last name] & "*") AND (JOBS.STATUS="COMPLETE" Or JOBS.STATUS="CLOSED" Or JOBS.STATUS="CANCELLED") AND (([JOBS].[COMPLETION DATE] Between [Enter From Date] And [Enter Thru Date]) Is Null))



(([JOBS].[COMPLETION DATE] Between [Enter From Date] And [Enter Thru Date]) Is Null))


what does the is null statment do. It just doesnt look right but it works.

 

[Jon K]

(([JOBS].[COMPLETION DATE] Between [Enter From Date] And [Enter Thru Date]) Is Null) ?

Since the above expression contained two operators: Between and Is Null, I wondered why it worked.

So out of curiosity, I pasted your query into the attached database. I ran the query, entered the last name Gate and the dates 11/1/2003 and 11/20/2003, but no records were returned. It worked if the dates were left blank. (It seems Is Null works but Between doen't.)


Then I created a similar query with or [Enter From Date] is null. It worked both when the dates were entered or left blank.

SELECT *
FROM Jobs
WHERE ([PC-LAN REP] Like "*" & [Enter your last name] & "*")
And ([STATUS] in ("COMPLETE","CLOSED","CANCELLED"))
And ([COMPLETION DATE] Between [Enter From Date] And [Enter Thru Date] or [Enter From Date] is null);


When the dates are left blank, or [Enter From Date] is null becomes True, which has the effect of returning every record (as far as the date field is concerned).

 

[superrob5]

ok I dont know why but for some reason that was confusing me so I just added another query that is just for date.

SELECT Jobs.[COMPLETION DATE], *
FROM Jobs
WHERE (((([Jobs].[COMPLETION DATE]) Between [Enter From Date] And [Enter Thru Date]) Is Null));

I just confused becasue it seems like this statement needs info. what does the is null do to the statement.?

see what I don't understand is how access translated it becasue when I was makeing this query I was doing it in design view
so I typed in Between [Enter From Date] And [Enter Thru Date]) Is Null. Take a look in design view and you will see why I am confused.

 

[Jon K]

When Access runs a query, it actually runs the SQL Statement. The Design View is only a graphical presentation of the SQL Statement.

When you typed Between [Enter From Date] And [Enter Thru Date] Is Null in the criteria cell in Design View, Access was confused by the two operators: Between and Is Null, but instead of popping up an error message, it made a guess and created an SQL Statement behind the scenes, without realizing it was syntactically incorrect. When you reopened the query in Design View, it just displayed the columns according to the SQL Statement that it created. That's why now you saw two columns.

And that's why I said in my first post that, for this kind of composite criteria, it would be easier to type the criteria in the Where Clause of the SQL Statement in SQL View, as we can type the correct Where Clause without making Access guess at our needs.