#error message in results

[Rick Campbell]

The frustrating thing about this message is it is sporadic, i.e., it doesn't
happen on all records.

I'm using a vba module from an O'Reilly book to calculate medians.

The code for the module follows:
======================================
Public Function acbDMedian( _
ByVal strField As String, ByVal strDomain As String, _
Optional ByVal strCriteria As String) As Variant

' Purpose:
' To calculate the median value
' for a field in a table or query.
' In:
' strField: the field
' strDomain: the table or query
' strCriteria: an optional WHERE clause to
' apply to the table or query
' Out:
' Return value: the median, if successful;
' Otherwise, an Error value.

Dim db As DAO.Database
Dim rstDomain As DAO.Recordset
Dim strSQL As String
Dim varMedian As Variant
Dim intFieldType As Integer
Dim intRecords As Integer

Const acbcErrAppTypeError = 3169

On Error GoTo HandleErr

Set db = CurrentDb()

' Initialize return value
varMedian = Null

' Build SQL string for recordset
strSQL = "Select " & strField
strSQL = strSQL & " FROM " & strDomain

' Only use a WHERE clause if one is passed in
If Len(strCriteria) > 0 Then
strSQL = strSQL & " WHERE " & strCriteria
End If

strSQL = strSQL & " ORDER BY " & strField

Set rstDomain = db.OpenRecordset(strSQL, dbOpenSnapshot)

' Check the data type of the median field
intFieldType = rstDomain.Fields(strField).Type
Select Case intFieldType
Case dbByte, dbInteger, dbLong, dbCurrency, dbSingle, dbDouble, dbDate
' Numeric field
If Not rstDomain.EOF Then
rstDomain.MoveLast
intRecords = rstDomain.RecordCount
' Start from the first record
rstDomain.MoveFirst

If (intRecords Mod 2) = 0 Then
' Even number of records
' No middle record, so move to the
' record right before the middle
rstDomain.Move ((intRecords \ 2) - 1)
varMedian = rstDomain.Fields(strField)
' Now move to the next record, the
' one right after the middle
rstDomain.MoveNext
' And average the two values
varMedian = (varMedian + rstDomain.Fields(strField)) / 2
' Make sure you return a date, even when
' averaging two dates
If intFieldType = dbDate And Not IsNull(varMedian) Then
varMedian = CDate(varMedian)
End If
Else
' Odd number or records
' Move to the middle record and return its value
rstDomain.Move ((intRecords \ 2))
varMedian = rstDomain.Fields(strField)
End If
Else
' No records; return Null
varMedian = Null
End If
Case Else
' Non-numeric field; so raise an app error
Err.Raise acbcErrAppTypeError
End Select

acbDMedian = varMedian

ExitHere:
On Error Resume Next
rstDomain.Close
Set rstDomain = Nothing
Exit Function

HandleErr:
' Return an error value
acbDMedian = CVErr(Err.Number)
Resume ExitHere
End Function
========================================
I use the following SQL to produce my results:
========================================
SELECT DISTINCTROW Sales.Style, CityZipLookUp.CityUp AS City,
(Format$(Sales.SoldDate,'mmm yyyy')) AS SDate, Avg(Sales.SoldPrice) AS
Average, CCur(acbDMedian("SoldPrice","Sales","Format$(SoldDate,'mmm yyyy') =
'" & Format$(Sales.SoldDate,'mmm yyyy') & "' And City = '" &
CityZipLookUp.CityUp & "' And [Style] = '" & Sales.Style & "'")) AS Median,
Count(*) AS Sales, Avg(Sales.DaysOnMarket) AS DOM, [Average]/[ListPrice] AS
[SP/LP], Avg(Sales.ListPrice) AS ListPrice
FROM Sales INNER JOIN CityZipLookUp ON Sales.ZipCode = CityZipLookUp.ZipCode
GROUP BY Sales.Style, CityZipLookUp.CityUp, CityZipLookUp.CityUp,
Format$(Sales.SoldDate,'mmm yyyy'),
Year(Sales.SoldDate)*12+DatePart('m',Sales.SoldDate)-1
HAVING (((CityZipLookUp.CityUp)="Nestor"))
ORDER BY Sales.Style DESC ,
Year(Sales.SoldDate)*12+DatePart('m',Sales.SoldDate)-1 DESC;
===========================================
I've checked the data and it looks fine. Nothing pops out to say "fix me!"

The error message so far is consistent, it happens in the same months. I
could understand if the module ALWAYS returned an #error, but not just
sometimes.

Anyone have a clue about this?

TIA

Rick

 

[Wayne Morgan]

Where are you placing these results? Is the result being returned that
causes the #Error a Null result? If so, try the Nz function to change the
Null to something else.

Nz(acbDMedian(Field, Domain, Criteria), 0)
or
Nz(acbDMedian(Field, Domain, Criteria), "")
or whatever value would be meaningful to where you are placing this.

--
Wayne Morgan
Microsoft Access MVP

The frustrating thing about this message is it is sporadic, i.e., it doesn't
happen on all records.

I'm using a vba module from an O'Reilly book to calculate medians.

