Date Range In Criteria

[jereece]

I have a table with a field name "Date". I have a form with two fields "StartDate" and "StopDate". I have a query to display the results. In the Criteria field I want it to use the StartDate and StopDate as the range. However if no date is entered into the StartDate and StopDate, I want it to include all dates.

Normally when I do this with a single filter field the criteria would be something like

[Forms]![frm_Main]![StartDate] Or Like [Forms]![frm_Main]![StartDate] & "*"

Is there someway to combine this with one similar for StopDate? Or is there a better way to do this?

As always, thanks for the help.
Jim

 

[rockyjr]

This is off the top of my head...I didnt test it... but try this in the where clause:

WHERE (isnull([Forms]![frm_Main]![StartDate] and [Forms]![frm_Main]![StopDate]) OR Tablename.Date Between [Forms]![frm_Main]![StartDate] and [Forms]![frm_Main]![StopDate])

 

[jereece]

"Where Clause"? Do you mean the Criteria section of the query?

 

[rockyjr]

When in design view, click the VIEW and then go to :SQL view

But you can try it from Microsoft design view also...

 

[jereece]

When I look at the SQL view I already see a lot of code (see below). Plus I am not very familiar with manually coding SQL.

SELECT tbl_Data.ID, tbl_Data.TagNumber, tbl_Data.DateOfTicket, tbl_Data.Offense, tbl_Data.EmployeeName, tbl_Data.EmployeeBadgeNo, tbl_Data.EmployeeType, tbl_Data.WorkGroup, tbl_Data.Status, tbl_Data.PIP, tbl_Data.Comments, tbl_Data.State, tbl_Data.VehicleDescription, tbl_Data.Officer
FROM tbl_Data
WHERE (((tbl_Data.EmployeeBadgeNo)=[Forms]![frm_Main]![txt_BadgeNumber] Or (tbl_Data.EmployeeBadgeNo) Like [Forms]![frm_Main]![txt_BadgeNumber] & "*") AND ((tbl_Data.WorkGroup)=[Forms]![frm_Main]![cbo_Group] Or (tbl_Data.WorkGroup) Like [Forms]![frm_Main]![cbo_Group] & "*") AND ((tbl_Data.Status)=[Forms]![frm_Main]![Status] Or (tbl_Data.Status) Like [Forms]![frm_Main]![Status] & "*"));

Is there not a way to do this in the Criteria section for the date field in the query? I was hoping for some kind of AND statement to combine my original code with a another similar statement but I have never done this (see example below).

BETWEEN [Forms]![frm_Main]![StartDate] Or Like [Forms]![frm_Main]![StartDate] & "*" and [Forms]![frm_Main]![StopDate] Or Like [Forms]![frm_Main]![StopDate] & "*"

Again I appreciate the help.
Jim

 

[namliam]

The syntax is:
Fieldname Between Date1 and Date2

"Like Date1*" is nonsence

 

[Brianwarnock]

Yes you can do it in the design grid but this is one of those instances where it is clearer in SQL, however if you insist on doing it in the design grid first read JON K 's excellent post here

to do it in the design grid create a new col

Newfield: origfield Between [Forms]![frm_Main]![StartDate] and [Forms]![frm_Main]![StopDate] OR
(isnull([Forms]![frm_Main]![StartDate] and [Forms]![frm_Main]![StopDate])

uncheck Show and type True in the criteria,


Note Rockyjr may need an =True in his code for the isnull portion.

EDIt this appears to be not so as it defaults to <>False

Brian

 

[namliam]

(isnull([Forms]![frm_Main]![StartDate] and [Forms]![frm_Main]![StopDate])

This has atleast one ) to little and doesnt make sense as it is...
This maybe
(isnull([Forms]![frm_Main]![StartDate]) and isnull([Forms]![frm_Main]![StopDate]) )
or
( origfield = [Forms]![frm_Main]![StartDate] and isnull([Forms]![frm_Main]![StopDate]) )
or something yet different, but not what you have Brian

 

[Brianwarnock]

[datetime] Between [forms]![frmdates]![startdate] And [forms]![frmdates]![enddate] Or IsNull([forms]![frmdates]![startdate] And [forms]![frmdates]![enddate])

in the field
Clear show
True in the criteria
works perfectly for me

I admit that when typing it out in a post I may get a typo such as an extra or missing) ( but that's not difficult for the poster to find and correct.


Brian


Perhaps I should also state the obvious that you also need to select the original field

 

[jereece]

Brianwarnock - I tried your latest suggestion. When I run the query I get a pop-up box to enter a Parameter Value for "datetime". If I click OK and do not enter any Parameter Value, the results of the query returns all data and ignores the date range even though I have a startdate and enddate specified in the fields on the form. Also, just FYI, Access automatically adds "Expr1: " to the code in the field section. I think this is normal however.

Any other suggestions?

Thanks,
Jim

 

[Brianwarnock]

Datetime was my fieldname from a query I took the example from to prove that it worked for Namlian, a few posts earlier I explained how to do it.

Did you follow the Link I posted to Jon's explanation of the technique?

Brian

 

[namliam]

@Brian
IsNull([forms]![frmdates]![startdate] And [forms]![frmdates]![enddate])

isnull(Field1 and Field2) ??? That doesnt make sense (to me) please explain to me how this works? And if this works?

 

[Brianwarnock]

How do you mean does it work, would I say it did if it didn't? Have you tried it?

How does it work, If either startdate and/or enddate is null it selects all of the records.
NOW if you mean how does it do that as it doesn't seem logical then I can only say that I don't have access to the original code to see exactly how the functions work. however if you say IIf(IsNull(date1 and Date2 and Date3),"NULL","NOT") the if any are null the answer is NULL

Brian

 

[namliam]

It seems to work, though I dont understand it... And returns a logical therefor shouldnt be null.... However it does work... I tested that before my previous post too... but I still do not understand the logic behind it...

Null AND Something = True in Isnull... Strange to me... But if it works it works.

I would have done:
Isnull(field1) and Isnull(field2)

Now these return to booleans on either side of the AND, that I understand

Maybe I am just dumb?

 

[Brianwarnock]

I would have done isnull(field1) or isnull(field2) , as I said I don't have the base code so I don't know why it works as it does but it does seem that
Isnull( loads of fields ANDed) =True if any is/are Null

Brian

 

[namliam]

and/or what ever...

Strange though... but it works...So point closed I think

 

[jereece]

Brianwarnock - I have been a way for a while but I finally got this to work. Thank you so much. I about pulled out the few hairs I have left trying to figure out how to do this.

Thanks again,
Jim

 

[Simon_MT]

Here's a non-technical approach. At some point you started collecting data so that becomes the default Start Date and todays date the default End Date. You will always get all the records if the user does not change the Start or End dates.

Simon

 

[Brianwarnock]

Thanks for coming back, I did wonder if the discussion between Namliam and me had put you off.

Brian