Entering only a start date in a query form

[Susan L]

My query form has text boxes for StartDate and EndDate. The date range
criteria are working fine. However, I'd like my users to be able to enter a
beginning date and get all record from that date to the present. I have tried
quite a few variations in writing the criteria statement, but haven't
achieved my goal -- yet. In its most recent form, the criteria statement
reads: Like [Forms]![frm_Search]![cboStartDate] & "*" Or Between
[Forms]![frm_Search]![StartDate] And [Forms]![frm_Search]![EndDate]. This
returns all reccords.

Thinking it was logical, I tried: Between [Forms]![frm_Search]![StartDate]
And [Forms]![frm_Search]![EndDate] Or [Forms]![frm_Search]![EndDate] Is Null,
but that didn't work either.

Can anyone advise? (It is probably obvious to all that I'm new at this - but
I wouldn't be as far along as I am without this wonderful resource.)

 

[Martin J]

It would be like

iif (isnull([Forms]![frm_Search]![EndDate]),#12/31/2040#,
[Forms]![frm_Search]![EndDate] )

 

[Susan L]

Thanks for your response, but it doesn't seem to work. When I enter just the
statement you wrote (taking out the "Between" statement), they query pulls no
records if I enter just a start date -- and pulls just records with the end
date when I enter a Start and End date. Am I doing something wrong?

It would be like

iif (isnull([Forms]![frm_Search]![EndDate]),#12/31/2040#,
[Forms]![frm_Search]![EndDate] )

My query form has text boxes for StartDate and EndDate. The date range
criteria are working fine. However, I'd like my users to be able to enter a
beginning date and get all record from that date to the present. I have tried
quite a few variations in writing the criteria statement, but haven't
achieved my goal -- yet. In its most recent form, the criteria statement
reads: Like [Forms]![frm_Search]![cboStartDate] & "*" Or Between
[Forms]![frm_Search]![StartDate] And [Forms]![frm_Search]![EndDate]. This
returns all reccords.

Thinking it was logical, I tried: Between [Forms]![frm_Search]![StartDate]
And [Forms]![frm_Search]![EndDate] Or [Forms]![frm_Search]![EndDate] Is Null,
but that didn't work either.

Can anyone advise? (It is probably obvious to all that I'm new at this - but
I wouldn't be as far along as I am without this wonderful resource.)

 

[Martin J]

I meant you can use a between with what i wrote before and your begin date or
your end date like this

Between IIf(IsNull([forms]![invdate]![startdate]),#12/1/2003#,#6/1/2004#)
And #12/31/2004#

All date literals (enclosed in ##) can be exchanged for dates on a form. For
today use 'date()'.

Sorry for the delay

HTH
Martin

Thanks for your response, but it doesn't seem to work. When I enter just the
statement you wrote (taking out the "Between" statement), they query pulls no
records if I enter just a start date -- and pulls just records with the end
date when I enter a Start and End date. Am I doing something wrong?

It would be like

iif (isnull([Forms]![frm_Search]![EndDate]),#12/31/2040#,
[Forms]![frm_Search]![EndDate] )

My query form has text boxes for StartDate and EndDate. The date range
criteria are working fine. However, I'd like my users to be able to enter a
beginning date and get all record from that date to the present. I have tried
quite a few variations in writing the criteria statement, but haven't
achieved my goal -- yet. In its most recent form, the criteria statement
reads: Like [Forms]![frm_Search]![cboStartDate] & "*" Or Between
[Forms]![frm_Search]![StartDate] And [Forms]![frm_Search]![EndDate]. This
returns all reccords.

Thinking it was logical, I tried: Between [Forms]![frm_Search]![StartDate]
And [Forms]![frm_Search]![EndDate] Or [Forms]![frm_Search]![EndDate] Is Null,
but that didn't work either.

Can anyone advise? (It is probably obvious to all that I'm new at this - but
I wouldn't be as far along as I am without this wonderful resource.)

 

[Susan L]

Thanks for the clarification. I'll try that out. And thanks for explaining
date literal. Little by little I'm learning! I'll post back after trying out.

I meant you can use a between with what i wrote before and your begin date or
your end date like this

Between IIf(IsNull([forms]![invdate]![startdate]),#12/1/2003#,#6/1/2004#)
And #12/31/2004#

All date literals (enclosed in ##) can be exchanged for dates on a form. For
today use 'date()'.

Sorry for the delay

HTH
Martin

Thanks for your response, but it doesn't seem to work. When I enter just the
statement you wrote (taking out the "Between" statement), they query pulls no
records if I enter just a start date -- and pulls just records with the end
date when I enter a Start and End date. Am I doing something wrong?

It would be like

iif (isnull([Forms]![frm_Search]![EndDate]),#12/31/2040#,
[Forms]![frm_Search]![EndDate] )

:

My query form has text boxes for StartDate and EndDate. The date range
criteria are working fine. However, I'd like my users to be able to enter a
beginning date and get all record from that date to the present. I have tried
quite a few variations in writing the criteria statement, but haven't
achieved my goal -- yet. In its most recent form, the criteria statement
reads: Like [Forms]![frm_Search]![cboStartDate] & "*" Or Between
[Forms]![frm_Search]![StartDate] And [Forms]![frm_Search]![EndDate]. This
returns all reccords.

Thinking it was logical, I tried: Between [Forms]![frm_Search]![StartDate]
And [Forms]![frm_Search]![EndDate] Or [Forms]![frm_Search]![EndDate] Is Null,
but that didn't work either.

Can anyone advise? (It is probably obvious to all that I'm new at this - but
I wouldn't be as far along as I am without this wonderful resource.)