The code for the module follows:
======================================
Public Function acbDMedian( _
ByVal strField As String, ByVal strDomain As String, _
Optional ByVal strCriteria As String) As Variant

' Purpose:
' To calculate the median value
' for a field in a table or query.
' In:
' strField: the field
' strDomain: the table or query
' strCriteria: an optional WHERE clause to
' apply to the table or query
' Out:
' Return value: the median, if successful;
' Otherwise, an Error value.

Dim db As DAO.Database
Dim rstDomain As DAO.Recordset
Dim strSQL As String
Dim varMedian As Variant
Dim intFieldType As Integer
Dim intRecords As Integer

Const acbcErrAppTypeError = 3169

On Error GoTo HandleErr

Set db = CurrentDb()

' Initialize return value
varMedian = Null

' Build SQL string for recordset
strSQL = "Select " & strField
strSQL = strSQL & " FROM " & strDomain

' Only use a WHERE clause if one is passed in
If Len(strCriteria) > 0 Then
strSQL = strSQL & " WHERE " & strCriteria
End If

strSQL = strSQL & " ORDER BY " & strField

Set rstDomain = db.OpenRecordset(strSQL, dbOpenSnapshot)

' Check the data type of the median field
intFieldType = rstDomain.Fields(strField).Type
Select Case intFieldType
Case dbByte, dbInteger, dbLong, dbCurrency, dbSingle, dbDouble, dbDate
' Numeric field
If Not rstDomain.EOF Then
rstDomain.MoveLast
intRecords = rstDomain.RecordCount
' Start from the first record
rstDomain.MoveFirst

If (intRecords Mod 2) = 0 Then
' Even number of records
' No middle record, so move to the
' record right before the middle
rstDomain.Move ((intRecords \ 2) - 1)
varMedian = rstDomain.Fields(strField)
' Now move to the next record, the
' one right after the middle
rstDomain.MoveNext
' And average the two values
varMedian = (varMedian + rstDomain.Fields(strField)) / 2
' Make sure you return a date, even when
' averaging two dates
If intFieldType = dbDate And Not IsNull(varMedian) Then
varMedian = CDate(varMedian)
End If
Else
' Odd number or records
' Move to the middle record and return its value
rstDomain.Move ((intRecords \ 2))
varMedian = rstDomain.Fields(strField)
End If
Else
' No records; return Null
varMedian = Null
End If
Case Else
' Non-numeric field; so raise an app error
Err.Raise acbcErrAppTypeError
End Select

acbDMedian = varMedian

ExitHere:
On Error Resume Next
rstDomain.Close
Set rstDomain = Nothing
Exit Function

HandleErr:
' Return an error value
acbDMedian = CVErr(Err.Number)
Resume ExitHere
End Function
========================================
I use the following SQL to produce my results:
========================================
SELECT DISTINCTROW Sales.Style, CityZipLookUp.CityUp AS City,
(Format$(Sales.SoldDate,'mmm yyyy')) AS SDate, Avg(Sales.SoldPrice) AS
Average, CCur(acbDMedian("SoldPrice","Sales","Format$(SoldDate,'mmm yyyy') =
'" & Format$(Sales.SoldDate,'mmm yyyy') & "' And City = '" &
CityZipLookUp.CityUp & "' And [Style] = '" & Sales.Style & "'")) AS Median,
Count(*) AS Sales, Avg(Sales.DaysOnMarket) AS DOM, [Average]/[ListPrice] AS
[SP/LP], Avg(Sales.ListPrice) AS ListPrice
FROM Sales INNER JOIN CityZipLookUp ON Sales.ZipCode = CityZipLookUp.ZipCode
GROUP BY Sales.Style, CityZipLookUp.CityUp, CityZipLookUp.CityUp,
Format$(Sales.SoldDate,'mmm yyyy'),
Year(Sales.SoldDate)*12+DatePart('m',Sales.SoldDate)-1
HAVING (((CityZipLookUp.CityUp)="Nestor"))
ORDER BY Sales.Style DESC ,
Year(Sales.SoldDate)*12+DatePart('m',Sales.SoldDate)-1 DESC;
===========================================
I've checked the data and it looks fine. Nothing pops out to say "fix me!"

The error message so far is consistent, it happens in the same months. I
could understand if the module ALWAYS returned an #error, but not just
sometimes.

Anyone have a clue about this?

TIA

Rick

 

[Rick Campbell]

The results are in a query. And no, they are not null results. If I copy the
underlying fields to Excel, I can get the median.

Here are the results for one city:

RES Annual Sales Mission Valley City Year Average Median Sales DOM SP/LP
Mission Valley 1998 $184,215.08 $165,000 126 28.06 98.57%
Mission Valley 1999 $212,485.65 #Error 156 26.78 98.26%
Mission Valley 2000 $252,482.65 #Error 170 27.34 98.19%
Mission Valley 2001 $290,537.08 #Error 164 36.73 97.59%
Mission Valley 2002 $367,632.90 #Error 155 27.65 98.22%
Mission Valley 2003 $414,524.20 $561,000 176 34.34 98.11%

Where are you placing these results? Is the result being returned that
causes the #Error a Null result? If so, try the Nz function to change the
Null to something else.

