select a crteria for a txt feild

[jon]

I have a query which works fine but I want to get the user to be able to
chose different criteria.

Date()-90
Date()-120

between 17/02/2006 and 17/06/2006 or various date selected by the user.
So I the following to select the txt
[Forms]![frmGraphSelection]![txtSRSRange] as this txt field holds the text
the query returns nothing, but if I cut and paste the text into the query
it's ok.

Do I need to do anything special?
Advise please

Thanks Jon

 

[John Vinson]

I have a query which works fine but I want to get the user to be able to
chose different criteria.

Date()-90
Date()-120

between 17/02/2006 and 17/06/2006 or various date selected by the user.
So I the following to select the txt
[Forms]![frmGraphSelection]![txtSRSRange] as this txt field holds the text
the query returns nothing, but if I cut and paste the text into the query
it's ok.

Do I need to do anything special?
Advise please

Thanks Jon

Date literals MUST be in American mm/dd/yyyy format, or in an
unambiguous format such as yyyy.mm.dd. In addition, you cannot use a
parameter to pass *operators* such as BETWEEN or expressions.

I'd suggest having two textboxes, txtFrom and txtTo. Set the default
value of txtTo to Date(), and use a query criterion like

= Format([Forms]![frmGraphSelection]![txtFrom], "mm/dd/yyyy") AND < Format(DateAdd("d", 1, [Forms]![frmGraphSelection]![txtFrom], "mm/dd/yyyy"))

The DateAdd will cover the case that the table date contains a time
portion.

John W. Vinson[MVP]

 

[jon]

Sorted thanks
used the 2 text boxes where I will control the inputs and put the criteria
as below

=Format([Forms]![frmGraphSelection]![txtdateFrom],"dd/mm/yyyy") And
<Format([Forms]![frmGraphSelection]![txtdateto],"dd/mm/yyyy")

Jon

I have a query which works fine but I want to get the user to be able to
chose different criteria.

Date()-90
Date()-120

between 17/02/2006 and 17/06/2006 or various date selected by the user.
So I the following to select the txt
[Forms]![frmGraphSelection]![txtSRSRange] as this txt field holds the text
the query returns nothing, but if I cut and paste the text into the query
it's ok.

Do I need to do anything special?
Advise please

Thanks Jon

Date literals MUST be in American mm/dd/yyyy format, or in an
unambiguous format such as yyyy.mm.dd. In addition, you cannot use a
parameter to pass *operators* such as BETWEEN or expressions.

I'd suggest having two textboxes, txtFrom and txtTo. Set the default
value of txtTo to Date(), and use a query criterion like

= Format([Forms]![frmGraphSelection]![txtFrom], "mm/dd/yyyy") AND <
Format(DateAdd("d", 1, [Forms]![frmGraphSelection]![txtFrom],
"mm/dd/yyyy"))

The DateAdd will cover the case that the table date contains a time
portion.

John W. Vinson[MVP]

 

[John Vinson]

Sorted thanks
used the 2 text boxes where I will control the inputs and put the criteria
as below

=Format([Forms]![frmGraphSelection]![txtdateFrom],"dd/mm/yyyy") And
<Format([Forms]![frmGraphSelection]![txtdateto],"dd/mm/yyyy")

Jon

Sorry but that WILL NOT WORK.

As I said, the dates *must* be in American mm/dd/yyyy format. Using
dd/mm/yyyy will give you incorrect results.

John W. Vinson[MVP]

 

[jon]

Hi John
Just found your reply.
The query runs well as posted and I can see no errors in the data.
What might the incorrect result be?
all the dates in the database or in the dd/mm/yyyy format, will that effect
it?

Thanks
Jon

Sorted thanks
used the 2 text boxes where I will control the inputs and put the criteria
as below

=Format([Forms]![frmGraphSelection]![txtdateFrom],"dd/mm/yyyy") And
<Format([Forms]![frmGraphSelection]![txtdateto],"dd/mm/yyyy")

Jon

Sorry but that WILL NOT WORK.

As I said, the dates *must* be in American mm/dd/yyyy format. Using
dd/mm/yyyy will give you incorrect results.

John W. Vinson[MVP]