WHERE Clause

  • Thread starter Thread starter Jake
  • Start date Start date
J

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
 
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
Click to expand...

Jake,
If there are just the 2 choices...
As Criteria for the Date field:
 
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.
fredg said:
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
Click to expand...

Jake,
If there are just the 2 choices...
As Criteria for the Date field:
=IIf(forms!frmReport!lstReports="Current",Date(),Date()+7)
Click to expand...
Click to expand...
 
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.
fredg said:
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
Click to expand...

Jake,
If there are just the 2 choices...
As Criteria for the Date field:
=IIf(forms!frmReport!lstReports="Current",Date(),Date()+7)
Click to expand...
Click to expand...
Click to expand...
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.
Click to expand...
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.
 
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?
Click to expand...
DateAdd("d", Date(), Switch([Forms]![frm_report]![lst_Reports]="Current", 0,
Click to expand...
[Forms]![frm_report]![lst_Reports]="Next", 7))
 
Thank you fred

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

Doh !!

Sorry, and thanks again

Jake
fredg said:
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.
fredg said:
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
Click to expand...
"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)
Click to expand...
Click to expand...
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.
Click to expand...
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.
Click to expand...
 
Back
Top