Dates in a form for filtering Report query

[Jan T.]

I have a form "Period" with two text boxes. One for startDate
and one for EndDate. I want to use that form to limit the query
for my Report by the dates. However, when I refer to the
Form it does not seem to understand it is a date? I use the following
statement in my query:

SELECT Opphold.CheckIn
FROM Opphold
WHERE (((Opphold.CheckIn)<=[Forms]![Perioder].[txtStartDate]));

I also tried to convert it to a date like the following:
CDate(<=[Forms]![Perioder].[txtStartDate]))) but that did not work?

What should I do in order to the query to read the condition or dates from
my forms?

(The only thing I CAN get to work is if I make a function in vba that return
the value or date from the form and refering to the function in my Report
query:
Function startingDate(myStart as Date) as Date
startingDate = Forms("Period").txtStartDate
End Function)

Appreciate your answer.
Regards
Jan T.

 

[fredg]

I have a form "Period" with two text boxes. One for startDate
and one for EndDate. I want to use that form to limit the query
for my Report by the dates. However, when I refer to the
Form it does not seem to understand it is a date? I use the following
statement in my query:

SELECT Opphold.CheckIn
FROM Opphold
WHERE (((Opphold.CheckIn)<=[Forms]![Perioder].[txtStartDate]));

I also tried to convert it to a date like the following:
CDate(<=[Forms]![Perioder].[txtStartDate]))) but that did not work?

What should I do in order to the query to read the condition or dates from
my forms?

(The only thing I CAN get to work is if I make a function in vba that return
the value or date from the form and refering to the function in my Report
query:
Function startingDate(myStart as Date) as Date
startingDate = Forms("Period").txtStartDate
End Function)

Appreciate your answer.
Regards
Jan T.

You seem to have a discrepancy in the name of the form.
1) Your opening statement .... I have a form "Period"
2) Your Where clause .... <=[Forms]![Perioder].[txtStartDate]))
3) Your Function .... startingDate = Forms("Period").txtStartDate

Perioder is not the same as Period. If the name of the form is
actually "Period" the query will not be able to find the form nor the
date.

The correct Where clause syntax to return records between 2 dates
should be:
WHERE Opphold.CheckIn Between [Forms]![Period]![txtStartDate] AND
[Forms]![Period]![txtEndDate];

I would also suggest you make sure the 2 controls in the form have
their format property set to a valid US (mm/dd/yyyy) or ISO
(yyyy-mm-dd) date format.
Also, make sure the form is open when the query is run.

 

[Jan T.]

I have a form "Period" with two text boxes. One for startDate
and one for EndDate. I want to use that form to limit the query
for my Report by the dates. However, when I refer to the
Form it does not seem to understand it is a date? I use the following
statement in my query:

SELECT Opphold.CheckIn
FROM Opphold
WHERE (((Opphold.CheckIn)<=[Forms]![Perioder].[txtStartDate]));

I also tried to convert it to a date like the following:
CDate(<=[Forms]![Perioder].[txtStartDate]))) but that did not work?

What should I do in order to the query to read the condition or dates
from
my forms?

(The only thing I CAN get to work is if I make a function in vba that
return
the value or date from the form and refering to the function in my Report
query:
Function startingDate(myStart as Date) as Date
startingDate = Forms("Period").txtStartDate
End Function)

Appreciate your answer.
Regards
Jan T.

You seem to have a discrepancy in the name of the form.
1) Your opening statement .... I have a form "Period"
2) Your Where clause .... <=[Forms]![Perioder].[txtStartDate]))
3) Your Function .... startingDate = Forms("Period").txtStartDate

Perioder is not the same as Period. If the name of the form is
actually "Period" the query will not be able to find the form nor the
date.

The correct Where clause syntax to return records between 2 dates
should be:
WHERE Opphold.CheckIn Between [Forms]![Period]![txtStartDate] AND
[Forms]![Period]![txtEndDate];

I would also suggest you make sure the 2 controls in the form have
their format property set to a valid US (mm/dd/yyyy) or ISO
(yyyy-mm-dd) date format.
Also, make sure the form is open when the query is run.

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Oobs Perioder was an writing error and now corrected. However, I still
can not understand that the form contains a date? I also tried different
formats like US m/d/yyyy or European dd.mm.yyyy but none of them
work. I am getting the parameter Input Box because I looks for a date?

Regards
Jan T.

 

[Jan T.]

Found out. It was because of a writing error it did not work. Did not see
that.
Thanx for your help. Jan T

I have a form "Period" with two text boxes. One for startDate
and one for EndDate. I want to use that form to limit the query
for my Report by the dates. However, when I refer to the
Form it does not seem to understand it is a date? I use the following
statement in my query:

SELECT Opphold.CheckIn
FROM Opphold
WHERE (((Opphold.CheckIn)<=[Forms]![Perioder].[txtStartDate]));

I also tried to convert it to a date like the following:
CDate(<=[Forms]![Perioder].[txtStartDate]))) but that did not work?

What should I do in order to the query to read the condition or dates
from
my forms?

(The only thing I CAN get to work is if I make a function in vba that
return
the value or date from the form and refering to the function in my
Report
query:
Function startingDate(myStart as Date) as Date
startingDate = Forms("Period").txtStartDate
End Function)

Appreciate your answer.
Regards
Jan T.

You seem to have a discrepancy in the name of the form.
1) Your opening statement .... I have a form "Period"
2) Your Where clause .... <=[Forms]![Perioder].[txtStartDate]))
3) Your Function .... startingDate = Forms("Period").txtStartDate

Perioder is not the same as Period. If the name of the form is
actually "Period" the query will not be able to find the form nor the
date.

The correct Where clause syntax to return records between 2 dates
should be:
WHERE Opphold.CheckIn Between [Forms]![Period]![txtStartDate] AND
[Forms]![Period]![txtEndDate];

I would also suggest you make sure the 2 controls in the form have
their format property set to a valid US (mm/dd/yyyy) or ISO
(yyyy-mm-dd) date format.
Also, make sure the form is open when the query is run.

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Oobs Perioder was an writing error and now corrected. However, I still
can not understand that the form contains a date? I also tried different
formats like US m/d/yyyy or European dd.mm.yyyy but none of them
work. I am getting the parameter Input Box because I looks for a date?

Regards
Jan T.

 

[De Jager]

I have a form "Period" with two text boxes. One for startDate
and one for EndDate. I want to use that form to limit the query
for my Report by the dates. However, when I refer to the
Form it does not seem to understand it is a date? I use the following
statement in my query:

SELECT Opphold.CheckIn
FROM Opphold
WHERE (((Opphold.CheckIn)<=[Forms]![Perioder].[txtStartDate]));

I also tried to convert it to a date like the following:
CDate(<=[Forms]![Perioder].[txtStartDate]))) but that did not work?

What should I do in order to the query to read the condition or dates from
my forms?

(The only thing I CAN get to work is if I make a function in vba that
return
the value or date from the form and refering to the function in my Report
query:
Function startingDate(myStart as Date) as Date
startingDate = Forms("Period").txtStartDate
End Function)

Appreciate your answer.
Regards
Jan T.