Making field display dependant on value in other field

[Guest]

I need to make a field (fieldB)in Access show 'Yes' or 'No' according to a date entry in another field(fieldA). I think that I should use the Iif function in a control?

I basically want to say 'If the date in fieldA is over 1 year ago, fieldB should display 'yes' or 'update' or whatever.'

I don't mind if field B is a toggle box or text.
I think that what I need to use is a control but I'm not sure how to use these.

Similarly, I want field C to display the date that is fieldA plus 3 months . How can I programme this like you would in excel?
DateSerial(Year("fieldA"),Month("FieldA")+3,Day("fieldA")) ???

Thank you!

 

[Nikos Yannacopoulos]

Ian,

Though you are not specifically saying so, I understand this is on a form?
If yes:

For the first one, make a new text box and in its ConstrolSource property
(tab Data), instead of selecting a field, type in the following expression:
=Iif(Date() - [fieldA] > 365, "Update","")
If you opt for a checkbox instead, then the expression in the ocntrolsource
property should be:
=Iif(Date() - [fieldA] > 365, True,False)
In either case, If the name of the control on the form is not fieldA, change
to the exact name of the control. This is not too accurate in that it does
not take account of leap years. This can be taken care of if required, but
it would take a very complex expression, or (preferably) a VB function.

Another approach you might use, is to create a label instead, reading Update
or something, and show/hide it accordingly. To do that, you would need to
add the following code in the form's On Current event:
IIf Date() - Me.fieldA > 365 Then
Me.filedB.Visible = True
Else
Me.filedB.Visible = False
End If
Again, change the control names as appropriate if different.

For the second one, again a text box with its ControlSource property set to
the expression you have in your mail, data format to desired date format.

HTH,
Nikos

I need to make a field (fieldB)in Access show 'Yes' or 'No' according to a

date entry in another field(fieldA). I think that I should use the Iif
function in a control?

I basically want to say 'If the date in fieldA is over 1 year ago, fieldB

should display 'yes' or 'update' or whatever.'

I don't mind if field B is a toggle box or text.
I think that what I need to use is a control but I'm not sure how to use these.

Similarly, I want field C to display the date that is fieldA plus 3 months

.. How can I programme this like you would in excel?

 

[Guest]

Thank you very much I have done it with your instructions! Is there a way I can now convert the 'form' in datasheet view into the 'table' section of my database?

Ian,

Though you are not specifically saying so, I understand this is on a form?
If yes:

For the first one, make a new text box and in its ConstrolSource property
(tab Data), instead of selecting a field, type in the following expression:
=Iif(Date() - [fieldA] > 365, "Update","")
If you opt for a checkbox instead, then the expression in the ocntrolsource
property should be:
=Iif(Date() - [fieldA] > 365, True,False)
In either case, If the name of the control on the form is not fieldA, change
to the exact name of the control. This is not too accurate in that it does
not take account of leap years. This can be taken care of if required, but
it would take a very complex expression, or (preferably) a VB function.

Another approach you might use, is to create a label instead, reading Update
or something, and show/hide it accordingly. To do that, you would need to
add the following code in the form's On Current event:
IIf Date() - Me.fieldA > 365 Then
Me.filedB.Visible = True
Else
Me.filedB.Visible = False
End If
Again, change the control names as appropriate if different.

For the second one, again a text box with its ControlSource property set to
the expression you have in your mail, data format to desired date format.

HTH,
Nikos

I need to make a field (fieldB)in Access show 'Yes' or 'No' according to a

date entry in another field(fieldA). I think that I should use the Iif
function in a control?

I basically want to say 'If the date in fieldA is over 1 year ago, fieldB

should display 'yes' or 'update' or whatever.'

I don't mind if field B is a toggle box or text.
I think that what I need to use is a control but I'm not sure how to use these.

Similarly, I want field C to display the date that is fieldA plus 3 months

.. How can I programme this like you would in excel?

DateSerial(Year("fieldA"),Month("FieldA")+3,Day("fieldA")) ???

