Option Group Question.

[Guest]

Please be patient with me, I have looked for this question in the forums and
could not find an answer. Also I am very new to Access and Forms.. the boss
chucked this task at me.

I have a form with a option group (optcontrol) that has 5 options in it. I
need to edit my query to only show dates from today and back based on my
option choice.

1= Optall (hopefully all dates)
2= optlast30 (should show data from today and back 30days)
3= optlast60
4= optlast90
5= optlast120

The code I have tested.

I edit the critera on the date field with this code and got an error.

=Chooce([optcontrol],"", "Between Date() and Date()-30", "Between Date() and
Date()-60", "so on and so on") The "" in the first choice should
give me all dates ( or at least in my mind)

 

[Duane Hookom]

The function name is Choose(). You might be able to set a criteria to
something like:
Between Date() And DateAdd("d",-Choose([optControl], 99999, 30,60,90,120),
Date())

 

[Guest]

Duane
Thanks for the quick reply. when I typed out my question, I miss typed.
Your response was perfect!! I copyied and pasted it in the criteria and it
worked.

Thanks
Walks

--
Peronold:Keep Your Feet On The Ground

The function name is Choose(). You might be able to set a criteria to
something like:
Between Date() And DateAdd("d",-Choose([optControl], 99999, 30,60,90,120),
Date())

--
Duane Hookom
MS Access MVP

Please be patient with me, I have looked for this question in the forums
and
could not find an answer. Also I am very new to Access and Forms.. the
boss
chucked this task at me.

I have a form with a option group (optcontrol) that has 5 options in it.
I
need to edit my query to only show dates from today and back based on my
option choice.

1= Optall (hopefully all dates)
2= optlast30 (should show data from today and back 30days)
3= optlast60
4= optlast90
5= optlast120

The code I have tested.

I edit the critera on the date field with this code and got an error.

=Chooce([optcontrol],"", "Between Date() and Date()-30", "Between Date()
and
Date()-60", "so on and so on") The "" in the first choice should
give me all dates ( or at least in my mind)

 

[Guest]

Duane
Ok the date ranges are being populated in my SubForm correctly, but
when I use my Macro to open a report based on my Query, it asks me for my
OptControl option. This also asks me for and OptControl option when I just
try to open my queiry.

Walks
--
Peronold:Keep Your Feet On The Ground

The function name is Choose(). You might be able to set a criteria to
something like:
Between Date() And DateAdd("d",-Choose([optControl], 99999, 30,60,90,120),
Date())

--
Duane Hookom
MS Access MVP

Please be patient with me, I have looked for this question in the forums
and
could not find an answer. Also I am very new to Access and Forms.. the
boss
chucked this task at me.

I have a form with a option group (optcontrol) that has 5 options in it.
I
need to edit my query to only show dates from today and back based on my
option choice.

1= Optall (hopefully all dates)
2= optlast30 (should show data from today and back 30days)
3= optlast60
4= optlast90
5= optlast120

The code I have tested.

I edit the critera on the date field with this code and got an error.

=Chooce([optcontrol],"", "Between Date() and Date()-30", "Between Date()
and
Date()-60", "so on and so on") The "" in the first choice should
give me all dates ( or at least in my mind)

 

[Duane Hookom]

I wasn't sure where you were attempting to use the expression or what
optControl was. If it is a control on a form then use:
Between Date() And DateAdd("d",-Choose(Forms!frmYourForm![optControl],
99999, 30,60,90,120), Date())

--
Duane Hookom
MS Access MVP

Duane
Ok the date ranges are being populated in my SubForm correctly, but
when I use my Macro to open a report based on my Query, it asks me for my
OptControl option. This also asks me for and OptControl option when I
just
try to open my queiry.

Walks
--
Peronold:Keep Your Feet On The Ground

The function name is Choose(). You might be able to set a criteria to
something like:
Between Date() And DateAdd("d",-Choose([optControl], 99999,
30,60,90,120),
Date())

--
Duane Hookom
MS Access MVP

Please be patient with me, I have looked for this question in the
forums
and
could not find an answer. Also I am very new to Access and Forms.. the
boss
chucked this task at me.

I have a form with a option group (optcontrol) that has 5 options in
it.
I
need to edit my query to only show dates from today and back based on
my
option choice.

1= Optall (hopefully all dates)
2= optlast30 (should show data from today and back 30days)
3= optlast60
4= optlast90
5= optlast120

The code I have tested.

I edit the critera on the date field with this code and got an error.

=Chooce([optcontrol],"", "Between Date() and Date()-30", "Between
Date()
and
Date()-60", "so on and so on") The "" in the first choice
should
give me all dates ( or at least in my mind)

 

[Guest]

Thanks for your Time Duane...

Here is my code and it works in my form.

Between Date() And
DateAdd("d",-Choose([Forms]![frmShortTest]![OptControl],99999,30,60,90,120),Date())

This still asks me for an option when I open my query. Maybe its how I have
my Select Query setup.

Field: Date
Table: Shorts_on_truck
Total: Group By
Sort: Ascending
Shown = checked box
Then in the criteria I have the above code.

Do I need to change the "Total" field to expression or var???

--
Peronold:Keep Your Feet On The Ground

I wasn't sure where you were attempting to use the expression or what
optControl was. If it is a control on a form then use:
Between Date() And DateAdd("d",-Choose(Forms!frmYourForm![optControl],
99999, 30,60,90,120), Date())

--
Duane Hookom
MS Access MVP

Duane
Ok the date ranges are being populated in my SubForm correctly, but
when I use my Macro to open a report based on my Query, it asks me for my
OptControl option. This also asks me for and OptControl option when I
just
try to open my queiry.

Walks
--
Peronold:Keep Your Feet On The Ground

The function name is Choose(). You might be able to set a criteria to
something like:
Between Date() And DateAdd("d",-Choose([optControl], 99999,
30,60,90,120),
Date())

--
Duane Hookom
MS Access MVP

Please be patient with me, I have looked for this question in the
forums
and
could not find an answer. Also I am very new to Access and Forms.. the
boss
chucked this task at me.

I have a form with a option group (optcontrol) that has 5 options in
it.
I
need to edit my query to only show dates from today and back based on
my
option choice.

1= Optall (hopefully all dates)
2= optlast30 (should show data from today and back 30days)
3= optlast60
4= optlast90
5= optlast120

The code I have tested.

I edit the critera on the date field with this code and got an error.

=Chooce([optcontrol],"", "Between Date() and Date()-30", "Between
Date()
and
Date()-60", "so on and so on") The "" in the first choice
should
give me all dates ( or at least in my mind)

 

[Duane Hookom]

This only works if the form is open. If the form is closed, you will get
prompted for the control value.

--
Duane Hookom
MS Access MVP

Thanks for your Time Duane...

Here is my code and it works in my form.

Between Date() And
DateAdd("d",-Choose([Forms]![frmShortTest]![OptControl],99999,30,60,90,120),Date())

This still asks me for an option when I open my query. Maybe its how I
have
my Select Query setup.

Field: Date
Table: Shorts_on_truck
Total: Group By
Sort: Ascending
Shown = checked box
Then in the criteria I have the above code.

Do I need to change the "Total" field to expression or var???

--
Peronold:Keep Your Feet On The Ground

I wasn't sure where you were attempting to use the expression or what
optControl was. If it is a control on a form then use:
Between Date() And DateAdd("d",-Choose(Forms!frmYourForm![optControl],
99999, 30,60,90,120), Date())

--
Duane Hookom
MS Access MVP

Duane
Ok the date ranges are being populated in my SubForm correctly, but
when I use my Macro to open a report based on my Query, it asks me for
my
OptControl option. This also asks me for and OptControl option when I
just
try to open my queiry.

Walks
--
Peronold:Keep Your Feet On The Ground


:

The function name is Choose(). You might be able to set a criteria to
something like:
Between Date() And DateAdd("d",-Choose([optControl], 99999,
30,60,90,120),
Date())

--
Duane Hookom
MS Access MVP

Please be patient with me, I have looked for this question in the
forums
and
could not find an answer. Also I am very new to Access and Forms..
the
boss
chucked this task at me.

I have a form with a option group (optcontrol) that has 5 options in
it.
I
need to edit my query to only show dates from today and back based
on
my
option choice.

1= Optall (hopefully all dates)
2= optlast30 (should show data from today and back 30days)
3= optlast60
4= optlast90
5= optlast120

The code I have tested.

I edit the critera on the date field with this code and got an
error.

=Chooce([optcontrol],"", "Between Date() and Date()-30", "Between
Date()
and
Date()-60", "so on and so on") The "" in the first choice
should
give me all dates ( or at least in my mind)

 

[Guest]

I have a command button in my form that uses a macro (open report) so the
form is open.


--
Peronold:Keep Your Feet On The Ground

This only works if the form is open. If the form is closed, you will get
prompted for the control value.

--
Duane Hookom
MS Access MVP

Thanks for your Time Duane...

Here is my code and it works in my form.

Between Date() And
DateAdd("d",-Choose([Forms]![frmShortTest]![OptControl],99999,30,60,90,120),Date())

This still asks me for an option when I open my query. Maybe its how I
have
my Select Query setup.

Field: Date
Table: Shorts_on_truck
Total: Group By
Sort: Ascending
Shown = checked box
Then in the criteria I have the above code.

Do I need to change the "Total" field to expression or var???

--
Peronold:Keep Your Feet On The Ground

I wasn't sure where you were attempting to use the expression or what
optControl was. If it is a control on a form then use:
Between Date() And DateAdd("d",-Choose(Forms!frmYourForm![optControl],
99999, 30,60,90,120), Date())

--
Duane Hookom
MS Access MVP


Duane
Ok the date ranges are being populated in my SubForm correctly, but
when I use my Macro to open a report based on my Query, it asks me for
my
OptControl option. This also asks me for and OptControl option when I
just
try to open my queiry.

Walks
--
Peronold:Keep Your Feet On The Ground


:

The function name is Choose(). You might be able to set a criteria to
something like:
Between Date() And DateAdd("d",-Choose([optControl], 99999,
30,60,90,120),
Date())

--
Duane Hookom
MS Access MVP

Please be patient with me, I have looked for this question in the
forums
and
could not find an answer. Also I am very new to Access and Forms..
the
boss
chucked this task at me.

I have a form with a option group (optcontrol) that has 5 options in
it.
I
need to edit my query to only show dates from today and back based
on
my
option choice.

1= Optall (hopefully all dates)
2= optlast30 (should show data from today and back 30days)
3= optlast60
4= optlast90
5= optlast120

The code I have tested.

I edit the critera on the date field with this code and got an
error.

=Chooce([optcontrol],"", "Between Date() and Date()-30", "Between
Date()
and
Date()-60", "so on and so on") The "" in the first choice
should
give me all dates ( or at least in my mind)

 

[Guest]

OK I got it.. Thanks for your help Duane. The issue is.. after I added
your code. I never closed and re-opened so I dont think things saved
correctly.

Walks