Date Boxes on Form to Filter Query Date Field

  • Thread starter Thread starter Carla Thistle
  • Start date Start date
C

Carla Thistle

Hello,

Thanks for reading and I really hope you can help.

I'll list what I have to hopefully give you all the needed info to help.
1. I have a table (tbl 1)
2. I have a query based on this table (qry 1)
3. I have a form based on this query (frm 1)
4. On the form I have put 2 unbounded Date fields(one for a beginning date
and one for an ending date)

I want to be able to search or filter the form to show all records between
these dates, yet show all the records when no date is entered.

Can you help me....pllllleeeeaaaasssse.
Greatfully appreciative,
Carla
 
Yes.
I can help you!

I almost always use syntax like this for criteria:
Like iif(isnull(Forms![FormName][txtName]),'*',Forms![FormName][txtName])

The syntax above does not quite fit your needs because you want to use
Between 2 dates.
So you need to define a min date and a max date.

In your query criteria for the tblDate field use syntax like this:
Between
iif(isnull(Forms![FormName][txtStartDate]),#1/1/1899#,Forms![FormName][txtSt
artDate]) And

iif(isnull(Forms![FormName][txtEndDate]),#1/1/2079#,Forms![FormName][txtEndD
ate])
 
Thank you very much...worked like a charm :).

Carla

Joe Fallon said:
Yes.
I can help you!

I almost always use syntax like this for criteria:
Like iif(isnull(Forms![FormName][txtName]),'*',Forms![FormName][txtName])

The syntax above does not quite fit your needs because you want to use
Between 2 dates.
So you need to define a min date and a max date.

In your query criteria for the tblDate field use syntax like this:
Between
iif(isnull(Forms![FormName][txtStartDate]),#1/1/1899#,Forms![FormName][txtSt
artDate]) And

iif(isnull(Forms![FormName][txtEndDate]),#1/1/2079#,Forms![FormName][txtEndD
ate])
--
Joe Fallon
Access MVP



Carla Thistle said:
Hello,

Thanks for reading and I really hope you can help.

I'll list what I have to hopefully give you all the needed info to help.
1. I have a table (tbl 1)
2. I have a query based on this table (qry 1)
3. I have a form based on this query (frm 1)
4. On the form I have put 2 unbounded Date fields(one for a beginning date
and one for an ending date)

I want to be able to search or filter the form to show all records between
these dates, yet show all the records when no date is entered.

Can you help me....pllllleeeeaaaasssse.
Greatfully appreciative,
Carla
Click to expand...
Click to expand...
 
Back
Top