Date Diff

[Michelle]

Hello,

I would like to calculate the date diffrence of admission date - start date
but I can't seem to get it to work. Here is the expression I am using.

=DateDiff("y",[Start Date],[Patient Information]![Admin Date])

I put it in the defalut value under the properties of the variable I am
trying to calculate in forms, is that where it belongs?

I have been playing around with it all day and I can't figure out what i am
doing wrong. Thanks for the help!

 

[ruralguy via AccessMonster.com]

Start by putting your function in the ControlSource of a Test TextBox so you
can see what is happening. What is the RecordSource of your form? What is
[Patient Information]?

Hello,

I would like to calculate the date diffrence of admission date - start date
but I can't seem to get it to work. Here is the expression I am using.

=DateDiff("y",[Start Date],[Patient Information]![Admin Date])

I put it in the defalut value under the properties of the variable I am
trying to calculate in forms, is that where it belongs?

I have been playing around with it all day and I can't figure out what i am
doing wrong. Thanks for the help!

 

[Michelle]

No that didnt work. I get the error #Name? in the text box.
I have two database which are connected through the primary key of database
1 using a one to many relationship.
"start date" is in the subform and admin date is in the mainform (ieatient
information)
Any other ideas on how to solve my problem? thanks!

Start by putting your function in the ControlSource of a Test TextBox so you
can see what is happening. What is the RecordSource of your form? What is
[Patient Information]?

Hello,

I would like to calculate the date diffrence of admission date - start date
but I can't seem to get it to work. Here is the expression I am using.

=DateDiff("y",[Start Date],[Patient Information]![Admin Date])

I put it in the defalut value under the properties of the variable I am
trying to calculate in forms, is that where it belongs?

I have been playing around with it all day and I can't figure out what i am
doing wrong. Thanks for the help!

 

[ruralguy via AccessMonster.com]

Believe it or not Michelle, I didn't expect it to work, just show you what
was wrong. Once you get the syntax of the referencing correct you will see
the value you want and then you can move the code wherever you want.

So [Patient Information] is the name of your MainForm and [Admin Date] is a
control (TextBox??) on that form. Forms are displayed on other form by means
of a SubFormControl. This SubFormControl has a name of its own that defaults
to the name of the form it is displaying, but does not have to remain so. To
reference a control on a SubForm, the syntax is:
SubFormControl.FORM.ControlName (on the subform) or
FORMS.MainForm.SubFormControl.FORM.ControlName

If the name of your SubFormControl is StartDateSubForm then the ControlSource
of your testing TextBox would be:

=DateDiff("yyyy",Me.[Admin Date], StartDateSubForm.FORM.[Start Date])

Did you really want the "Day of the Year" ("y") or the differences in years
("yyyy")?

Remember this is "air code" and could have typos.

Here's a link you should probably read:
Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

No that didnt work. I get the error #Name? in the text box.
I have two database which are connected through the primary key of database
1 using a one to many relationship.
"start date" is in the subform and admin date is in the mainform (ieatient
information)
Any other ideas on how to solve my problem? thanks!

Start by putting your function in the ControlSource of a Test TextBox so you
can see what is happening. What is the RecordSource of your form? What is

[quoted text clipped - 12 lines]

 

[Michelle]

Well I never mind learning new things. Unfortentaly I still can't seem to get
it to work. here is the code I am trying to use now

=DateDiff("d",Me.[Admin Date],RestraintInformationSubForm.Form.[Start Date])

I actually want to know the number of days from admin to start date. so
would i use"d", right?
Also this is the code i use when the text box is in the subform right?
thanks for the help!

Believe it or not Michelle, I didn't expect it to work, just show you what
was wrong. Once you get the syntax of the referencing correct you will see
the value you want and then you can move the code wherever you want.

So [Patient Information] is the name of your MainForm and [Admin Date] is a
control (TextBox??) on that form. Forms are displayed on other form by means
of a SubFormControl. This SubFormControl has a name of its own that defaults
to the name of the form it is displaying, but does not have to remain so. To
reference a control on a SubForm, the syntax is:
SubFormControl.FORM.ControlName (on the subform) or
FORMS.MainForm.SubFormControl.FORM.ControlName