Nz(acbDMedian(Field, Domain, Criteria), 0)
or
Nz(acbDMedian(Field, Domain, Criteria), "")
or whatever value would be meaningful to where you are placing this.

--
Wayne Morgan
Microsoft Access MVP

The frustrating thing about this message is it is sporadic, i.e., it doesn't
happen on all records.

I'm using a vba module from an O'Reilly book to calculate medians.

The code for the module follows:
======================================
Public Function acbDMedian( _
ByVal strField As String, ByVal strDomain As String, _
Optional ByVal strCriteria As String) As Variant

' Purpose:
' To calculate the median value
' for a field in a table or query.
' In:
' strField: the field
' strDomain: the table or query
' strCriteria: an optional WHERE clause to
' apply to the table or query
' Out:
' Return value: the median, if successful;
' Otherwise, an Error value.

Dim db As DAO.Database
Dim rstDomain As DAO.Recordset
Dim strSQL As String
Dim varMedian As Variant
Dim intFieldType As Integer
Dim intRecords As Integer

Const acbcErrAppTypeError = 3169

On Error GoTo HandleErr

Set db = CurrentDb()

' Initialize return value
varMedian = Null

' Build SQL string for recordset
strSQL = "Select " & strField
strSQL = strSQL & " FROM " & strDomain

' Only use a WHERE clause if one is passed in
If Len(strCriteria) > 0 Then
strSQL = strSQL & " WHERE " & strCriteria
End If

strSQL = strSQL & " ORDER BY " & strField

Set rstDomain = db.OpenRecordset(strSQL, dbOpenSnapshot)

' Check the data type of the median field
intFieldType = rstDomain.Fields(strField).Type
Select Case intFieldType
Case dbByte, dbInteger, dbLong, dbCurrency, dbSingle, dbDouble, dbDate
' Numeric field
If Not rstDomain.EOF Then
rstDomain.MoveLast
intRecords = rstDomain.RecordCount
' Start from the first record
rstDomain.MoveFirst

If (intRecords Mod 2) = 0 Then
' Even number of records
' No middle record, so move to the
' record right before the middle
rstDomain.Move ((intRecords \ 2) - 1)
varMedian = rstDomain.Fields(strField)
' Now move to the next record, the
' one right after the middle
rstDomain.MoveNext
' And average the two values
varMedian = (varMedian + rstDomain.Fields(strField)) / 2
' Make sure you return a date, even when
' averaging two dates
If intFieldType = dbDate And Not IsNull(varMedian) Then
varMedian = CDate(varMedian)
End If
Else
' Odd number or records
' Move to the middle record and return its value
rstDomain.Move ((intRecords \ 2))
varMedian = rstDomain.Fields(strField)
End If
Else
' No records; return Null
varMedian = Null
End If
Case Else
' Non-numeric field; so raise an app error
Err.Raise acbcErrAppTypeError
End Select

acbDMedian = varMedian

ExitHere:
On Error Resume Next
rstDomain.Close
Set rstDomain = Nothing
Exit Function

HandleErr:
' Return an error value
acbDMedian = CVErr(Err.Number)
Resume ExitHere
End Function
========================================
I use the following SQL to produce my results:
========================================
SELECT DISTINCTROW Sales.Style, CityZipLookUp.CityUp AS City,
(Format$(Sales.SoldDate,'mmm yyyy')) AS SDate, Avg(Sales.SoldPrice) AS
Average, CCur(acbDMedian("SoldPrice","Sales","Format$(SoldDate,'mmm

yyyy')

=
'" & Format$(Sales.SoldDate,'mmm yyyy') & "' And City = '" &
CityZipLookUp.CityUp & "' And [Style] = '" & Sales.Style & "'")) AS Median,
Count(*) AS Sales, Avg(Sales.DaysOnMarket) AS DOM, [Average]/[ListPrice] AS
[SP/LP], Avg(Sales.ListPrice) AS ListPrice
FROM Sales INNER JOIN CityZipLookUp ON Sales.ZipCode = CityZipLookUp.ZipCode
GROUP BY Sales.Style, CityZipLookUp.CityUp, CityZipLookUp.CityUp,
Format$(Sales.SoldDate,'mmm yyyy'),
Year(Sales.SoldDate)*12+DatePart('m',Sales.SoldDate)-1
HAVING (((CityZipLookUp.CityUp)="Nestor"))
ORDER BY Sales.Style DESC ,
Year(Sales.SoldDate)*12+DatePart('m',Sales.SoldDate)-1 DESC;
===========================================
I've checked the data and it looks fine. Nothing pops out to say "fix me!"

The error message so far is consistent, it happens in the same months. I
could understand if the module ALWAYS returned an #error, but not just
sometimes.

Anyone have a clue about this?

TIA

Rick

 

[Wayne Morgan]

The error handler in the function is set to return the error number as the
function's value if there is an error. Could this error number be incorrect
for the field? You may want to change the error handler temporarily to
pop-up a message box if there is an error.

--
Wayne Morgan
Microsoft Access MVP

The results are in a query. And no, they are not null results. If I copy the
underlying fields to Excel, I can get the median.

Here are the results for one city:

