WHERE Clause

[Jake]

Hi

I have a field in my query called [completionDate]

Depending on whether the user has clicked on the listbox value "Current" or
Next" I wish to restrict the values to >=Date() and >=Date()+7 respectively.

What would I type into the 'Criteria' row of the query grid (or indeed the
SQL) to accomplish this?

I can refer to my forms listbox values as....
[Forms]![frm_report]![lst_Reports]="Current"
[Forms]![frm_report]![lst_Reports]="Next"

Many thanks

Jake

 

[fredg]

Hi

I have a field in my query called [completionDate]

Depending on whether the user has clicked on the listbox value "Current" or
Next" I wish to restrict the values to >=Date() and >=Date()+7 respectively.

What would I type into the 'Criteria' row of the query grid (or indeed the
SQL) to accomplish this?

I can refer to my forms listbox values as....
[Forms]![frm_report]![lst_Reports]="Current"
[Forms]![frm_report]![lst_Reports]="Next"

Many thanks

Jake

Jake,
If there are just the 2 choices...
As Criteria for the Date field:

 

[Jake]

Hi Fred

Thanks for that BUT...

I need GREATER THAN Date() and GREATER THAN Date()+7

I can't seem to get this concept to work in the criteria part of the query
grid

Any ideas?

Cheers

Jake.

Hi

I have a field in my query called [completionDate]

Depending on whether the user has clicked on the listbox value "Current" or
Next" I wish to restrict the values to >=Date() and >=Date()+7 respectively.

What would I type into the 'Criteria' row of the query grid (or indeed the
SQL) to accomplish this?

I can refer to my forms listbox values as....
[Forms]![frm_report]![lst_Reports]="Current"
[Forms]![frm_report]![lst_Reports]="Next"

Many thanks

Jake

Jake,
If there are just the 2 choices...
As Criteria for the Date field:

=IIf(forms!frmReport!lstReports="Current",Date(),Date()+7)

 

[fredg]

Hi Fred

Thanks for that BUT...

I need GREATER THAN Date() and GREATER THAN Date()+7

I can't seem to get this concept to work in the criteria part of the query
grid

Any ideas?

Cheers

Jake.

Hi

I have a field in my query called [completionDate]

Depending on whether the user has clicked on the listbox value "Current" or
Next" I wish to restrict the values to >=Date() and >=Date()+7 respectively.

What would I type into the 'Criteria' row of the query grid (or indeed the
SQL) to accomplish this?

I can refer to my forms listbox values as....
[Forms]![frm_report]![lst_Reports]="Current"
[Forms]![frm_report]![lst_Reports]="Next"

Many thanks

Jake

Jake,
If there are just the 2 choices...
As Criteria for the Date field:

=IIf(forms!frmReport!lstReports="Current",Date(),Date()+7)

Jake,
I'm afraid I don't quite understand.
In your original post you wrote .....

Depending on whether the user has clicked on the listbox value "Current"
or Next" I wish to restrict the values to >=Date() and >=Date()+7
respectively.

which I believe to mean if the user selects "Current" the criteria is
greater than or equal to Date(), otherwise the criteria is greater
than or equal to Date()+7.
That's what you wrote.

That's what the >= at the beginning of the criteria means.
That's what I gave you. Did you try it?
If you only want Greater than Date, simply remove the = sign.

 

[John Vinson]

Hi Fred

Thanks for that BUT...

I need GREATER THAN Date() and GREATER THAN Date()+7

I can't seem to get this concept to work in the criteria part of the query
grid

Any ideas?

DateAdd("d", Date(), Switch([Forms]![frm_report]![lst_Reports]="Current", 0,

[Forms]![frm_report]![lst_Reports]="Next", 7))

 

[Jake]

Thank you fred

I didn't notice u had put >= at the front

Doh !!

Sorry, and thanks again

Jake

Hi Fred

Thanks for that BUT...

I need GREATER THAN Date() and GREATER THAN Date()+7

I can't seem to get this concept to work in the criteria part of the query
grid

Any ideas?

Cheers

Jake.

On Wed, 18 Feb 2004 19:00:34 -0000, Jake wrote:

Hi

I have a field in my query called [completionDate]

Depending on whether the user has clicked on the listbox value

"Current"
or

Next" I wish to restrict the values to >=Date() and >=Date()+7 respectively.

What would I type into the 'Criteria' row of the query grid (or indeed the
SQL) to accomplish this?

I can refer to my forms listbox values as....
[Forms]![frm_report]![lst_Reports]="Current"
[Forms]![frm_report]![lst_Reports]="Next"

Many thanks

Jake

Jake,
If there are just the 2 choices...
As Criteria for the Date field:
=IIf(forms!frmReport!lstReports="Current",Date(),Date()+7)

Jake,
I'm afraid I don't quite understand.
In your original post you wrote .....

Depending on whether the user has clicked on the listbox value "Current"
or Next" I wish to restrict the values to >=Date() and >=Date()+7
respectively.

which I believe to mean if the user selects "Current" the criteria is
greater than or equal to Date(), otherwise the criteria is greater
than or equal to Date()+7.
That's what you wrote.

That's what the >= at the beginning of the criteria means.
That's what I gave you. Did you try it?
If you only want Greater than Date, simply remove the = sign.