By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,492 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Assign value to variable from query

P: n/a
How would I assign the MAX(value) in a tablecolumn to an Integer
variable, or put another way, assign any value from a table record to a
variable?

I am trying to achieve something like this:

Dim anInteger As Integer
Dim strSelect As String
strSelect = "SELECT MAX(tblTable.autonumberColumn) From tblTable;"

anInteger = CurrentDb.Execute(strSelect) 'or
anInteger = DoCmd.RunSQL(strSelect)

Neither DoCmd.RunSQL or CurrentDb.Execute lets me do this. I must be
missing something basic and essential since I get an easy understandable
error message stating: "compile error, expected function or variable"
and that I have not found any information where someone is trying to do
something similar.

I am not completely new to programming and databases, and I do belive I
remember doing something very similar with a window-application using
VB.NET and Access, but in Access 2003 VBA I am stuck.

I would be grateful if someone would point me in the right direction
(and sorry if this is a stupid question, I will not be surprised if it is)
Jan 23 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Dim AnIntegerMayNotBeBigEnough as Long
' JET's INTEGER = VBA'S Long
AnIntegerMayNotBeBigEnough = CurrentDb.OpenRecordset("SELECT Max(ID)
FROM Customers").Collect(0)

Jan 23 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.