RES Annual Sales Mission Valley City Year Average Median Sales DOM SP/LP
Mission Valley 1998 $184,215.08 $165,000 126 28.06 98.57%
Mission Valley 1999 $212,485.65 #Error 156 26.78 98.26%
Mission Valley 2000 $252,482.65 #Error 170 27.34 98.19%
Mission Valley 2001 $290,537.08 #Error 164 36.73 97.59%
Mission Valley 2002 $367,632.90 #Error 155 27.65 98.22%
Mission Valley 2003 $414,524.20 $561,000 176 34.34 98.11%

Where are you placing these results? Is the result being returned that
causes the #Error a Null result? If so, try the Nz function to change the
Null to something else.

Nz(acbDMedian(Field, Domain, Criteria), 0)
or
Nz(acbDMedian(Field, Domain, Criteria), "")
or whatever value would be meaningful to where you are placing this.

--
Wayne Morgan
Microsoft Access MVP

The frustrating thing about this message is it is sporadic, i.e., it doesn't
happen on all records.

I'm using a vba module from an O'Reilly book to calculate medians.

The code for the module follows:
======================================
Public Function acbDMedian( _
ByVal strField As String, ByVal strDomain As String, _
Optional ByVal strCriteria As String) As Variant

' Purpose:
' To calculate the median value
' for a field in a table or query.
' In:
' strField: the field
' strDomain: the table or query
' strCriteria: an optional WHERE clause to
' apply to the table or query
' Out:
' Return value: the median, if successful;
' Otherwise, an Error value.

Dim db As DAO.Database
Dim rstDomain As DAO.Recordset
Dim strSQL As String
Dim varMedian As Variant
Dim intFieldType As Integer
Dim intRecords As Integer

Const acbcErrAppTypeError = 3169

On Error GoTo HandleErr

Set db = CurrentDb()

' Initialize return value
varMedian = Null

' Build SQL string for recordset
strSQL = "Select " & strField
strSQL = strSQL & " FROM " & strDomain

' Only use a WHERE clause if one is passed in
If Len(strCriteria) > 0 Then
strSQL = strSQL & " WHERE " & strCriteria
End If

strSQL = strSQL & " ORDER BY " & strField

Set rstDomain = db.OpenRecordset(strSQL, dbOpenSnapshot)

' Check the data type of the median field
intFieldType = rstDomain.Fields(strField).Type
Select Case intFieldType
Case dbByte, dbInteger, dbLong, dbCurrency, dbSingle, dbDouble, dbDate
' Numeric field
If Not rstDomain.EOF Then
rstDomain.MoveLast
intRecords = rstDomain.RecordCount
' Start from the first record
rstDomain.MoveFirst

If (intRecords Mod 2) = 0 Then
' Even number of records
' No middle record, so move to the
' record right before the middle
rstDomain.Move ((intRecords \ 2) - 1)
varMedian = rstDomain.Fields(strField)
' Now move to the next record, the
' one right after the middle
rstDomain.MoveNext
' And average the two values
varMedian = (varMedian + rstDomain.Fields(strField)) / 2
' Make sure you return a date, even when
' averaging two dates
If intFieldType = dbDate And Not IsNull(varMedian) Then
varMedian = CDate(varMedian)
End If
Else
' Odd number or records
' Move to the middle record and return its value
rstDomain.Move ((intRecords \ 2))
varMedian = rstDomain.Fields(strField)
End If
Else
' No records; return Null
varMedian = Null
End If
Case Else
' Non-numeric field; so raise an app error
Err.Raise acbcErrAppTypeError
End Select

acbDMedian = varMedian

ExitHere:
On Error Resume Next
rstDomain.Close
Set rstDomain = Nothing
Exit Function

HandleErr:
' Return an error value
acbDMedian = CVErr(Err.Number)
Resume ExitHere
End Function
========================================
I use the following SQL to produce my results:
========================================
SELECT DISTINCTROW Sales.Style, CityZipLookUp.CityUp AS City,
(Format$(Sales.SoldDate,'mmm yyyy')) AS SDate, Avg(Sales.SoldPrice) AS
Average, CCur(acbDMedian("SoldPrice","Sales","Format$(SoldDate,'mmm

yyyy')

=
'" & Format$(Sales.SoldDate,'mmm yyyy') & "' And City = '" &
CityZipLookUp.CityUp & "' And [Style] = '" & Sales.Style & "'")) AS Median,
Count(*) AS Sales, Avg(Sales.DaysOnMarket) AS DOM,

[Average]/[ListPrice]
AS

[SP/LP], Avg(Sales.ListPrice) AS ListPrice
FROM Sales INNER JOIN CityZipLookUp ON Sales.ZipCode = CityZipLookUp.ZipCode
GROUP BY Sales.Style, CityZipLookUp.CityUp, CityZipLookUp.CityUp,
Format$(Sales.SoldDate,'mmm yyyy'),
Year(Sales.SoldDate)*12+DatePart('m',Sales.SoldDate)-1
HAVING (((CityZipLookUp.CityUp)="Nestor"))
ORDER BY Sales.Style DESC ,
Year(Sales.SoldDate)*12+DatePart('m',Sales.SoldDate)-1 DESC;
===========================================
I've checked the data and it looks fine. Nothing pops out to say "fix me!"

