Brilliant - Thanks Warren - works a treat.
One additional question.
If I pass a partnumber that is not found by the SQL I get a "Invalid use of
Null" error when the command
adorecordset.fields("wire_length").value
is executed - I.E I assume wire_length is created but doesn't contain any
fields (or at least they are Null)?
Is there a correct way to check for this (Null) without resorting to an on
error goto (or similar)?
Thanks again
Julian
-----Original Message-----
From: warren ang [mailto:[EMAIL PROTECTED]
Sent: 24 May 2004 10:07
To: [EMAIL PROTECTED]
Subject: RE: [vbhelp] An easy database question
you could use the below code to access sum(length):
adorecordset.fields("wire_length").value
-----Original Message-----
From: Julian Holt [mailto:[EMAIL PROTECTED]
Sent: Monday, May 24, 2004 4:43 PM
To: [EMAIL PROTECTED]
Subject: [vbhelp] An easy database question
(Sorry for the cross posting)
Guys
I am getting into SQL (wow powerful or what?)
Anyway I have a really simple question (and embarrassing one).
I am opening the DB and passing in a SQL query that sums a few fields and
returns the value in a 'temporary' table called wire_length
eg SELECT SUM(Length) AS wire_length FROM LOOP WHERE PartNo = '610972'
I have checked this query in Access and it works ok. I now want to put it
into VB
Here is the code
Set adoConnection = New ADODB.Connection
Set adoRecordset = New ADODB.Recordset
strTemp = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
strLoopName
& ";Persist Security Info=False"
adoConnection.Open strTemp
strSQL = "SELECT SUM(Length) AS wire_length FROM LOOP WHERE PartNo =
'"
& strPartNo & "'"
adoRecordset.Open strSQL, adoConnection
' adoRecordset.Close
' adoConnection.Close
Set adoConnection = Nothing
Set adoRecordset = Nothing
Question
How do I access the summed value in VB???? To date I have only used SQL to
add and delete items in the DB so this is new stuff for me.
I.E I want to add a line like sngSum = ???????????
Told you it was simple :-)
Oh and another one,,,
Why is it that I have to comment out the lines adoRecordset.Close and
adoConnection.Close when doing a query?
Does a query automatically close the DB???
Mucho thanks
Julian Holt
[Non-text portions of this message have been removed]
'// =======================================================
Rules : http://ReliableAnswers.com/List/Rules.asp
Home : http://groups.yahoo.com/group/vbHelp/
=======================================================
Post : [EMAIL PROTECTED]
Join : [EMAIL PROTECTED]
Leave : [EMAIL PROTECTED]
'// =======================================================
Yahoo! Groups Links
############################################################################
#########
This e-mail message has been scanned for Viruses and Content and cleared
by NetIQ MailMarshal
############################################################################
#########
'// =======================================================
Rules : http://ReliableAnswers.com/List/Rules.asp
Home : http://groups.yahoo.com/group/vbHelp/
=======================================================
Post : [EMAIL PROTECTED]
Join : [EMAIL PROTECTED]
Leave : [EMAIL PROTECTED]
'// =======================================================
Yahoo! Groups Sponsor
ADVERTISEMENT
----------------------------------------------------------------------------
--
Yahoo! Groups Links
a.. To visit your group on the web, go to:
http://groups.yahoo.com/group/vbhelp/
b.. To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
[Non-text portions of this message have been removed]
------------------------ Yahoo! Groups Sponsor ---------------------~-->
Yahoo! Domains - Claim yours for only $14.70
http://us.click.yahoo.com/Z1wmxD/DREIAA/yQLSAA/k7folB/TM
---------------------------------------------------------------------~->
'// =======================================================
Rules : http://ReliableAnswers.com/List/Rules.asp
Home : http://groups.yahoo.com/group/vbHelp/
=======================================================
Post : [EMAIL PROTECTED]
Join : [EMAIL PROTECTED]
Leave : [EMAIL PROTECTED]
'// =======================================================
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/vbhelp/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
