Updating a date??

[Nick T]

Hi,

I have a form, which some text boxes get their information from a table.
Other text boxes have information inputted into them using buttons on the
form.
When a new record is added, the form automatically inserts a date & time
into 'textbox A'.

'Textbox B' has the number '5' in it as this text box looks information up
in a table.

Now, i want 'Textbox C' to add '5' to the month detaied in 'TextBox A'

Eg, if TextBox A says '01.05.08 hh:mm:ss' TextBox C will detail '01.10.08
hh:mm:ss'

In the control source of TextBox C, i have tried = [TextBoxA]+[TextBoxB],
however this doesnt return the desired value for obvious reasons.

In addition to this, i want the end value of TextBox C to be recorded in a
specified table, which again, i cant do if i put the = [TextBoxA]+[TextBoxB]
into the control source.

Maybe to help explain, i want to do this in order to automatically display &
record the best before date of a product - which in this example is 5 months
after the date of the record being added.

Any suggestions??
Thanks

 

[Klatuu]

You probably don't want to store the resulting date in a table. if you store
the number of months and the original date in text box B, then doing so
violates database normalization rules. The correct way to do it is the just
use TextBoxC to show the results of the calculation:
=IIf(IsNull([txtBoxA]) Or
IsNull([txtBoxB]),Null,DateAdd("m",[txtBoxA],[txtBoxB]))

Private Declare Function GetUserNameA Lib "Advapi32" (ByVal strN As String,
ByRef intN As Long) As Long
Public Function GetUserID()

Dim Buffer As String * 20
Dim Length As Long
Dim lngresult As Long, userid As String

Length = 20

 

[Wayne-I-M]

Text box c Source =DateAdd("m", 5, [TextboxB)

BUT don't store this information in the table - just reproduce it (on a form
or in a query) as and when you need it

 

[Wayne-I-M]

Private Declare Function GetUserNameA Lib "Advapi32" (ByVal strN As String,
ByRef intN As Long) As Long
Public Function GetUserID()

Dim Buffer As String * 20
Dim Length As Long
Dim lngresult As Long, userid As String

Length = 20


????????????

Must be all the sun you get in texas (its raining here in Manchester - for a
change)
--
Wayne
Manchester, England.

You probably don't want to store the resulting date in a table. if you store
the number of months and the original date in text box B, then doing so
violates database normalization rules. The correct way to do it is the just
use TextBoxC to show the results of the calculation:
=IIf(IsNull([txtBoxA]) Or
IsNull([txtBoxB]),Null,DateAdd("m",[txtBoxA],[txtBoxB]))

Private Declare Function GetUserNameA Lib "Advapi32" (ByVal strN As String,
ByRef intN As Long) As Long
Public Function GetUserID()

Dim Buffer As String * 20
Dim Length As Long
Dim lngresult As Long, userid As String

Length = 20


--
Dave Hargis, Microsoft Access MVP

Hi,

I have a form, which some text boxes get their information from a table.
Other text boxes have information inputted into them using buttons on the
form.
When a new record is added, the form automatically inserts a date & time
into 'textbox A'.

'Textbox B' has the number '5' in it as this text box looks information up
in a table.

Now, i want 'Textbox C' to add '5' to the month detaied in 'TextBox A'

Eg, if TextBox A says '01.05.08 hh:mm:ss' TextBox C will detail '01.10.08
hh:mm:ss'

In the control source of TextBox C, i have tried = [TextBoxA]+[TextBoxB],
however this doesnt return the desired value for obvious reasons.

In addition to this, i want the end value of TextBox C to be recorded in a
specified table, which again, i cant do if i put the = [TextBoxA]+[TextBoxB]
into the control source.

Maybe to help explain, i want to do this in order to automatically display &
record the best before date of a product - which in this example is 5 months
after the date of the record being added.

Any suggestions??
Thanks

 

[Nick T]

Hi Both,

Thanks for the help! Really appreciate it. Its all comming together now.
However, lastly, i have it working how i want, however perhaps you could
enlighten me as to why i shouldnt store this info in a table?

Thanks again!

Text box c Source =DateAdd("m", 5, [TextboxB)

BUT don't store this information in the table - just reproduce it (on a form
or in a query) as and when you need it

--
Wayne
Manchester, England.

Hi,

I have a form, which some text boxes get their information from a table.
Other text boxes have information inputted into them using buttons on the
form.
When a new record is added, the form automatically inserts a date & time
into 'textbox A'.

'Textbox B' has the number '5' in it as this text box looks information up
in a table.

Now, i want 'Textbox C' to add '5' to the month detaied in 'TextBox A'

Eg, if TextBox A says '01.05.08 hh:mm:ss' TextBox C will detail '01.10.08
hh:mm:ss'

In the control source of TextBox C, i have tried = [TextBoxA]+[TextBoxB],
however this doesnt return the desired value for obvious reasons.

In addition to this, i want the end value of TextBox C to be recorded in a
specified table, which again, i cant do if i put the = [TextBoxA]+[TextBoxB]
into the control source.

Maybe to help explain, i want to do this in order to automatically display &
record the best before date of a product - which in this example is 5 months
after the date of the record being added.

Any suggestions??
Thanks

 

[Beetle]

As resident MVP John Vinson says -

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

--
_________

Sean Bailey

Hi Both,

Thanks for the help! Really appreciate it. Its all comming together now.
However, lastly, i have it working how i want, however perhaps you could
enlighten me as to why i shouldnt store this info in a table?

Thanks again!

Text box c Source =DateAdd("m", 5, [TextboxB)

BUT don't store this information in the table - just reproduce it (on a form
or in a query) as and when you need it

--
Wayne
Manchester, England.

Hi,

I have a form, which some text boxes get their information from a table.
Other text boxes have information inputted into them using buttons on the
form.
When a new record is added, the form automatically inserts a date & time
into 'textbox A'.

'Textbox B' has the number '5' in it as this text box looks information up
in a table.

Now, i want 'Textbox C' to add '5' to the month detaied in 'TextBox A'

Eg, if TextBox A says '01.05.08 hh:mm:ss' TextBox C will detail '01.10.08
hh:mm:ss'

In the control source of TextBox C, i have tried = [TextBoxA]+[TextBoxB],
however this doesnt return the desired value for obvious reasons.

In addition to this, i want the end value of TextBox C to be recorded in a
specified table, which again, i cant do if i put the = [TextBoxA]+[TextBoxB]
into the control source.

Maybe to help explain, i want to do this in order to automatically display &
record the best before date of a product - which in this example is 5 months
after the date of the record being added.

Any suggestions??
Thanks

 

[Wayne-I-M]

Nick

It depends on how the data is being input into your form - it is "always" 5.
Is there "always" a date. If not you should use Klatuu's answer which
allows for these things. My answer was simply a stright answer to your
question but Klatuu gave you a fuller and better solution to the question.

This is his answer

=IIf(IsNull([txtBoxA]) Or
IsNull([txtBoxB]),Null,DateAdd("m",[txtBoxA],[txtBoxB]))


Use this as the source for box c if either box A or B will ever have nothing
in them as my answer will not work in these situation