The error message so far is consistent, it happens in the same months. I
could understand if the module ALWAYS returned an #error, but not just
sometimes.

Anyone have a clue about this?

TIA

Rick

 

[Wayne Morgan]

The better way to disable the error handling temporarily would be to comment
out the

On Error GoTo HandleErr

line. This will cause the function to break into the code with the line
creating the error highlighted.

 

[Rick Campbell]

There are no error numbers, only #Error. The numbers after the error in the
list I posted are the field values for field Sales.

I commented out the error handler and still get the same thing.

The error handler in the function is set to return the error number as the
function's value if there is an error. Could this error number be incorrect
for the field? You may want to change the error handler temporarily to
pop-up a message box if there is an error.

--
Wayne Morgan
Microsoft Access MVP

The results are in a query. And no, they are not null results. If I copy the
underlying fields to Excel, I can get the median.

Here are the results for one city:

RES Annual Sales Mission Valley City Year Average Median Sales DOM SP/LP
Mission Valley 1998 $184,215.08 $165,000 126 28.06 98.57%
Mission Valley 1999 $212,485.65 #Error 156 26.78 98.26%
Mission Valley 2000 $252,482.65 #Error 170 27.34 98.19%
Mission Valley 2001 $290,537.08 #Error 164 36.73 97.59%
Mission Valley 2002 $367,632.90 #Error 155 27.65 98.22%
Mission Valley 2003 $414,524.20 $561,000 176 34.34 98.11%

/

2

' Make sure you return a date, even when
' averaging two dates
If intFieldType = dbDate And Not IsNull(varMedian) Then
varMedian = CDate(varMedian)
End If
Else
' Odd number or records
' Move to the middle record and return its value
rstDomain.Move ((intRecords \ 2))
varMedian = rstDomain.Fields(strField)
End If
Else
' No records; return Null
varMedian = Null
End If
Case Else
' Non-numeric field; so raise an app error
Err.Raise acbcErrAppTypeError
End Select

acbDMedian = varMedian

ExitHere:
On Error Resume Next
rstDomain.Close
Set rstDomain = Nothing
Exit Function

HandleErr:
' Return an error value
acbDMedian = CVErr(Err.Number)
Resume ExitHere
End Function
========================================
I use the following SQL to produce my results:
========================================
SELECT DISTINCTROW Sales.Style, CityZipLookUp.CityUp AS City,
(Format$(Sales.SoldDate,'mmm yyyy')) AS SDate, Avg(Sales.SoldPrice) AS
Average, CCur(acbDMedian("SoldPrice","Sales","Format$(SoldDate,'mmm yyyy')
=
'" & Format$(Sales.SoldDate,'mmm yyyy') & "' And City = '" &
CityZipLookUp.CityUp & "' And [Style] = '" & Sales.Style & "'")) AS
Median,
Count(*) AS Sales, Avg(Sales.DaysOnMarket) AS DOM, [Average]/[ListPrice]
AS
[SP/LP], Avg(Sales.ListPrice) AS ListPrice
FROM Sales INNER JOIN CityZipLookUp ON Sales.ZipCode =
CityZipLookUp.ZipCode
GROUP BY Sales.Style, CityZipLookUp.CityUp, CityZipLookUp.CityUp,
Format$(Sales.SoldDate,'mmm yyyy'),
Year(Sales.SoldDate)*12+DatePart('m',Sales.SoldDate)-1
HAVING (((CityZipLookUp.CityUp)="Nestor"))
ORDER BY Sales.Style DESC ,
Year(Sales.SoldDate)*12+DatePart('m',Sales.SoldDate)-1 DESC;
===========================================
I've checked the data and it looks fine. Nothing pops out to say

"fix
me!"

The error message so far is consistent, it happens in the same

months.

I

 

[Wayne Morgan]

On the rows that return #Error, what are the values passed to the acbDMedian
function?

Also, in the call to the function,

CCur(acbDMedian("SoldPrice","Sales","Format$(SoldDate,'mmm yyyy') =
'" & Format$(Sales.SoldDate,'mmm yyyy') & "' And City = '" &
CityZipLookUp.CityUp & "' And [Style] = '" & Sales.Style & "'")) AS Median

The criteria is optional on the receiving end, but you're always passing
criteria. This is ok, but you are passing 3 criteria, is there always a
value for each of those 3 options? Are all the fields listed in the criteria
available in the Domain ("Sales")? This would be SoldDate, City, and
[Style]. While it probably doesn't matter, have you tried putting brackets
around SoldDate and City?

Format$([SoldDate],'mmm yyyy') =
And [City] =

Also, if there are no records, Null is returned. CCur(Null) will cause an
error.

--
Wayne Morgan
Microsoft Access MVP

There are no error numbers, only #Error. The numbers after the error in the
list I posted are the field values for field Sales.

I commented out the error handler and still get the same thing.

The error handler in the function is set to return the error number as the
function's value if there is an error. Could this error number be incorrect
for the field? You may want to change the error handler temporarily to
pop-up a message box if there is an error.

--
Wayne Morgan
Microsoft Access MVP


copy
the change
the

rstDomain.Fields(strField))

/

Avg(Sales.SoldPrice)

