Full Version: Datediff
UtterAccess Forums > Microsoft® Access > Access Forms
MikeD
I have not been a user of Access, I tend to prefer FileMaker Pro and I have come to realize why. I have created a database and a form and I want to do a simply calculation between fields. My fields are SubmissionDate, and EDReview. Both are in short date format. I want the difference between the two dates to also appear on the form. How do I formate the control statement. I have tried several different iterations and get various effor messages. Thanks for any help.
Jack Cowley
Welcome to Utter Access!
On the Control Source of an unbound control on your form:
=DateDiff("d", [SubmissionDate], [EDRReview])
The above code will give the the number of days difference between the two dates.
hth,
Jack
MikeD
I put that in and now I get the error message #Name?
Jack Cowley
Be sure that "SubmissionDate" and "EDRReview" are the actual names of the controls on your form. Also, check Access help (use the help menu that you will find in the code editor (where you see the Debug menu)) for the actual syntax of the function. I suggested the code from memory so I could be wrong...
ack
MikeD
Checked both and they are the same. This is extremely cryptic to perform a simple database function. Not even Dbase is this bad. Still get the same error no matter what permutation I attempt. It will be easiest to caclulate in Excel I think.
ikeD
R. Hicks
Post the "exact" line you are using that is returning the error ...
And "exactly" where you are using it ...
DH
MikeD
=DateDiff("d", [SubmissionDate], [EDReview])
I am using in an unbound box in a form and putting it in the Control Source.
R. Hicks
Are you wanting to reference fields in the form's recordset named .. "SubmissionDate" and "EDReview" ???
FOr are you wanting to reference controls on the form named .. "SubmissionDate" and "EDReview" ???
Do you have the controls on the form named the same as the field names in the recordset they are bound to ???

RDH
MikeD
I have two fields on the form with the control names SubmissionDate and EdReview. All I want is the new field to reference those two fields and calculate the difference int he dates. The fields are the same names between the record set and the form.
R. Hicks
First ...
There are "no fields on a form" ...
"Fields" are in Tables and Queries ...
"Controls" .. are on Forms and Reports .. and they can be bound to "Fields" ...

You need to name the controls on the form different names than the field names ...
THere is an example ...
Name the control on the form that is bound to the field "SubmissionDate" .. assign it the name "txtSubmissionDate" ...

Then you can reference the Field value as ... [SubmissionDate]
FOr .. reference the control value on the form as ... Me.txtSubmissionDate
MikeD
If I remane the control on the form I get an error message that says "This control has an invalid control source" This is my first time using Access period. So sorry for not understanding quite what you mean. So you are saying to change the Control Source on the Form field to "txtSubmissionDate"? That is what I did when I got the above error message.
R. Hicks
See the attached file above ...
DH
MikeD
I have done this and have taken screen captures of all the fields involved. I still get the same result on the form.
MikeD
Here is the SubmittedDate properties
MikeD
Here is the Edreview properties
MikeD
Here is the calculation box properties
MikeD
And the result on the form field.
R. Hicks
I haven't a clue as to what the problem is ...
Can the database be compacted the zipped up and attached to a reply (500k limit) ???
RDH
MikeD
It is just the shell and I have a dummy entry in it just to try the calculation so It should come through. I really appreciate your help.
R. Hicks
The following lines are in the form's module and need to be removed ...
CODE
[color="red"]Accepted, Accepted Minor Rev, Accepted Major Rev, Rejected Out of Scope, Rejected Major Flaws[/color]


[color="darkblue"]End Sub[/color]

I have attached a fixed copy ...
DH
R. Hicks
I just took a look at you table structure ... "not good" ..

You have what is known as repeating groups" in your table ...
The .. Review 1, Review 2, and Review 3 .. information fields.
You need to Normalize this structure.
Each Review information .. should a seperate record in another table linked to the main table.

RDH
MikeD
I appreciate all your help. I am goign to try formatting the database in FileMaker Pro and try to open it as an access database. If that does not work our web technician is just going to have to suffice with Filemaker. Thanks again.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.