date diff expression in query

[Radhika]

I am trying to calculate the difference between two dates 'End Date' and
'Date of Visit' using a query. This is the expression I used:
TimeFrame:[Date of Visit] - [End Date].
However, 'End Date' is present in both tables I have used to create this
query 'tbl_NewInfo' and 'tbl_Foloowup'. I want the 'End Date' from
'tbl_NewInfo'.
How do i specify this in the expression and thereby prevent the error msg I
am getting?

Thankyou

 

[Duane Hookom]

You must fully qualify the field name like:
[Table Name].[Field Name]

I use a naming convention that results in every field in my MDBs having a
unique name. This shields me from this type of issue.

 

[Ken Sheridan]

Include the table name in the expression:

TimeFrame:[Date of Visit] – [tbl_NewInfo].[End Date]

You might prefer to use the DateDiff function, however, rather than simple
date arithmetic. This will give the difference in days even if the values
happen to include non-zero times of day, which can all too easily be there
without your being aware of it unless validation rules have been included in
the table design to explicitly prevent it:

TimeFrameateDiff("d", [tbl_NewInfo].[End Date], [Date of Visit])

Ken Sheridan
Stafford, England

 

[Radhika]

I put in the following expression:
TimeFrame[tbl_Followup].[Date of Visit]-[tbl_NewInfo].[End Date]).
I get no error msg, but in place of values in the TimeFrame filed I get
'#Error'. Why is this happenning?
Thankyou!

You must fully qualify the field name like:
[Table Name].[Field Name]

I use a naming convention that results in every field in my MDBs having a
unique name. This shields me from this type of issue.
--
Duane Hookom
Microsoft Access MVP

I am trying to calculate the difference between two dates 'End Date' and
'Date of Visit' using a query. This is the expression I used:
TimeFrame:[Date of Visit] - [End Date].
However, 'End Date' is present in both tables I have used to create this
query 'tbl_NewInfo' and 'tbl_Foloowup'. I want the 'End Date' from
'tbl_NewInfo'.
How do i specify this in the expression and thereby prevent the error msg I
am getting?

Thankyou

 

[Radhika]

Thankyou!

Include the table name in the expression:

TimeFrame:[Date of Visit] – [tbl_NewInfo].[End Date]

You might prefer to use the DateDiff function, however, rather than simple
date arithmetic. This will give the difference in days even if the values
happen to include non-zero times of day, which can all too easily be there
without your being aware of it unless validation rules have been included in
the table design to explicitly prevent it:

TimeFrameateDiff("d", [tbl_NewInfo].[End Date], [Date of Visit])

Ken Sheridan
Stafford, England

I am trying to calculate the difference between two dates 'End Date' and
'Date of Visit' using a query. This is the expression I used:
TimeFrame:[Date of Visit] - [End Date].
However, 'End Date' is present in both tables I have used to create this
query 'tbl_NewInfo' and 'tbl_Foloowup'. I want the 'End Date' from
'tbl_NewInfo'.
How do i specify this in the expression and thereby prevent the error msg I
am getting?

Thankyou

 

[Duane Hookom]

You might have spelled something incorrectly or values might be null or you
might already have a field named "timeframe" or the tables might not be in
your query or the fields might not be date/time fields ...
--
Duane Hookom
Microsoft Access MVP

I put in the following expression:
TimeFrame[tbl_Followup].[Date of Visit]-[tbl_NewInfo].[End Date]).
I get no error msg, but in place of values in the TimeFrame filed I get
'#Error'. Why is this happenning?
Thankyou!

You must fully qualify the field name like:
[Table Name].[Field Name]

I use a naming convention that results in every field in my MDBs having a
unique name. This shields me from this type of issue.
--
Duane Hookom
Microsoft Access MVP

I am trying to calculate the difference between two dates 'End Date' and
'Date of Visit' using a query. This is the expression I used:
TimeFrame:[Date of Visit] - [End Date].
However, 'End Date' is present in both tables I have used to create this
query 'tbl_NewInfo' and 'tbl_Foloowup'. I want the 'End Date' from
'tbl_NewInfo'.
How do i specify this in the expression and thereby prevent the error msg I
am getting?

Thankyou