AS

Average, CCur(acbDMedian("SoldPrice","Sales","Format$(SoldDate,'mmm
yyyy')
=
'" & Format$(Sales.SoldDate,'mmm yyyy') & "' And City = '" &
CityZipLookUp.CityUp & "' And [Style] = '" & Sales.Style & "'")) AS
Median,
Count(*) AS Sales, Avg(Sales.DaysOnMarket) AS DOM, [Average]/[ListPrice]
AS
[SP/LP], Avg(Sales.ListPrice) AS ListPrice
FROM Sales INNER JOIN CityZipLookUp ON Sales.ZipCode =
CityZipLookUp.ZipCode
GROUP BY Sales.Style, CityZipLookUp.CityUp, CityZipLookUp.CityUp,
Format$(Sales.SoldDate,'mmm yyyy'),
Year(Sales.SoldDate)*12+DatePart('m',Sales.SoldDate)-1
HAVING (((CityZipLookUp.CityUp)="Nestor"))
ORDER BY Sales.Style DESC ,
Year(Sales.SoldDate)*12+DatePart('m',Sales.SoldDate)-1 DESC;
===========================================
I've checked the data and it looks fine. Nothing pops out to say "fix
me!"

The error message so far is consistent, it happens in the same

months.

I
could understand if the module ALWAYS returned an #error, but not just
sometimes.

Anyone have a clue about this?

TIA

Rick

 

[Rick Campbell]

Wayne,

I really appreciate your time on this. I've figured out the #error problem.
I was using a lookup table to correlate zip codes and cities.

I need the data in cities, but that field in the table was much less
accurate than the zip codes. I finally just made a new table where the city
field was replaced by its correlated zip codes.

Now, though, rather than getting the sporadic #error message, I'm getting a
median value of $3,061 for every set, grouped by city and date.

Although I can get accurate median values for the database as a whole,
selected by date values, I am still unable to add the third criterion,
cities.

Using brackets didn't help.

Also, all the fields are filled.

This function functions the same way as dAvg. So this should work. I think.

On the rows that return #Error, what are the values passed to the acbDMedian
function?

Also, in the call to the function,

CCur(acbDMedian("SoldPrice","Sales","Format$(SoldDate,'mmm yyyy') =
'" & Format$(Sales.SoldDate,'mmm yyyy') & "' And City = '" &
CityZipLookUp.CityUp & "' And [Style] = '" & Sales.Style & "'")) AS

Median

The criteria is optional on the receiving end, but you're always passing
criteria. This is ok, but you are passing 3 criteria, is there always a
value for each of those 3 options? Are all the fields listed in the criteria
available in the Domain ("Sales")? This would be SoldDate, City, and
[Style]. While it probably doesn't matter, have you tried putting brackets
around SoldDate and City?

Format$([SoldDate],'mmm yyyy') =
And [City] =

Also, if there are no records, Null is returned. CCur(Null) will cause an
error.

--
Wayne Morgan
Microsoft Access MVP

There are no error numbers, only #Error. The numbers after the error in the
list I posted are the field values for field Sales.

I commented out the error handler and still get the same thing.

i.e.,

it

doesn't
happen on all records.

I'm using a vba module from an O'Reilly book to calculate medians.

The code for the module follows:
======================================
Public Function acbDMedian( _
ByVal strField As String, ByVal strDomain As String, _
Optional ByVal strCriteria As String) As Variant

' Purpose:
' To calculate the median value
' for a field in a table or query.
' In:
' strField: the field
' strDomain: the table or query
' strCriteria: an optional WHERE clause to
' apply to the table or query
' Out:
' Return value: the median, if successful;
' Otherwise, an Error value.

Dim db As DAO.Database
Dim rstDomain As DAO.Recordset
Dim strSQL As String
Dim varMedian As Variant
Dim intFieldType As Integer
Dim intRecords As Integer

Const acbcErrAppTypeError = 3169

On Error GoTo HandleErr

Set db = CurrentDb()

' Initialize return value
varMedian = Null

' Build SQL string for recordset
strSQL = "Select " & strField
strSQL = strSQL & " FROM " & strDomain

' Only use a WHERE clause if one is passed in
If Len(strCriteria) > 0 Then
strSQL = strSQL & " WHERE " & strCriteria
End If

strSQL = strSQL & " ORDER BY " & strField

Set rstDomain = db.OpenRecordset(strSQL, dbOpenSnapshot)

' Check the data type of the median field
intFieldType = rstDomain.Fields(strField).Type
Select Case intFieldType
Case dbByte, dbInteger, dbLong, dbCurrency, dbSingle, dbDouble,
dbDate
' Numeric field
If Not rstDomain.EOF Then
rstDomain.MoveLast
intRecords = rstDomain.RecordCount
' Start from the first record
rstDomain.MoveFirst

If (intRecords Mod 2) = 0 Then
' Even number of records
' No middle record, so move to the
' record right before the middle
rstDomain.Move ((intRecords \ 2) - 1)
varMedian = rstDomain.Fields(strField)
' Now move to the next record, the
' one right after the middle
rstDomain.MoveNext
' And average the two values
varMedian = (varMedian +

rstDomain.Fields(strField))

