Return value from a sql statement

  • Thread starter Thread starter SAC
  • Start date Start date
S

SAC

I'm trying to find an invoice number based on the total amount of an
invoice, but I don't know how to retunr the invoice number into a variable.

Here's what I have:

Dim intInvNo As String
intInvNo = "SELECT tblInvoiceL.InvoiceNO " _
& "FROM tblInvoiceL " _ 'Line items
& "GROUP BY tblInvoiceL.InvoiceNO " _
& "HAVING (((Sum([Qty]*[Rate]))=189));"

MsgBox (intInvNo)

What should I do to make this work?

Thanks for your help.
 
hi,
I'm trying to find an invoice number based on the total amount of an
invoice, but I don't know how to retunr the invoice number into a variable.
Dim intInvNo As String
intInvNo = "SELECT tblInvoiceL.InvoiceNO " _
& "FROM tblInvoiceL " _ 'Line items
& "GROUP BY tblInvoiceL.InvoiceNO " _
& "HAVING (((Sum([Qty]*[Rate]))=189));"

MsgBox (intInvNo)
Click to expand...

Dim rs As DAO.Recordset

Dim yourSQL As String

yourSQL = "SELECT tblInvoiceL.InvoiceNO " & _
"FROM tblInvoiceL " & _
"GROUP BY tblInvoiceL.InvoiceNO " & _
"HAVING (((Sum([Qty]*[Rate]))=189));"
Set rs = CurrentDb.OpenRecordset(yourSQL, dbOpenSnapshot)
If Not rs.BOF And Not rs.EOF Then
MsgBox rs![InvoiceNo]
End If
rs.Close
Set rs = Nothing

mfG
--> stefan <--
 
Try:

dim rstData as dao.RecordSet
dim strSql as string
dim strInvoiceNum as string

strSql = "SELECT tblInvoiceL.InvoiceNO " _
& "FROM tblInvoiceL " _ 'Line items
& "GROUP BY tblInvoiceL.InvoiceNO " _
& "HAVING (((Sum([Qty]*[Rate]))=189));"

debug.print strSql
set rstData = currentdb.OpenreocrdSet(strSql)
if rstData.RecordCount > 0 then
strInvocieNum = rstData!invoiceNO
end if

I not sure if that sql you have posted is correct. You will see the sql
generated in the debug window via the :

debug.print strSql

And, then try pasting the debug windows sql into the query builder to see if
the sql is legit..

Once it works, you can remove the debug.print line above...it only for
testing.

note the above is "air" code, but it the general approach.
 
I think you and Stefan are missing an important point. It would not be far
fetched so assume there could be multiple invoices with the same amount.

I would suggest a datasheet form with a query that will return all rows
where the invoice amount matches and let the user select an invoice.
 
Great! Didn't think about using a recordset.

Stefan Hoffmann said:
hi,
I'm trying to find an invoice number based on the total amount of an
invoice, but I don't know how to retunr the invoice number into a
variable.
Dim intInvNo As String
intInvNo = "SELECT tblInvoiceL.InvoiceNO " _
& "FROM tblInvoiceL " _ 'Line items
& "GROUP BY tblInvoiceL.InvoiceNO " _
& "HAVING (((Sum([Qty]*[Rate]))=189));"

MsgBox (intInvNo)
Click to expand...

Dim rs As DAO.Recordset

Dim yourSQL As String

yourSQL = "SELECT tblInvoiceL.InvoiceNO " & _
"FROM tblInvoiceL " & _
"GROUP BY tblInvoiceL.InvoiceNO " & _
"HAVING (((Sum([Qty]*[Rate]))=189));"
Set rs = CurrentDb.OpenRecordset(yourSQL, dbOpenSnapshot)
If Not rs.BOF And Not rs.EOF Then
MsgBox rs![InvoiceNo]
End If
rs.Close
Set rs = Nothing

mfG
--> stefan <--
Click to expand...
 
This is true! Thanks!!
Klatuu said:
I think you and Stefan are missing an important point. It would not be far
fetched so assume there could be multiple invoices with the same amount.

I would suggest a datasheet form with a query that will return all rows
where the invoice amount matches and let the user select an invoice.
--
Dave Hargis, Microsoft Access MVP


Albert D. Kallal said:
Try:

dim rstData as dao.RecordSet
dim strSql as string
dim strInvoiceNum as string

strSql = "SELECT tblInvoiceL.InvoiceNO " _
& "FROM tblInvoiceL " _ 'Line items
& "GROUP BY tblInvoiceL.InvoiceNO " _
& "HAVING (((Sum([Qty]*[Rate]))=189));"

debug.print strSql
set rstData = currentdb.OpenreocrdSet(strSql)
if rstData.RecordCount > 0 then
strInvocieNum = rstData!invoiceNO
end if

I not sure if that sql you have posted is correct. You will see the sql
generated in the debug window via the :

debug.print strSql

And, then try pasting the debug windows sql into the query builder to see
if
the sql is legit..

Once it works, you can remove the debug.print line above...it only for
testing.

note the above is "air" code, but it the general approach.
Click to expand...
Click to expand...
 
Klatuu said:
I think you and Stefan are missing an important point. It would not be far
fetched so assume there could be multiple invoices with the same amount.

I would suggest a datasheet form with a query that will return all rows
where the invoice amount matches and let the user select an invoice.
Click to expand...

Much agree, and it likey less code suffing that sql into a contiues form, or
listbox.....
 
Back
Top