Date range with from/to dates in another file

[Guest]

I have a table set up that has only two fields in it, from date and to date.
I want to be able to use these dates in my Between And expression in multiple
queries in order to find data that is between those two dates instead of
having to go into each query and fill in the specific dates.

I have tried doing it like this:
Between [tblDateRange]![Frm_Dat] And [tblDateRange]![To_Dat]

tblDateRange is my table with the two fields and Frm_Dat and To_Dat are the
date fields.

What am I doing wrong or am I going about this entirely wrong.

Thanks in advance,
Peggy

 

[Guest]

Try this, assuming that you have only one record in this table

Between DLookup("[Frm_Dat]","[tblDateRange]") And
Dlookup("[To_Dat]","[tblDateRange]")

 

[Jeff Boyce]

Why a table? Another approach is to create a form in which you can
enter/select dates, then refer to the form's controls in your query, with
something like:

Between [frmYourForm]![YourFromDate] And [frmYourForm]![YourToDate]

Regards

Jeff Boyce
<Office/Access MVP>

 

[Guest]

I have tried both methods and am really struggling. I am not an Access
expert, but learning.

Let me give more information.

I have a table, tbl_CHGS, with charges in it and they have an entry date,
which I will name the field CHG_ENTDT

I have another table that is just two fields and one record. One field is
the from date and the other is the to date They are named FRM_DT and TO_DT
and the table is tblDateRange.

In my query I am not sure where I am supposed to put this. Do I create a new
field and put the DLookup in the expression there or does it go in the
criteria. How would I do the criteria portion of the DLookup? Any help is
going to be greatly appreciated as I have spent all afternoon on this.

 

[Van T. Dinh]

Have you included the Table [tblDateRange] in the FROM clause of your Query
/ SQL?

With your criteria, you need to include the Table in the FROM clause. OTOH,
Jeff's methos is what I would use instead on a one-Record Table
[tblDateRange]

 

[Guest]

I tried this method and the query is asking for a parameter for the from date
and I am sure for the to date also. I have my Between And statement in the
criteria for the CHG_ENTRYDT. Is that wrong?

Why a table? Another approach is to create a form in which you can
enter/select dates, then refer to the form's controls in your query, with
something like:

Between [frmYourForm]![YourFromDate] And [frmYourForm]![YourToDate]

Regards

Jeff Boyce
<Office/Access MVP>

I have a table set up that has only two fields in it, from date and to
date.
I want to be able to use these dates in my Between And expression in
multiple
queries in order to find data that is between those two dates instead of
having to go into each query and fill in the specific dates.

I have tried doing it like this:
Between [tblDateRange]![Frm_Dat] And [tblDateRange]![To_Dat]

tblDateRange is my table with the two fields and Frm_Dat and To_Dat are
the
date fields.

What am I doing wrong or am I going about this entirely wrong.

Thanks in advance,
Peggy

 

[Van T. Dinh]

Suggest you post the SQL String of your Query so that other can analyze and
suggest possible solutions.

 

[John Vinson]

I tried this method and the query is asking for a parameter for the from date
and I am sure for the to date also. I have my Between And statement in the
criteria for the CHG_ENTRYDT. Is that wrong?

If you have criteria like

BETWEEN [Forms]![YourFormName]![txtFrom]

John W. Vinson[MVP]

 

[John Vinson]

I tried this method and the query is asking for a parameter for the from date
and I am sure for the to date also. I have my Between And statement in the
criteria for the CHG_ENTRYDT. Is that wrong?

If you have a criterion like

BETWEEN [Forms]![YourFormName]![txtFrom] AND
[Forms]![YourFormName]![txtTo]

it will pull the dates from the textboxes named txtFrom and txtTo on
the form name YourFormName - *IF* that form is open. It won't open the
form for you though - instead it will prompt you.

Is the form in fact open? And are your parameters spelled exactly like
the names of the controls on the form?

John W. Vinson[MVP]

 

[Guest]

Thank you to all who replied and helped me with this question.

The first method with the DLookup works just great. It didn't work yesterday
because we were having ODBC issues with one of the tables that I was using.

The second method using the fields in a form works great also. I just didn't
know that the form needed to be opened when I ran the query. I have very
limited knowledge of Access.

Again thank you and I appreciate the efforts.