/
2
' Make sure you return a date, even when
' averaging two dates
If intFieldType = dbDate And Not IsNull(varMedian)
Then
varMedian = CDate(varMedian)
End If
Else
' Odd number or records
' Move to the middle record and return its value
rstDomain.Move ((intRecords \ 2))
varMedian = rstDomain.Fields(strField)
End If
Else
' No records; return Null
varMedian = Null
End If
Case Else
' Non-numeric field; so raise an app error
Err.Raise acbcErrAppTypeError
End Select

acbDMedian = varMedian

ExitHere:
On Error Resume Next
rstDomain.Close
Set rstDomain = Nothing
Exit Function

HandleErr:
' Return an error value
acbDMedian = CVErr(Err.Number)
Resume ExitHere
End Function
========================================
I use the following SQL to produce my results:
========================================
SELECT DISTINCTROW Sales.Style, CityZipLookUp.CityUp AS City,
(Format$(Sales.SoldDate,'mmm yyyy')) AS SDate,

Avg(Sales.SoldPrice)

AS
Average, CCur(acbDMedian("SoldPrice","Sales","Format$(SoldDate,'mmm
yyyy')
=
'" & Format$(Sales.SoldDate,'mmm yyyy') & "' And City = '" &
CityZipLookUp.CityUp & "' And [Style] = '" & Sales.Style & "'")) AS
Median,
Count(*) AS Sales, Avg(Sales.DaysOnMarket) AS DOM,
[Average]/[ListPrice]
AS
[SP/LP], Avg(Sales.ListPrice) AS ListPrice
FROM Sales INNER JOIN CityZipLookUp ON Sales.ZipCode =
CityZipLookUp.ZipCode
GROUP BY Sales.Style, CityZipLookUp.CityUp, CityZipLookUp.CityUp,
Format$(Sales.SoldDate,'mmm yyyy'),
Year(Sales.SoldDate)*12+DatePart('m',Sales.SoldDate)-1
HAVING (((CityZipLookUp.CityUp)="Nestor"))
ORDER BY Sales.Style DESC ,
Year(Sales.SoldDate)*12+DatePart('m',Sales.SoldDate)-1 DESC;
===========================================
I've checked the data and it looks fine. Nothing pops out to say "fix
me!"

The error message so far is consistent, it happens in the same months.
I
could understand if the module ALWAYS returned an #error, but

not
just

sometimes.

Anyone have a clue about this?

TIA

Rick

 

[Wayne Morgan]

In-between these 2 lines

strSQL = strSQL & " ORDER BY " & strField

Set rstDomain = db.OpenRecordset(strSQL, dbOpenSnapshot)

Add

Debug.Print strSQL

This will print out the sql statement in the debug window. Copy and paste
that so we can see what the function is looking at.

--
Wayne Morgan
Microsoft Access MVP

Wayne,

I really appreciate your time on this. I've figured out the #error problem.
I was using a lookup table to correlate zip codes and cities.

I need the data in cities, but that field in the table was much less
accurate than the zip codes. I finally just made a new table where the city
field was replaced by its correlated zip codes.

Now, though, rather than getting the sporadic #error message, I'm getting a
median value of $3,061 for every set, grouped by city and date.

Although I can get accurate median values for the database as a whole,
selected by date values, I am still unable to add the third criterion,
cities.

Using brackets didn't help.

Also, all the fields are filled.

This function functions the same way as dAvg. So this should work. I think.

On the rows that return #Error, what are the values passed to the acbDMedian
function?

Also, in the call to the function,

CCur(acbDMedian("SoldPrice","Sales","Format$(SoldDate,'mmm yyyy') =
'" & Format$(Sales.SoldDate,'mmm yyyy') & "' And City = '" &
CityZipLookUp.CityUp & "' And [Style] = '" & Sales.Style & "'")) AS

Median

The criteria is optional on the receiving end, but you're always passing
criteria. This is ok, but you are passing 3 criteria, is there always a
value for each of those 3 options? Are all the fields listed in the criteria
available in the Domain ("Sales")? This would be SoldDate, City, and
[Style]. While it probably doesn't matter, have you tried putting brackets
around SoldDate and City?

Format$([SoldDate],'mmm yyyy') =
And [City] =

Also, if there are no records, Null is returned. CCur(Null) will cause an
error.

--
Wayne Morgan
Microsoft Access MVP

There are no error numbers, only #Error. The numbers after the error

in
the

list I posted are the field values for field Sales.

I commented out the error handler and still get the same thing.


The error handler in the function is set to return the error number

as
the

function's value if there is an error. Could this error number be
incorrect
for the field? You may want to change the error handler temporarily to
pop-up a message box if there is an error.

--
Wayne Morgan
Microsoft Access MVP


The results are in a query. And no, they are not null results. If

I
copy

the
underlying fields to Excel, I can get the median.

Here are the results for one city:

RES Annual Sales Mission Valley City Year Average Median Sales DOM
SP/LP
Mission Valley 1998 $184,215.08 $165,000 126 28.06 98.57%
Mission Valley 1999 $212,485.65 #Error 156 26.78 98.26%
Mission Valley 2000 $252,482.65 #Error 170 27.34 98.19%
Mission Valley 2001 $290,537.08 #Error 164 36.73 97.59%
Mission Valley 2002 $367,632.90 #Error 155 27.65 98.22%
Mission Valley 2003 $414,524.20 $561,000 176 34.34 98.11%



