HELP! Query will not display calculation?

[Liam.M]

Hey guys,

I have in my database a "Calendar2" where the user selects date
x...this date is then automatically displayed into a Field title "Date
of Issue", I have then set-up another text field titled "Due Date",
which, based on a calculation, works out the due date for the user,
this code, is as follows:

=DateAdd("yyyy", 1, [DateOfIssue])

(this code for the calculation is placed under the fields "control
source")

MY ISSUE IS THIS.....I have a Query set up, which is an EXTREMELY
important aspect of my project/ database...and I have both fields in
there (Date of Issue and also Due Date), now for some reason the query
will not display the "Due Date", it is just blank...I am assuming for
some reason this is because this field is based on a Calculation and
therefore for some reason doesnt think there is any value within the
field...yet...it displays Date of Issue...and this value is passed from
another application "Calandar2", so I really cant see how code or a
calculation could be interfering with this process....HELP...I am
completely lost, and this needs to be fixed???????? it is extremely
important that this "Due Date" value can be returned via a Query!!!!!

 

[Liam.M]

well I kind of just work it out....I'm sounding really stupid! I
realise that this code is placed in the fields control source....and
obviously my query's control source is set to a table! So therefore its
returning a null value! How can I get this field to perform the same
calculation/ function..working out the Due Date....with this fields
control source property still set to the Table...which the query is
based on?

 

[John Vinson]

well I kind of just work it out....I'm sounding really stupid! I
realise that this code is placed in the fields control source....and
obviously my query's control source is set to a table! So therefore its
returning a null value! How can I get this field to perform the same
calculation/ function..working out the Due Date....with this fields
control source property still set to the Table...which the query is
based on?

A Query does not have "a control source".

Don't confuse a Recordsource, which applies to a Form or Report, with
a Control Source, which applies to a single control on a Form or
Report. That control source may be an expression, or the name of a
table field - but it CANNOT be "a table" or "a query".

If you have a query that's not working, please open it in SQL view and
post the SQL text here. If you have a form or report control, please
post its relevant properties.

John W. Vinson[MVP]

 

[Liam.M]

Hey John, thanxs for the reply....no...the code:
=DateAdd("yyyy",1,[Date of Issue])
Is the control source for the "DueDate" Field in my form....the issue
was that on another one of my forms....which I want to use as my main
form...its a Summary List, representating ALL of the records in my
database...this "SummaryList" form has its Record Source based on a
Query...however....it will NOT return data for the "DueDate" Field for
some reason...therefore meaning I cannot on my main form (the
SUmmaryList) see what the DueDate is...it remains blank...any
suggestions as to why this is the case?
Here is the SQL View for the Query:

SELECT ShipsInformation.[SBMA Number], ShipsInformation.[Vessel Name],
ShipsInformation.[IMO Number], ShipsInformation.[Date of Issue],
ShipsInformation.RecordID, ShipsInformation.[Date of Attendance],
ShipsInformation.[Due Date]
FROM ShipsInformation;

Kind Regards,

Liam

 

[John Vinson]

Hey John, thanxs for the reply....no...the code:
=DateAdd("yyyy",1,[Date of Issue])
Is the control source for the "DueDate" Field in my form....the issue
was that on another one of my forms....which I want to use as my main
form...its a Summary List, representating ALL of the records in my
database...this "SummaryList" form has its Record Source based on a
Query...however....it will NOT return data for the "DueDate" Field for
some reason...therefore meaning I cannot on my main form (the
SUmmaryList) see what the DueDate is...it remains blank...any
suggestions as to why this is the case?
Here is the SQL View for the Query:

SELECT ShipsInformation.[SBMA Number], ShipsInformation.[Vessel Name],
ShipsInformation.[IMO Number], ShipsInformation.[Date of Issue],
ShipsInformation.RecordID, ShipsInformation.[Date of Attendance],
ShipsInformation.[Due Date]
FROM ShipsInformation;

You're apparently assuming that putting a textbox named Due Date on a
Form does something to the data in the table. It doesn't. That textbox
is JUST a display, on the form; it's not available anywhere else.

Simply redo the calculation directly in your Query:

SELECT ShipsInformation.[SBMA Number], ShipsInformation.[Vessel Name],
ShipsInformation.[IMO Number], ShipsInformation.[Date of Issue],
ShipsInformation.RecordID, ShipsInformation.[Date of Attendance],
DateAdd("yyyy", 1, [ShipsInformation].[Date of Issue]) AS [Due Date]
FROM ShipsInformation;

This query can be used for a Form (your original form or a different
form), for a Report, as the basis for another query, whatever you
like.

John W. Vinson[MVP]

 

[Liam.M]

Mr. John Vinson,

Thankyou so much for your help it is very much appreciated....I have
implemented this solutions and it is working perfectly....thankyou so
much, your help is very much appreciated!

I have another question for you Mr. Vinson, however, I am unsure if you
will be able to assist me, you may be able to steer me in the correct
direction though!

Based on the Query you have helped me display in my Main form
(SummaryList), I want to be able to Query the whole database based on
this "DueDate" ....any record that falls within 2 months of reaching
this "DueDate" ...I would like to execute a email reminder to myslef (I
have already managed to write this module on how to send an email) BUT
i still need to know how to query the database, or "loop" through it,
and find any records that fall within the above criteria...and then,
from this, execute the module I have written?
Any help would be much appreciated!

Kind Regards,
Liam