Calculating dates..

[Joel]

I need to have a textbox display a calculated date from another field in my
report.

ex: User types in the date [InspectionDate] on the form, then they select
the inspection frequency ([InspectionFrequency] values are 3 months, 6m,
1y,2y,3y,4y,5y, and 10y and each has a unique id).

Now in my report i need the [InspectionDate] to add the
[InspectionFrequency] and display in the report.

Thanks.

 

[Allen Browne]

Use DateAdd() to add the number of months or years.

Joel, you probably have a table for the InspectionFrequency dates.
How about adding 2 more fields to the table:
PeriodType
Freqency
The PeriodType will contain the text DateAdd needs, e.g. "m", "yyyy", "d",
"q".
The Frequency is the number of periods.
Example data:
3 months m 3
6 monhts m 6
1 year yyyy 1
5 years yyyy 5

Now you can make a query that contains both tables, and create a calculated
field by typing this into a fresh column in the Field row:
DateAdd([PeriodType], [Frequency], [InspectionDate])

 

[Joel]

Thank you for your responce.
But Im still having trouble with this. I got the query to work just fine but
I cant get the calculated date to show up in a text box on the form or a
report, all I get is #Name?.
I have the control of that textbox [Text147] set to
=InspectionFrequencyQuery!Expr1
Also I have the combobox [InspectionFrequency] set to do a
requery(Me!Text147.ReQuery) after update.

Thanks

Use DateAdd() to add the number of months or years.

Joel, you probably have a table for the InspectionFrequency dates.
How about adding 2 more fields to the table:
PeriodType
Freqency
The PeriodType will contain the text DateAdd needs, e.g. "m", "yyyy", "d",
"q".
The Frequency is the number of periods.
Example data:
3 months m 3
6 monhts m 6
1 year yyyy 1
5 years yyyy 5

Now you can make a query that contains both tables, and create a calculated
field by typing this into a fresh column in the Field row:
DateAdd([PeriodType], [Frequency], [InspectionDate])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I need to have a textbox display a calculated date from another field in my
report.

ex: User types in the date [InspectionDate] on the form, then they select
the inspection frequency ([InspectionFrequency] values are 3 months, 6m,
1y,2y,3y,4y,5y, and 10y and each has a unique id).

Now in my report i need the [InspectionDate] to add the
[InspectionFrequency] and display in the report.

Thanks.

 

[Joel]

This is the SQL statement for ny query.

SELECT
DateAdd([InspectionFrequency.PeriodType],[InspectionFrequency.Frequency],[InspectionDate]) AS Expr1
FROM InspectionFrequency INNER JOIN MainUnitData ON
InspectionFrequency.InspectionFrequency = MainUnitData.InspectionFrequencyName
WHERE
(((MainUnitData.InspectionFrequencyName)=[Forms]![UnitDataInput]![combo120)]));

Use DateAdd() to add the number of months or years.

Joel, you probably have a table for the InspectionFrequency dates.
How about adding 2 more fields to the table:
PeriodType
Freqency
The PeriodType will contain the text DateAdd needs, e.g. "m", "yyyy", "d",
"q".
The Frequency is the number of periods.
Example data:
3 months m 3
6 monhts m 6
1 year yyyy 1
5 years yyyy 5

Now you can make a query that contains both tables, and create a calculated
field by typing this into a fresh column in the Field row:
DateAdd([PeriodType], [Frequency], [InspectionDate])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I need to have a textbox display a calculated date from another field in my
report.

ex: User types in the date [InspectionDate] on the form, then they select
the inspection frequency ([InspectionFrequency] values are 3 months, 6m,
1y,2y,3y,4y,5y, and 10y and each has a unique id).

Now in my report i need the [InspectionDate] to add the
[InspectionFrequency] and display in the report.

Thanks.

 

[Allen Browne]

You need to put that into the query that feeds the form. You can then just
set the Control Source of the text box to the name of the calculated field
(Expr1 in your example.)

It is possible to DLookup() the result from a query, but that seems
pointless when you could include the calculated field in the form's
RecordSource.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for your responce.
But Im still having trouble with this. I got the query to work just fine
but
I cant get the calculated date to show up in a text box on the form or a
report, all I get is #Name?.
I have the control of that textbox [Text147] set to
=InspectionFrequencyQuery!Expr1
Also I have the combobox [InspectionFrequency] set to do a
requery(Me!Text147.ReQuery) after update.

Thanks

Use DateAdd() to add the number of months or years.

Joel, you probably have a table for the InspectionFrequency dates.
How about adding 2 more fields to the table:
PeriodType
Freqency
The PeriodType will contain the text DateAdd needs, e.g. "m", "yyyy",
"d",
"q".
The Frequency is the number of periods.
Example data:
3 months m 3
6 monhts m 6
1 year yyyy 1
5 years yyyy 5

Now you can make a query that contains both tables, and create a
calculated
field by typing this into a fresh column in the Field row:
DateAdd([PeriodType], [Frequency], [InspectionDate])

I need to have a textbox display a calculated date from another field in
my
report.

ex: User types in the date [InspectionDate] on the form, then they
select
the inspection frequency ([InspectionFrequency] values are 3 months,
6m,
1y,2y,3y,4y,5y, and 10y and each has a unique id).

Now in my report i need the [InspectionDate] to add the
[InspectionFrequency] and display in the report.

Thanks.

 

[Joel]

I cant change the record source of the form, I need it set to the table it
set to.

You need to put that into the query that feeds the form. You can then just
set the Control Source of the text box to the name of the calculated field
(Expr1 in your example.)