message
Where are you placing these results? Is the result being

returned
that

causes the #Error a Null result? If so, try the Nz function to change
the
Null to something else.

Nz(acbDMedian(Field, Domain, Criteria), 0)
or
Nz(acbDMedian(Field, Domain, Criteria), "")
or whatever value would be meaningful to where you are placing this.

--
Wayne Morgan
Microsoft Access MVP


The frustrating thing about this message is it is sporadic,

i.e.,

it
doesn't
happen on all records.

I'm using a vba module from an O'Reilly book to calculate medians.

The code for the module follows:
======================================
Public Function acbDMedian( _
ByVal strField As String, ByVal strDomain As String, _
Optional ByVal strCriteria As String) As Variant

' Purpose:
' To calculate the median value
' for a field in a table or query.
' In:
' strField: the field
' strDomain: the table or query
' strCriteria: an optional WHERE clause to
' apply to the table or query
' Out:
' Return value: the median, if successful;
' Otherwise, an Error value.

Dim db As DAO.Database
Dim rstDomain As DAO.Recordset
Dim strSQL As String
Dim varMedian As Variant
Dim intFieldType As Integer
Dim intRecords As Integer

Const acbcErrAppTypeError = 3169

On Error GoTo HandleErr

Set db = CurrentDb()

' Initialize return value
varMedian = Null

' Build SQL string for recordset
strSQL = "Select " & strField
strSQL = strSQL & " FROM " & strDomain

' Only use a WHERE clause if one is passed in
If Len(strCriteria) > 0 Then
strSQL = strSQL & " WHERE " & strCriteria
End If

strSQL = strSQL & " ORDER BY " & strField

Set rstDomain = db.OpenRecordset(strSQL, dbOpenSnapshot)

' Check the data type of the median field
intFieldType = rstDomain.Fields(strField).Type
Select Case intFieldType
Case dbByte, dbInteger, dbLong, dbCurrency, dbSingle, dbDouble,
dbDate
' Numeric field
If Not rstDomain.EOF Then
rstDomain.MoveLast
intRecords = rstDomain.RecordCount
' Start from the first record
rstDomain.MoveFirst

If (intRecords Mod 2) = 0 Then
' Even number of records
' No middle record, so move to the
' record right before the middle
rstDomain.Move ((intRecords \ 2) - 1)
varMedian = rstDomain.Fields(strField)
' Now move to the next record, the
' one right after the middle
rstDomain.MoveNext
' And average the two values
varMedian = (varMedian + rstDomain.Fields(strField))
/
2
' Make sure you return a date, even when
' averaging two dates
If intFieldType = dbDate And Not IsNull(varMedian)
Then
varMedian = CDate(varMedian)
End If
Else
' Odd number or records
' Move to the middle record and return its value
rstDomain.Move ((intRecords \ 2))
varMedian = rstDomain.Fields(strField)
End If
Else
' No records; return Null
varMedian = Null
End If
Case Else
' Non-numeric field; so raise an app error
Err.Raise acbcErrAppTypeError
End Select

acbDMedian = varMedian

ExitHere:
On Error Resume Next
rstDomain.Close
Set rstDomain = Nothing
Exit Function

HandleErr:
' Return an error value
acbDMedian = CVErr(Err.Number)
Resume ExitHere
End Function
========================================
I use the following SQL to produce my results:
========================================
SELECT DISTINCTROW Sales.Style, CityZipLookUp.CityUp AS City,
(Format$(Sales.SoldDate,'mmm yyyy')) AS SDate, Avg(Sales.SoldPrice)

AS
Average,

CCur(acbDMedian("SoldPrice","Sales","Format$(SoldDate,'mmm
yyyy')
=
'" & Format$(Sales.SoldDate,'mmm yyyy') & "' And City = '" &
CityZipLookUp.CityUp & "' And [Style] = '" & Sales.Style &

"'"))
AS

Median,
Count(*) AS Sales, Avg(Sales.DaysOnMarket) AS DOM,
[Average]/[ListPrice]
AS
[SP/LP], Avg(Sales.ListPrice) AS ListPrice
FROM Sales INNER JOIN CityZipLookUp ON Sales.ZipCode =
CityZipLookUp.ZipCode
GROUP BY Sales.Style, CityZipLookUp.CityUp, CityZipLookUp.CityUp,
Format$(Sales.SoldDate,'mmm yyyy'),
Year(Sales.SoldDate)*12+DatePart('m',Sales.SoldDate)-1
HAVING (((CityZipLookUp.CityUp)="Nestor"))
ORDER BY Sales.Style DESC ,
Year(Sales.SoldDate)*12+DatePart('m',Sales.SoldDate)-1 DESC;
===========================================
I've checked the data and it looks fine. Nothing pops out to say
"fix
me!"

The error message so far is consistent, it happens in the same
months.
I
could understand if the module ALWAYS returned an #error, but not
just
sometimes.

Anyone have a clue about this?

TIA

Rick