Differences between 2000 and 2003

[Joe Sutphin]

The following code works perfectly in Access 2000. However, it fails with
#Name? being displayed instead of the value for sExtPrice. The code is
contained in the Detail_Format section of the Report module. Does anyone
know why this occurs and how to fix it? TIA.

Joe
--
Public sExtPrice As String

Const QUOTE = """"

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim PartsSQL As String
Dim con As ADODB.Connection
Dim rsParts As New ADODB.Recordset

Set con = Application.CurrentProject.Connection

PartsSQL = "SELECT Quotes.QuoteNumber, BOMs.PartNumber, Sum(BOMs.Count) AS
SumOfCount, Sum(BOMs.Quantity) AS SumOfQuantity, Parts.Description,
Parts.CatalogSectionNumber, Parts.ListPrice, BOMs.AttributeType, Parts.UOM "
& _
"FROM Parts RIGHT JOIN (Quotes LEFT JOIN BOMs ON
Quotes.QuoteNumber = BOMs.QuoteNumber) ON Parts.PartNumber = BOMs.PartNumber
" & _
"GROUP BY Quotes.QuoteNumber, BOMs.PartNumber,
Parts.Description, Parts.CatalogSectionNumber, Parts.ListPrice,
BOMs.AttributeType, Parts.UOM " & _
"HAVING Quotes.QuoteNumber=" & QUOTE &
[Report_BillOfMaterial].PartNumber & QUOTE & " And Sum(BOMs.Count) > 0 And
BOMs.AttributeType=0 Or Sum(BOMs.Quantity) > 0 " & _
"ORDER BY Parts.CatalogSectionNumber;"

'open the parts recordset
rsParts.Open PartsSQL, con, adOpenKeyset

If rsParts.State = adStateOpen Then
Do
If [SumOfCount] > 0 And [SumOfQuantity] = 0 Then
sExtPrice = Format([SumOfCount] * [ListPrice], "$0.00")

Else
If [SumOfQuantity] > 0 Then
If [UOM] <> 1 Then
sExtPrice = Format(([ListPrice] / [UOM]) * [SumOfQuantity],
"$0.00")

Else
sExtPrice = Format([ListPrice] * [SumOfQuantity], "$0.00")
End If
End If
End If

rsParts.MoveNext
Loop Until rsParts.EOF
End If
End Sub

 

[Jeff Boyce]

Joe

Already answered in another newsgroup. Kindly don't post the same question
to multiple newsgroups.