It is possible to DLookup() the result from a query, but that seems
pointless when you could include the calculated field in the form's
RecordSource.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for your responce.
But Im still having trouble with this. I got the query to work just fine
but
I cant get the calculated date to show up in a text box on the form or a
report, all I get is #Name?.
I have the control of that textbox [Text147] set to
=InspectionFrequencyQuery!Expr1
Also I have the combobox [InspectionFrequency] set to do a
requery(Me!Text147.ReQuery) after update.

Thanks

Use DateAdd() to add the number of months or years.

Joel, you probably have a table for the InspectionFrequency dates.
How about adding 2 more fields to the table:
PeriodType
Freqency
The PeriodType will contain the text DateAdd needs, e.g. "m", "yyyy",
"d",
"q".
The Frequency is the number of periods.
Example data:
3 months m 3
6 monhts m 6
1 year yyyy 1
5 years yyyy 5

Now you can make a query that contains both tables, and create a
calculated
field by typing this into a fresh column in the Field row:
DateAdd([PeriodType], [Frequency], [InspectionDate])

I need to have a textbox display a calculated date from another field in
my
report.

ex: User types in the date [InspectionDate] on the form, then they
select
the inspection frequency ([InspectionFrequency] values are 3 months,
6m,
1y,2y,3y,4y,5y, and 10y and each has a unique id).

Now in my report i need the [InspectionDate] to add the
[InspectionFrequency] and display in the report.

Thanks.

 

[Allen Browne]

You cannot make a new query based on that table, and put the calculated
field in that query so you can refer to it in your form as well as all the
other fields you already have?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I cant change the record source of the form, I need it set to the table it
set to.

You need to put that into the query that feeds the form. You can then
just
set the Control Source of the text box to the name of the calculated
field
(Expr1 in your example.)

It is possible to DLookup() the result from a query, but that seems
pointless when you could include the calculated field in the form's
RecordSource.

Thank you for your responce.
But Im still having trouble with this. I got the query to work just
fine
but
I cant get the calculated date to show up in a text box on the form or
a
report, all I get is #Name?.
I have the control of that textbox [Text147] set to
=InspectionFrequencyQuery!Expr1
Also I have the combobox [InspectionFrequency] set to do a
requery(Me!Text147.ReQuery) after update.

Thanks

:

Use DateAdd() to add the number of months or years.

Joel, you probably have a table for the InspectionFrequency dates.
How about adding 2 more fields to the table:
PeriodType
Freqency
The PeriodType will contain the text DateAdd needs, e.g. "m", "yyyy",
"d",
"q".
The Frequency is the number of periods.
Example data:
3 months m 3
6 monhts m 6
1 year yyyy 1
5 years yyyy 5

Now you can make a query that contains both tables, and create a
calculated
field by typing this into a fresh column in the Field row:
DateAdd([PeriodType], [Frequency], [InspectionDate])

I need to have a textbox display a calculated date from another field
in
my
report.

ex: User types in the date [InspectionDate] on the form, then they
select
the inspection frequency ([InspectionFrequency] values are 3 months,
6m,
1y,2y,3y,4y,5y, and 10y and each has a unique id).

Now in my report i need the [InspectionDate] to add the
[InspectionFrequency] and display in the report.

Thanks.

 

[Joel]

I tried making another form with the query as the record source and I set a
textbox to Expr1 and I just get a blank box. (I have the other form open so
the query can read the combobox that feeds the criteria)

You cannot make a new query based on that table, and put the calculated
field in that query so you can refer to it in your form as well as all the
other fields you already have?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I cant change the record source of the form, I need it set to the table it
set to.

You need to put that into the query that feeds the form. You can then
just
set the Control Source of the text box to the name of the calculated
field
(Expr1 in your example.)

It is possible to DLookup() the result from a query, but that seems
pointless when you could include the calculated field in the form's
RecordSource.

Thank you for your responce.
But Im still having trouble with this. I got the query to work just
fine
but
I cant get the calculated date to show up in a text box on the form or
a
report, all I get is #Name?.
I have the control of that textbox [Text147] set to
=InspectionFrequencyQuery!Expr1
Also I have the combobox [InspectionFrequency] set to do a
requery(Me!Text147.ReQuery) after update.

Thanks

:

Use DateAdd() to add the number of months or years.

Joel, you probably have a table for the InspectionFrequency dates.
How about adding 2 more fields to the table:
PeriodType
Freqency
The PeriodType will contain the text DateAdd needs, e.g. "m", "yyyy",
"d",
"q".
The Frequency is the number of periods.
Example data:
3 months m 3
6 monhts m 6
1 year yyyy 1
5 years yyyy 5

Now you can make a query that contains both tables, and create a
calculated
field by typing this into a fresh column in the Field row:
DateAdd([PeriodType], [Frequency], [InspectionDate])

I need to have a textbox display a calculated date from another field
in
my
report.

ex: User types in the date [InspectionDate] on the form, then they
select
the inspection frequency ([InspectionFrequency] values are 3 months,
6m,
1y,2y,3y,4y,5y, and 10y and each has a unique id).

Now in my report i need the [InspectionDate] to add the
[InspectionFrequency] and display in the report.

Thanks.

 

[Allen Browne]

Joel, it it works in the query, it should show in a form that is based on
that query.

 

[Joel]

I figured out what was wrong. I had ")" in the criteria in the wrong place =/
I fixed it and it works great!

Thank You so much for helping me figure this out.
This was my first time dealing with this situation.

 

[Allen Browne]

I fixed it and it works great!

Excellent!