Auto-Enter Value Based on Date Range

[chattavegas]

I want to auto-enter a number value based on if a date falls with a specific date range.

For instance, if the date is between July 1, 1980 and June 30, 1990, the value should be $25.00, if the date is between July 1, 1970 and June 30, 1980, the value should be $20.00, etc, etc.

I tried this, but it doesn't seem to work (v_Date is the variable) :

Case ( v_Date ? "7/1/1980" and v_Date ? "6/30/1990", "25.00",

v_Date ? "7/1/1970" and v_Date ? "6/30/1980", "20.00")

Also, does it matter if v_Date is a global field or not?

-Scott

[chattavegas]

I want to auto-enter a number value based on if a date falls with a specific date range.

For instance, if the date is between July 1, 1980 and June 30, 1990, the value should be $25.00, if the date is between July 1, 1970 and June 30, 1980, the value should be $20.00, etc, etc.

I tried this, but it doesn't seem to work (v_Date is the variable) :

Case ( v_Date ? "7/1/1980" and v_Date ? "6/30/1990", "25.00",

v_Date ? "7/1/1970" and v_Date ? "6/30/1980", "20.00")

Also, does it matter if v_Date is a global field or not?

-Scott

[chattavegas]

I want to auto-enter a number value based on if a date falls with a specific date range.

For instance, if the date is between July 1, 1980 and June 30, 1990, the value should be $25.00, if the date is between July 1, 1970 and June 30, 1980, the value should be $20.00, etc, etc.

I tried this, but it doesn't seem to work (v_Date is the variable) :

Case ( v_Date ? "7/1/1980" and v_Date ? "6/30/1990", "25.00",

v_Date ? "7/1/1970" and v_Date ? "6/30/1980", "20.00")

Also, does it matter if v_Date is a global field or not?

-Scott

[RalphL]

Auto enter happens when a record is created. Try making this a calculated field. I assume the ?'s are >= & <=. Also your numbers should not be in quotes and the trailing zeros are unnecessary.

[RalphL]

Auto enter happens when a record is created. Try making this a calculated field. I assume the ?'s are >= & <=. Also your numbers should not be in quotes and the trailing zeros are unnecessary.

[RalphL]

Auto enter happens when a record is created. Try making this a calculated field. I assume the ?'s are >= & <=. Also your numbers should not be in quotes and the trailing zeros are unnecessary.

[chattavegas]

I eliminated the quotes and made it a calculated field, and it still doesn't work. Any other thoughts?

Also, here's the current calc:

Case ( v_Date >= 7/1/1980 and v_Date <= 6/30/1990, "25",

v_Date >= "7/1/1970" and v_Date <= "6/30/1980", "20" )

-Scott

[chattavegas]

I eliminated the quotes and made it a calculated field, and it still doesn't work. Any other thoughts?

Also, here's the current calc:

Case ( v_Date >= 7/1/1980 and v_Date <= 6/30/1990, "25",

v_Date >= "7/1/1970" and v_Date <= "6/30/1980", "20" )

-Scott

[chattavegas]

I eliminated the quotes and made it a calculated field, and it still doesn't work. Any other thoughts?

Also, here's the current calc:

Case ( v_Date >= 7/1/1980 and v_Date <= 6/30/1990, "25",

v_Date >= "7/1/1970" and v_Date <= "6/30/1980", "20" )

-Scott

[mr_vodka]

Try this.

Case( v_Date >= TextToDate("07/01/1980") and v_Date <= TextToDate("06/30/1990") , 25, v_Date >= TextToDate("07/01/1970") and v_Date <= TextToDate("06/30/1980"), 20)

[mr_vodka]

Try this.

Case( v_Date >= TextToDate("07/01/1980") and v_Date <= TextToDate("06/30/1990") , 25, v_Date >= TextToDate("07/01/1970") and v_Date <= TextToDate("06/30/1980"), 20)

[mr_vodka]

Try this.

Case( v_Date >= TextToDate("07/01/1980") and v_Date <= TextToDate("06/30/1990") , 25, v_Date >= TextToDate("07/01/1970") and v_Date <= TextToDate("06/30/1980"), 20)

[-Queue-]

Just to note, Date( 7, 1, 1980 ) is more reliable than TextToDate("7/1/1980") since it doesn't rely on system or regional settings.

[-Queue-]

Just to note, Date( 7, 1, 1980 ) is more reliable than TextToDate("7/1/1980") since it doesn't rely on system or regional settings.

[-Queue-]

Just to note, Date( 7, 1, 1980 ) is more reliable than TextToDate("7/1/1980") since it doesn't rely on system or regional settings.

[chattavegas]

Yes, that works! The winning calc is as follows:

Case( v_Date >= Date(7,1,1980) and v_Date <= Date(6,30,1990), "25",

v_Date >= Date(7,1,1970) and v_Date <= Date(6,30,1980), "20")

Thanks for all the responses,

-Scott

[chattavegas]

Yes, that works! The winning calc is as follows:

Case( v_Date >= Date(7,1,1980) and v_Date <= Date(6,30,1990), "25",

v_Date >= Date(7,1,1970) and v_Date <= Date(6,30,1980), "20")

Thanks for all the responses,

-Scott

[chattavegas]

Yes, that works! The winning calc is as follows:

Case( v_Date >= Date(7,1,1980) and v_Date <= Date(6,30,1990), "25",

v_Date >= Date(7,1,1970) and v_Date <= Date(6,30,1980), "20")

Thanks for all the responses,

-Scott

[-Queue-]

Note also that your numeric results need not be in quotes since they are not textual. It will not hurt if they are though.

[-Queue-]

Note also that your numeric results need not be in quotes since they are not textual. It will not hurt if they are though.

[-Queue-]

Note also that your numeric results need not be in quotes since they are not textual. It will not hurt if they are though.