Date field + number field = New Date

[Carl_and_Earl]

How do I make this possible?
I want by pressing a button, to add Data + Number fields to Data2.
Thanks.

http://img55.imageshack.us/my.php?image=testqa2.jpg

 

[Jeff Boyce]

Use DateAdd() function -- see Access HELP

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Carl_and_Earl]

DateAdd("m", 1, "31-Jan-95")

Ok, I understand that example, but how can I replace "1" in the formula with
the number I input in the text box?

 

[John W. Vinson]

DateAdd("m", 1, "31-Jan-95")

Ok, I understand that example, but how can I replace "1" in the formula with
the number I input in the text box?

=DateAdd("m", [numbercontrol], [datecontrol])

Replace numbercontrol with the name of the textbox containing the number,
datecontrol with the name of the textbox containing the date, and use the
expression as the control source of a textbox on the form.

You will NOT want to store this derived value in any table, since it can and
should be calculated on the fly from the other two fields.

 

[Carl_and_Earl]

http://img361.imageshack.us/my.php?image=sampleid3.jpg
Please tell me what do I do wrong?
I am expecting that when I enter the "Expirare" field, the date to be set
like "Data" + the number I put in [Zile]

Thanks.

 

[John W. Vinson]

http://img361.imageshack.us/my.php?image=sampleid3.jpg
Please tell me what do I do wrong?
I am expecting that when I enter the "Expirare" field, the date to be set
like "Data" + the number I put in [Zile]

Thanks.

=DateAdd("m", [numbercontrol], [datecontrol])

Replace numbercontrol with the name of the textbox containing the number,
datecontrol with the name of the textbox containing the date, and use the
expression as the control source of a textbox on the form.

You will NOT want to store this derived value in any table, since it can and
should be calculated on the fly from the other two fields.

If you're doing the operation in VBA code (hint: you didn't ever say anything
about the context!) you need to reference the form control using the Me!
syntax. Me! is a shortcut for [Forms]![ThisCurrentForm]! so you can reference
other controls on the form.

I would suggest changing the line to

Me![Expirare] = DateAdd("m", Me![Zile], Me![Data])

You might need to use the NZ() function to replace null values in Zile and
Data with some reasonable default, or check for NULL before executing this
line, since you'll get an error message if either control is NULL.

 

[Carl_and_Earl]

To be frank, I thought that is the only way to program my form. I mean VBA.
Is there any other way?
The sintax you told me to input earlyer, didn't work either. Nothing changes
on focus.
I'm out of options.

 

[Ken Sheridan]

Whether you need to use code or not depends on three things:

1. The Zile being bound to a field in the form's underlying table.
2. The Expirare control also being bound to a field in the form's
underlying table.
3. If both of the above are true then on your needing to be able to
manually change the value of the Expirare control after its been calculated
on the basis of the other two values.

If 1 and 2 are True but 3 isn't, i.e. the value of the Expirare control for
each record will always be the value of data + the number of months in the
Zile control then:

You don't need, and should not have, an Expirare field in the underlying
table, but compute the value in an unbound control, or a computed column in a
query, with no VBA code needed at all. In your form for instance the
ControlSource property of an unbound control would be:

=DateAdd("m", Nz([Zile],0), [Data])

The Nz function is necessary to, return a zero if Zile is Null, otherwise
the expression would raise an error.

If on the other hand Zile is an unbound control, with no corresponding Zile
field in the underlying table then you do need to 'push' the value into the
Expirare control with code. I'd suggest the AfterUpdate event procedure of
the Zile control rather than a separate button, with code like this:

Me.[Expirare] = DateAdd("m", Nz(Me.[Zile],0), Me!.Data])

If you are unfamiliar with entering code in event procedures this is how
its done - forgive me if I'm teaching my grandmother to suck eggs:

Select the control in form design view and open its properties sheet if its
not already open. Then select the AfterUpdate event property in the
properties sheet. Click on the 'build' button; that's the one on the right
with 3 dots. Select 'Code Builder' in the dialogue, and click OK. The VBA
window will open at the event procedure with the first and last lines already
in place. Enter the above line of code between these two existing lines.

Ken Sheridan
Stafford, England

 

[MikeJohnB]

I cant help thinking you are putting the code in the wrong control. Perhaps
you should try placing the code in the on enter or on lost focus event of the
data control?

Private Sub Data_Enter()
Me![Expirare] = DateAdd("m", Me![Zile], Me![Data])
End Sub

This will populate Expirare with the date generated by the two completed
controls?

I don't think you have explained to Jeff and John the full requirement
otherwise they would have provided the better solution, I'm only a amature.

regards

 

[Carl_and_Earl]

Thank you so much Ken Sheridan for such a complex answer. I was about to
write down that I give up, because I tried and tried so many ways I didn't
know what else I could do.

The "Zile" and "Data" fields are bound and "Expirare" is unbound.
The expression "=DateAdd("m", Nz([Zile],0), [Data])" you gave me I have
inputed on "Expirare" Property Sheet on Data tab, as default value not in
Control Source as later saw you wrote me. Finally the code works. Thank you
so much.

Thanks also MikeJohnB for telling me "I cant help thinking you are putting
the code in the wrong control." That made me think I am putting the text in
the wrong field.
Now I have another problem:
The input mask for data field is 99.99.0000;0; and gives me an error when I
try to input 31.12.2008. I want the day to be the first displayed, not the
month.

 

[Carl_and_Earl]

How can I transform this code "=DateAdd("m", Nz([Zile],0), [Data])" to VBA,
because I am realising that later on this project I will want to get the data
from "Expirare" in a list box and I think that will be possible only if it's
bound to a table. Am I right?

The list box would be sorted descending like this:

Name Date
John 31.12.2008
Dimitry 10.10.2008

And being unbound I couldn't get the data at one time, am I right?

 

[Carl_and_Earl]

How to translate the code "=DateAdd("m", Nz([Zile],0), [Data])" to VBA,
because I need later on the project to take the data from the "Expirare"
field and insert it in a list box and sort it. I beleive that leaving
"Expirare" unbound, I coudn't acomplish this.

 

[Ken Sheridan]

No, provided you have the Data and Zile fields in the table then you don't
need the Expirare field also. You can compute it from the first two, so the
RowSource property for a list box to list all the Expirare dates in
descending order would be like this:

SELECT [Name], DateAdd("m", Nz([Zile],0), [Data])
FROM YourTable
ORDER BY DateAdd("m", Nz([Zile],0), [Data]) DESC;

If data can be computed from other data you can get it anywhere you wish, so
there's no need to store it, and moreover, its risky if you do.

Ken Sheridan
Stafford, England

How can I transform this code "=DateAdd("m", Nz([Zile],0), [Data])" to VBA,
because I am realising that later on this project I will want to get the data
from "Expirare" in a list box and I think that will be possible only if it's
bound to a table. Am I right?

The list box would be sorted descending like this:

Name Date
John 31.12.2008
Dimitry 10.10.2008

And being unbound I couldn't get the data at one time, am I right?

Thank you so much Ken Sheridan for such a complex answer. I was about to
write down that I give up, because I tried and tried so many ways I didn't
know what else I could do.

The "Zile" and "Data" fields are bound and "Expirare" is unbound.
The expression "=DateAdd("m", Nz([Zile],0), [Data])" you gave me I have
inputed on "Expirare" Property Sheet on Data tab, as default value not in
Control Source as later saw you wrote me. Finally the code works. Thank you
so much.

Thanks also MikeJohnB for telling me "I cant help thinking you are putting
the code in the wrong control." That made me think I am putting the text in
the wrong field.
Now I have another problem:
The input mask for data field is 99.99.0000;0; and gives me an error when I
try to input 31.12.2008. I want the day to be the first displayed, not the
month.