Help with conditional auto-populating

  • Thread starter Thread starter oftenconfused
  • Start date Start date
O

oftenconfused

Help!!!

Here's what I'm trying to do:

There is Field A, which is the date a project is due, and Field B, which is
the later of today's date or Field A.

I built the following expression: =If([Field A]>Date(),[Field A],Date()),
which I added to the table properties of Field B as a "Validation
Rule"...but, when I entered the expression and tried to save, it kicked back
the following message:

Syntax Error (missing operator) in table-level validation expression.

Help!!! What am I doing wrong?

Thanks!!!

Field B prints out the date the project is due

I want
 
Often,

I think you have misinterpreted the purpose, and also the correct
operation, of the Validation Rule property. What you are trying to do
here is not applicable to Validation Rule. And anyway, incorrect syntax
with the = sign.

A Validation Rule is an expression that evaluates to True or False. Its
purpose is to enforce certain data rules. It does not manipulate or
calculate or determine the display of data.

In any case, it seems to me that the Field B value is *derived from*
Field A, and as such should not really be stored in a table. I believe
you should remove Field B from the table completely. And then you can
return the Field B value whenever you need it, either in a calculated
field in a query, or via the Contrl Source of an unbound textbox on a
form or report.
 
Steven,

You can see that I'm a novice in this...although, I've done a pretty good
job so far!

My next question is,

How do I either create a calculated field in a query or do this via the
Contrl Source of an unbound textbox on a form or report. Is a calculated
field the same as a "build expression?"

Thanks!!!



Steve Schapel said:
Often,

I think you have misinterpreted the purpose, and also the correct
operation, of the Validation Rule property. What you are trying to do
here is not applicable to Validation Rule. And anyway, incorrect syntax
with the = sign.

A Validation Rule is an expression that evaluates to True or False. Its
purpose is to enforce certain data rules. It does not manipulate or
calculate or determine the display of data.

In any case, it seems to me that the Field B value is *derived from*
Field A, and as such should not really be stored in a table. I believe
you should remove Field B from the table completely. And then you can
return the Field B value whenever you need it, either in a calculated
field in a query, or via the Contrl Source of an unbound textbox on a
form or report.

--
Steve Schapel, Microsoft Access MVP
Help!!!

Here's what I'm trying to do:

There is Field A, which is the date a project is due, and Field B, which is
the later of today's date or Field A.

I built the following expression: =If([Field A]>Date(),[Field A],Date()),
which I added to the table properties of Field B as a "Validation
Rule"...but, when I entered the expression and tried to save, it kicked back
the following message:

Syntax Error (missing operator) in table-level validation expression.

Help!!! What am I doing wrong?

Thanks!!!

Field B prints out the date the project is due

I want
Click to expand...
Click to expand...
 
Often,

If you have a form which is based on your table, and you have a Field A
with data in it, you can add a textbox to the form. And then, look at
the Contrl Source property of the textbox, and in there enter exactly
the same as what you had in your original question:
=If([Field A]>Date(),[Field A],Date())

Alternatively, you can make a query, based on your table. And then, in
the Field row of a blank column in the query design grid, you enter
similar, but without the = sign, and also put something to alias the
calculated field, like this:
TheNameYouWant: If([Field A]>Date(),[Field A],Date())
 
Steve,

Thank you for being so patient. I tried your alternative suggestion, and
got the following error message: "Undefined function 'If' in expression"

Specifically, this is what I typed into a completely blank column:

Field: PrintDate: If([EXT Suspense Date]>Date(),[EXT Suspense Date],Date())
Table: [left blank]
Sort: [left blank]
Show: [left unchecked]
Criteria: [left blank]

Honestly, I don't know what it could be. Could it be that:
(1) the field "EXT Suspense Date" in "Table" is formatted as a date?
(2) the fact that I have spaces in the name "EXT Suspense Date"?

I'm just about ready to jump out the window.

Thanks!
Often

Steve Schapel said:
Often,

If you have a form which is based on your table, and you have a Field A
with data in it, you can add a textbox to the form. And then, look at
the Contrl Source property of the textbox, and in there enter exactly
the same as what you had in your original question:
=If([Field A]>Date(),[Field A],Date())

Alternatively, you can make a query, based on your table. And then, in
the Field row of a blank column in the query design grid, you enter
similar, but without the = sign, and also put something to alias the
calculated field, like this:
TheNameYouWant: If([Field A]>Date(),[Field A],Date())

--
Steve Schapel, Microsoft Access MVP
Steven,

You can see that I'm a novice in this...although, I've done a pretty good
job so far!

My next question is,

How do I either create a calculated field in a query or do this via the
Contrl Source of an unbound textbox on a form or report. Is a calculated
field the same as a "build expression?"
Click to expand...
Click to expand...
 
Often,

I am very sorry, this is my fault. I copy/pasted from your original
post, and simply didn't notice the error. The name of the function is
IIf not If i.e. two Is...
PrintDate: IIf([EXT Suspense Date]>Date(),[EXT Suspense Date],Date())
 
Back
Top