Question Reading a value from database into an integer

[mauni]

Hi

I need to read the latest value of invoiceid from a table, into an integer called A1.


Table INVOICE:

invoiceid AS integer
invoicename AS string


I stored a query for the search. I can read/modify the query with this code:

Dim db As dao.Database
Dim qdef As dao.QueryDef
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT invoiceid from invoice;"
Set qdef = db.QueryDefs("search_latest_invoiceid")
qdef.sql = strSQL
qdef.Close
Set qdef = Nothing
Set db = Nothing
DoCmd.OpenQuery "search_latest_invoiceid", acViewNormal


This query returns all invoiceid values.

How can i read the latest invoiceid into an integer called A1 ?

 

[Dennisk]

Try

SELECT LAST(InvoiceID) FROM Invoice

 

[mauni]

Thanks DennisK, that code works. I was able to output the correct value also with:

SELECT TOP 1 invoiceid FROM invoice ORDER BY invoiceid DESC;


But how can i transfer the value into an integer? Now it only opens the result into an openquery window.

 

[DCrake]

You seem to be going about this the wrong way. Try.

Dim A1 As Integer

A1 = DLast("InvoiceID","Invoice")

 

[wiklendt]

totally untested aircode, but maybe it will work:

Dim myInvioceID As String
Dim myInt As Integer

myInvoiceID = "SELECT LAST(InvoiceID) FROM Invoice"
myInt = myInvoiceID

 

[wiklendt]

ah, DCrake, always one step ahead and with more elegant code LOL

 

[gemma-the-husky]

make it a long, not an integer possibly

an integer stops at 32000 odd, which may cause issues

 

[mauni]

DCrake your code works.

Thanks all

 

[DCrake]

Mauni,

I think we all knew that it was just a case of simplifying the code. And not only that but using the correct code for the job.

David

 

[wiklendt]

make it a long, not an integer possibly

an integer stops at 32000 odd, which may cause issues

ahhh! good point never even thought about that for an SQL statement (only really ever considered it in table design)

thanks gemma-the-husky

 

[gemma-the-husky]

i very rarely declare integers - pretty well always longs just in case