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

SQL and VBE

P: 7
Hi! I need help with the following:

Even though it is quite easy to run SQL UPDATE queries in VBA using the DoCmd.RunSQL, I cannot find a way to run SELECT queries and use the result.

I have a table with customer names and orders. I want to store the latest orderID of a particular customer in a local variable.

In short, I would like to run this:

Private orderid as integer

orderid = {the result from the query below}
{SELECT Top 1 Orders.OrderID FROM Orders WHERE Order.customer = "Mr x" ORDER BY Orders.OrderID DESC;}

Thanks!
Feb 21 '07 #1
Share this Question
Share on Google+
2 Replies


MSeda
Expert 100+
P: 159
Since your only looking to return one value a domain aggregate will work.
Either accesses built in dmax or I like to use Allen Browne’s Elookup.
They should both yeild identical results in this case, Allen Browne explains the subtle differences at his website.

orderid = Dmax(“[OrderID]”, “[Orders]”, “[customer] = ‘Mr x’”)
or
orderid = Elookup(“[OrderID]”, “[Orders]”, “[customer] = ‘Mr x’”, "[OrderID] DESC"

hope this helps,
Megan
Feb 21 '07 #2

P: 7
It does help a lot! Thanks!!!
Feb 21 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.