#Error

[Greg]

I am creating database that will track issues for each day. Issues can be in
4 different statuses: Open, Assigned, Pending and Closed. At the end of the
day I would like to create a report that will show me all issues opened for a
specific day. I have fields called Time Open and Time closed (set in general
date format) and Time difference where I am calculating time difference
between opened and closed issue. I would like to show on my report all
issues and since there is no closed time defined for Open, Assign and Pending
status I am receiving an error message “#Error†in my report. How do I
clear this error message and replace for ex. with text “Issue still open�
Record source for this report is Query named Turnover. I was thinking about
using IIf function but I don’t know exactly where to put it. Thanks

 

[Steve Schapel]

Greg,

Can you post back please with the SQL view of the query? And also let
us know where exactly you are seeing the #Error?

 

[Greg]

SELECT [Remedy Tickets].[Ticket #], [Remedy Tickets].[Ticket Description],
[Remedy Tickets].[Assigned To], [Remedy Tickets].[Open Date], [Remedy
Tickets].Status, [Remedy Tickets].[#Groups], [Remedy Tickets].[Client
Impact], [Remedy Tickets].[Time Closed], [Remedy Tickets].[Time Diference]
FROM [Remedy Tickets]
ORDER BY [Remedy Tickets].[Ticket #] DESC;

# error is in field [Time Difference] because tickets in open, assign and
pending status don't have closure time. Time difference is calculated by
subtracting [Time Closed]-[Open Date]. [Time Closed] and [Open Date] are in
general date format and I am using function =ElapsedTimeString([Open
Date],[Time Closed]) in control source of [Time Difference] Like I
mentioned before [Time Difference] is empty since there is no closure time
and I would like to replace #Error with text “Still open issueâ€

# error is in field [Time Diference] because tickets in open, asssign and
pending status don't have clouser time.

 

[Greg]

SELECT [Remedy Tickets].[Ticket #], [Remedy Tickets].[Ticket Description],
[Remedy Tickets].[Assigned To], [Remedy Tickets].[Open Date], [Remedy
Tickets].Status, [Remedy Tickets].[#Groups], [Remedy Tickets].[Client
Impact], [Remedy Tickets].[Time Closed], [Remedy Tickets].[Time Diference]
FROM [Remedy Tickets]
ORDER BY [Remedy Tickets].[Ticket #] DESC;


# error is in field [Time Difference] because tickets in open, assign and
pending status don't have closure time. Time difference is calculated by
subtracting [Time Closed]-[Open Date]. [Time Closed] and [Open Date] are in
general date format and I am using function =ElapsedTimeString([Open
Date],[Time Closed]) in control source of [Time Difference] Like I
mentioned before [Time Difference] is empty since there is no closure time
and I would like to replace #Error with text “Still open issueâ€

 

[Steve Schapel]

Sorry, Greg, I'm afraid you've lost me there. What it sounds loke to me
is that [Remedy Tickets] is a query. Is that right? And the [Remedy
Tickets] query is based on another query? Or else the [Time Diference]
column is a calculated field that uses a function ElapsedTimeString(),
is that right? I think this must be a custom function, which I am not
familiar with. But it sounds like you will either need to edit the code
for this function, in order to cater to null fields being used as
arguments in the function. Or else alter the way the function is being
used.

 

[Greg]

Remedy Tickets is the name of the table. Name of the query is Turnover Day
and name of report in which I am receiving #error is Turnover report. [Time
Diference]
column is a calculated field that uses a function ElapsedTimeString(),
Function looks like this and displays diffrence between two day in format XX
hours, XX minutes, XX seconds

Public Function ElapsedTimeString(Date1 As Date, _
Date2 As Date) _
As String
'*************************************************************
' Function ElapsedTimeString(Date1 As Date,
' Date2 As Date) As String
' Returns the time elapsed between a starting Date/Time and
' an ending Date/Time formatted as a string that looks like
' this:
' "10 days, 20 hours, 30 minutes, 40 seconds".
'*************************************************************
Dim Interval As Double, str As String, days As Variant
Dim hours As String, minutes As String, seconds As String

If IsNull(Date1) = True Or _
IsNull(Date2) = True Then Exit Function

Interval = Date2 - Date1
days = Fix(CSng(Interval))
hours = Format(Interval, "h")
minutes = Format(Interval, "n")
seconds = Format(Interval, "s")

' Days part of the string
str = IIf(days = 0, "", _
IIf(days = 1, days & " Day", days & " Days"))
str = str & IIf(days = 0, "", _
IIf(hours & minutes & seconds <> "000", ", ", " "))

' Hours part of the string
str = str & IIf(hours = "0", "", _
IIf(hours = "1", hours & " Hour", hours & " Hours"))
str = str & IIf(hours = "0", "", _
IIf(minutes & seconds <> "00", ", ", " "))

' Minutes part of the string
str = str & IIf(minutes = "0", "", _
IIf(minutes = "1", minutes & " Minute", _
minutes & " Minutes"))


str = str & IIf(minutes = "0", "", _
IIf(seconds <> "0", ", ", " "))

'Seconds part of the string
str = str & IIf(seconds = "0", "", _
IIf(seconds = "1", seconds & " Second", _
seconds & " Seconds"))

ElapsedTimeString = IIf(str = "", "0", str)


End Function

Sorry, Greg, I'm afraid you've lost me there. What it sounds loke to me
is that [Remedy Tickets] is a query. Is that right? And the [Remedy
Tickets] query is based on another query? Or else the [Time Diference]
column is a calculated field that uses a function ElapsedTimeString(),
is that right? I think this must be a custom function, which I am not
familiar with. But it sounds like you will either need to edit the code
for this function, in order to cater to null fields being used as
arguments in the function. Or else alter the way the function is being
used.

--
Steve Schapel, Microsoft Access MVP

SELECT [Remedy Tickets].[Ticket #], [Remedy Tickets].[Ticket Description],
[Remedy Tickets].[Assigned To], [Remedy Tickets].[Open Date], [Remedy
Tickets].Status, [Remedy Tickets].[#Groups], [Remedy Tickets].[Client
Impact], [Remedy Tickets].[Time Closed], [Remedy Tickets].[Time Diference]
FROM [Remedy Tickets]
ORDER BY [Remedy Tickets].[Ticket #] DESC;

# error is in field [Time Difference] because tickets in open, assign and
pending status don't have closure time. Time difference is calculated by
subtracting [Time Closed]-[Open Date]. [Time Closed] and [Open Date] are in
general date format and I am using function =ElapsedTimeString([Open
Date],[Time Closed]) in control source of [Time Difference] Like I
mentioned before [Time Difference] is empty since there is no closure time
and I would like to replace #Error with text “Still open issueâ€

 

[Steve Schapel]

Greg,

Sorry, I must confess I still find it confusing to try and reconcile
these three things:
1. Your query includes this in the SELECT clause:
[Remedy Tickets].[Time Diference]
2. [Remedy Tickets] is a table
3. [Time Diference] is a calculated field

I mean, these just don't hang together.

Anyway, there are some problems with the ElapsedTimeString() function.
We start out with:
Public Function ElapsedTimeString(Date1 As Date, Date2 As Date) As String
.... and then, we see:
If IsNull(Date1) = True Or IsNull(Date2) = True Then
But, Date1 and Date2 are typed as Date variables, so by definition they
can't be Null, so it doesn't really make sense, if you see what I mean.

Nevertheless, it is probably easiest to cater to this in your calculated
expression, rather than modifying the function. Therefore, instead of:
=ElapsedTimeString([Open Date],[Time Closed])
.... you could replace with something like this:
=IIf([Open Date] Is Null Or [Time Closed] Is Null,"Still
open",ElapsedTimeString([Open Date],[Time Closed]))

Do you think that will work?

 

[Greg]

Thanks Steve, you are absolutely right this doesn’t make much sense, but I am
new in Access. As far as elapsed time function I found it on official
Microsoft web site. Anyway your if function worked great, thanks for your
help.

If you can take a look at my sql and tell me how to crate report that will
select tickets open for today
SELECT [Remedy Tickets].[Ticket #], [Remedy Tickets].[Ticket Description],
[Remedy Tickets].[Assigned To], [Remedy Tickets].[Open Date], [Remedy
Tickets].Status, [Remedy Tickets].[#Groups], [Remedy Tickets].[Client
Impact], [Remedy Tickets].[Time Closed]
FROM [Remedy Tickets]
ORDER BY [Remedy Tickets].[Ticket #] DESC;

I tried
Where [Remedy Tickets].[Open Date] = Now()
but is not working

Thanks again for all your help

Greg,

Sorry, I must confess I still find it confusing to try and reconcile
these three things:
1. Your query includes this in the SELECT clause:
[Remedy Tickets].[Time Diference]
2. [Remedy Tickets] is a table
3. [Time Diference] is a calculated field

I mean, these just don't hang together.

Anyway, there are some problems with the ElapsedTimeString() function.
We start out with:
Public Function ElapsedTimeString(Date1 As Date, Date2 As Date) As String
.... and then, we see:
If IsNull(Date1) = True Or IsNull(Date2) = True Then
But, Date1 and Date2 are typed as Date variables, so by definition they
can't be Null, so it doesn't really make sense, if you see what I mean.

Nevertheless, it is probably easiest to cater to this in your calculated
expression, rather than modifying the function. Therefore, instead of:
=ElapsedTimeString([Open Date],[Time Closed])
.... you could replace with something like this:
=IIf([Open Date] Is Null Or [Time Closed] Is Null,"Still
open",ElapsedTimeString([Open Date],[Time Closed]))

Do you think that will work?

--
Steve Schapel, Microsoft Access MVP

Remedy Tickets is the name of the table. Name of the query is Turnover Day
and name of report in which I am receiving #error is Turnover report. [Time
Diference]
column is a calculated field that uses a function ElapsedTimeString(),
Function looks like this and displays diffrence between two day in format XX
hours, XX minutes, XX seconds

 

[Steve Schapel]

Greg,

The Now() function returns the current date *and time*, so your data
will never match this. Try like this instead:

WHERE [Remedy Tickets].[Open Date] = Date()

 

[Greg]

Thank you very much

Greg,

The Now() function returns the current date *and time*, so your data
will never match this. Try like this instead:

WHERE [Remedy Tickets].[Open Date] = Date()

--
Steve Schapel, Microsoft Access MVP

Thanks Steve, you are absolutely right this doesn’t make much sense, but I am
new in Access. As far as elapsed time function I found it on official
Microsoft web site. Anyway your if function worked great, thanks for your
help.

If you can take a look at my sql and tell me how to crate report that will
select tickets open for today
SELECT [Remedy Tickets].[Ticket #], [Remedy Tickets].[Ticket Description],
[Remedy Tickets].[Assigned To], [Remedy Tickets].[Open Date], [Remedy
Tickets].Status, [Remedy Tickets].[#Groups], [Remedy Tickets].[Client
Impact], [Remedy Tickets].[Time Closed]
FROM [Remedy Tickets]
ORDER BY [Remedy Tickets].[Ticket #] DESC;

I tried
Where [Remedy Tickets].[Open Date] = Now()
but is not working