Hello,

I'm pulling info over from another database into a field. This normally
works, but for this one, I'm using a SUM command and something is not
working. I think I just have a bit of syntax that is off. I think it's
getting hung up in the select statement, or below that were I try to copy
the value. Can anybody help?

------------------------------------------------------------------------
Set oRS = Application.CreateObject("ADODB.Recordset")
oRS.Open "Select Sum(FI_BETR_NETTO) FROM BW_Auftr_Kopf WHERE ID = '" &
Trim(Item.UserProperties("CustomerNum").Value) & "'", _
oDBConn, adOpenKeyset
If Not oRS.BOF and Not oRS.EOF Then
Item.UserProperties("PendingAlfakOrders").Value = oRS("Sum(FI_BETR_NETTO)")
Else
MsgBox "No Database record found for this Customer ID value.6",
vbExclamation
End If
oRS.Close
Set oRS = Nothing
---------------------------


Thanks,
Joel

I think a field name for the result is missing:

Select Sum(...) As SumOfWhatEver ...

Then you can access that field like:

v = oRS!SumAsWhatEver

BTW: This Outlook programming group is not the best choice for asking SQL
related questions.

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Synchronize Color Categories & Ensure that Every Item Gets Categorized:
http://www.vboffice.net/product.html?id=2006063&cmd=detail&lang=en&pub=6

Am Wed, 31 Oct 2007 13:20:11 -0700 schrieb Joel Allen:

Hello,

I'm pulling info over from another database into a field. This normally
works, but for this one, I'm using a SUM command and something is not
working. I think I just have a bit of syntax that is off. I think it's
getting hung up in the select statement, or below that were I try to copy
the value. Can anybody help?

------------------------------------------------------------------------
Set oRS = Application.CreateObject("ADODB.Recordset")
oRS.Open "Select Sum(FI_BETR_NETTO) FROM BW_Auftr_Kopf WHERE ID = '" &
Trim(Item.UserProperties("CustomerNum").Value) & "'", _
oDBConn, adOpenKeyset
If Not oRS.BOF and Not oRS.EOF Then
Item.UserProperties("PendingAlfakOrders").Value =
oRS("Sum(FI_BETR_NETTO)")
Else
MsgBox "No Database record found for this Customer ID value.6",
vbExclamation
End If
oRS.Close
Set oRS = Nothing
---------------------------


Thanks,
Joel

Thanks. I got it to work. Here's the code:

Set oRS = Application.CreateObject("ADODB.Recordset")

oRS.Open "Select Sum(FI_BETR_NETTO) AS SumOfFI_BETR_NETTO FROM BW_Auftr_Kopf
WHERE AH_IDENT = '" & Trim(Item.UserProperties("CustomerNum").Value) & "'
and Status '80' and Status '-1'", _

oDBConn, adOpenKeyset

'msgbox oRS("SumOfFI_BETR_NETTO")

If Not oRS.BOF and Not oRS.EOF Then

Item.UserProperties("PendingAlfakOrders").Value = oRS("SumOfFI_BETR_NETTO")

Else

MsgBox "No Database record found for this Customer ID value.6",
vbExclamation

End If

oRS.Close

Set oRS = Nothing



"Michael Bauer [MVP - Outlook]" wrote in message
...


I think a field name for the result is missing:

Select Sum(...) As SumOfWhatEver ...

Then you can access that field like:

v = oRS!SumAsWhatEver

BTW: This Outlook programming group is not the best choice for asking SQL
related questions.

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Synchronize Color Categories & Ensure that Every Item Gets Categorized:

http://www.vboffice.net/product.html?id=2006063&cmd=detail&lang=en&pub=6

Am Wed, 31 Oct 2007 13:20:11 -0700 schrieb Joel Allen:

Hello,

I'm pulling info over from another database into a field. This normally
works, but for this one, I'm using a SUM command and something is not
working. I think I just have a bit of syntax that is off. I think it's
getting hung up in the select statement, or below that were I try to copy
the value. Can anybody help?

------------------------------------------------------------------------
Set oRS = Application.CreateObject("ADODB.Recordset")
oRS.Open "Select Sum(FI_BETR_NETTO) FROM BW_Auftr_Kopf WHERE ID = '" &
Trim(Item.UserProperties("CustomerNum").Value) & "'", _
oDBConn, adOpenKeyset
If Not oRS.BOF and Not oRS.EOF Then
Item.UserProperties("PendingAlfakOrders").Value =
oRS("Sum(FI_BETR_NETTO)")
Else
MsgBox "No Database record found for this Customer ID value.6",
vbExclamation
End If
oRS.Close
Set oRS = Nothing
---------------------------


Thanks,
Joel