If the name of your SubFormControl is StartDateSubForm then the ControlSource
of your testing TextBox would be:

=DateDiff("yyyy",Me.[Admin Date], StartDateSubForm.FORM.[Start Date])

Did you really want the "Day of the Year" ("y") or the differences in years
("yyyy")?

Remember this is "air code" and could have typos.

Here's a link you should probably read:
Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

No that didnt work. I get the error #Name? in the text box.
I have two database which are connected through the primary key of database
1 using a one to many relationship.
"start date" is in the subform and admin date is in the mainform (ieatient
information)
Any other ideas on how to solve my problem? thanks!

Start by putting your function in the ControlSource of a Test TextBox so you
can see what is happening. What is the RecordSource of your form? What is

[quoted text clipped - 12 lines]

I have been playing around with it all day and I can't figure out what i am
doing wrong. Thanks for the help!

 

[ruralguy via AccessMonster.com]

I thought the TextBox was on the MainForm, sorry. When the TextBox is on the
SubForm the it will look like:

=DateDiff("d",Me.Parent.[Admin Date],Me.[Start Date])

Well I never mind learning new things. Unfortentaly I still can't seem to get
it to work. here is the code I am trying to use now

=DateDiff("d",Me.[Admin Date],RestraintInformationSubForm.Form.[Start Date])

I actually want to know the number of days from admin to start date. so
would i use"d", right?
Also this is the code i use when the text box is in the subform right?
thanks for the help!

Believe it or not Michelle, I didn't expect it to work, just show you what
was wrong. Once you get the syntax of the referencing correct you will see

[quoted text clipped - 34 lines]

 

[Michelle]

Hi again,

so it's still not working and I have no idea where i am going wrong. here is
the code i put into the control source of the text box. any ideas???
Thanks for sticking with me!

=DateDiff("d",Me.[Start Date],Me.Patient Information.[Admin Date])

I thought the TextBox was on the MainForm, sorry. When the TextBox is on the
SubForm the it will look like:

=DateDiff("d",Me.Parent.[Admin Date],Me.[Start Date])

Well I never mind learning new things. Unfortentaly I still can't seem to get
it to work. here is the code I am trying to use now

=DateDiff("d",Me.[Admin Date],RestraintInformationSubForm.Form.[Start Date])

I actually want to know the number of days from admin to start date. so
would i use"d", right?
Also this is the code i use when the text box is in the subform right?
thanks for the help!

Believe it or not Michelle, I didn't expect it to work, just show you what
was wrong. Once you get the syntax of the referencing correct you will see

[quoted text clipped - 34 lines]

I have been playing around with it all day and I can't figure out what i am
doing wrong. Thanks for the help!

 

[ruralguy via AccessMonster.com]

=DateDiff("d",Me.[Start Date],Me.Parent.[Admin Date])

Hi again,

so it's still not working and I have no idea where i am going wrong. here is
the code i put into the control source of the text box. any ideas???
Thanks for sticking with me!
JOE ARPAIO >

I thought the TextBox was on the MainForm, sorry. When the TextBox is on the
SubForm the it will look like:

[quoted text clipped - 15 lines]

 

[scott04]

How about using this code:
Public Function workingdays2(StartDate As Date, endDate As Date) As Integer
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM [Service Request]",
dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= endDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

workingdays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select
End Function

Then use a text box that has properties =WorkingDays2([StartDate],[EndDate])
.. You can also add holiday date to your table to exclude these dates in your
calculation

=DateDiff("d",Me.[Start Date],Me.Parent.[Admin Date])

Hi again,

so it's still not working and I have no idea where i am going wrong. here is
the code i put into the control source of the text box. any ideas???
Thanks for sticking with me!
JOE ARPAIO >

I thought the TextBox was on the MainForm, sorry. When the TextBox is on the
SubForm the it will look like:

[quoted text clipped - 15 lines]

I have been playing around with it all day and I can't figure out what i am
doing wrong. Thanks for the help!