Thank you!

 

[Nikos Yannacopoulos]

Ian,

What do you mean by that? Not clear. You want the table to behave like the
form (calculated values)? You want to store the calculated values in the
table?

In the former case, it's not possible. Tables are just data storage, no form
functionality.
In the latter case, don't store it at all. Information univocally determined
on the basis of existing data, thus easily calculated upon request at any
time, is redundant and should not be stored.
If you meant something else, please elaborate.

HTH,
Nikos

Thank you very much I have done it with your instructions! Is there a way

I can now convert the 'form' in datasheet view into the 'table' section of
my database?

Ian,

Though you are not specifically saying so, I understand this is on a form?
If yes:

For the first one, make a new text box and in its ConstrolSource property
(tab Data), instead of selecting a field, type in the following expression:
=Iif(Date() - [fieldA] > 365, "Update","")
If you opt for a checkbox instead, then the expression in the ocntrolsource
property should be:
=Iif(Date() - [fieldA] > 365, True,False)
In either case, If the name of the control on the form is not fieldA, change
to the exact name of the control. This is not too accurate in that it does
not take account of leap years. This can be taken care of if required, but
it would take a very complex expression, or (preferably) a VB function.

Another approach you might use, is to create a label instead, reading Update
or something, and show/hide it accordingly. To do that, you would need to
add the following code in the form's On Current event:
IIf Date() - Me.fieldA > 365 Then
Me.filedB.Visible = True
Else
Me.filedB.Visible = False
End If
Again, change the control names as appropriate if different.

For the second one, again a text box with its ControlSource property set to
the expression you have in your mail, data format to desired date format.

HTH,
Nikos

I need to make a field (fieldB)in Access show 'Yes' or 'No' according

to a
date entry in another field(fieldA). I think that I should use the Iif
function in a control?

I basically want to say 'If the date in fieldA is over 1 year ago,

fieldB
should display 'yes' or 'update' or whatever.'

I don't mind if field B is a toggle box or text.
I think that what I need to use is a control but I'm not sure how to

use
these.

Similarly, I want field C to display the date that is fieldA plus 3

months
.. How can I programme this like you would in excel?

DateSerial(Year("fieldA"),Month("FieldA")+3,Day("fieldA")) ???

Thank you!

 

[carlos margalef]

me descargue servidor de noticias

Ian,

What do you mean by that? Not clear. You want the table to behave like the
form (calculated values)? You want to store the calculated values in the
table?

In the former case, it's not possible. Tables are just data storage, no form
functionality.
In the latter case, don't store it at all. Information univocally determined
on the basis of existing data, thus easily calculated upon request at any
time, is redundant and should not be stored.
If you meant something else, please elaborate.

HTH,
Nikos

Thank you very much I have done it with your instructions! Is there a

way
I can now convert the 'form' in datasheet view into the 'table' section of
my database?

Ian,

Though you are not specifically saying so, I understand this is on a form?
If yes:

For the first one, make a new text box and in its ConstrolSource property
(tab Data), instead of selecting a field, type in the following expression:
=Iif(Date() - [fieldA] > 365, "Update","")
If you opt for a checkbox instead, then the expression in the ocntrolsource
property should be:
=Iif(Date() - [fieldA] > 365, True,False)
In either case, If the name of the control on the form is not fieldA, change
to the exact name of the control. This is not too accurate in that it does
not take account of leap years. This can be taken care of if required, but
it would take a very complex expression, or (preferably) a VB function.

Another approach you might use, is to create a label instead, reading Update
or something, and show/hide it accordingly. To do that, you would need to
add the following code in the form's On Current event:
IIf Date() - Me.fieldA > 365 Then
Me.filedB.Visible = True
Else
Me.filedB.Visible = False
End If
Again, change the control names as appropriate if different.

For the second one, again a text box with its ControlSource property set to
the expression you have in your mail, data format to desired date format.

HTH,
Nikos

I need to make a field (fieldB)in Access show 'Yes' or 'No'

according
to a