Issues with SQL

[gblack]

All I want to do is run a simple SQL code on an onclick event procedure. I want the SQL to get the max value in a field and then I want it to appear in my message box. Sounds easy but I am stupid, so if anyone could look at my code and tell me what i am doing wrong, it would be a great help!

Here is the part of my code I am having issues with:

Dim strMaxNum As String
Dim dbs As Database
Dim strSQL As String
Dim rst As Recordset

Set dbs = CurrentDb
strSQL = "SELECT Max([FMS Cycle Raw Data].file_suffix) AS MaxNum FROM [FMS Cycle Raw Data];"
Set rst = dbs.OpenRecordset(strSQL)
strMaxNum = rst(MaxNum)
MsgBox strMaxNum

PS. Since this site is very slow, feel free to e-mail me directly @: gblackiv@hotmail.com

Thanks,
Gary

 

[dan-cat]

All I want to do is run a simple SQL code on an onclick event procedure. I want the SQL to get the max value in a field and then I want it to appear in my message box. Sounds easy but I am stupid, so if anyone could look at my code and tell me what i am doing wrong, it would be a great help!

Here is the part of my code I am having issues with:

Dim strMaxNum As String
Dim dbs As DAO.Database
Dim strSQL As String
Dim rst As DAO.Recordset

Set dbs = CurrentDb
strSQL = "SELECT Max(file_suffix) AS MaxNum FROM [FMS Cycle Raw Data];"
Set rst = dbs.OpenRecordset(strSQL)
strMaxNum = rst("MaxNum")
MsgBox strMaxNum

PS. Since this site is very slow, feel free to e-mail me directly @: gblackiv@hotmail.com

Thanks,
Gary

I've displayed the changes in red.

What do you mean that this site is slow?
Is it not providing free consultation fast enough for you?

I think the idea of this forum is to provide answers for all. NOT privately e-mail individuals with solutions to their problems

 

[ReAn]

From time to time the connectivity of the site is poor but that's what you get for $0.00/mo. Im sure if everyone were paying the owners $9.99/mo. They'd have dual OC148 lines for the server as well as hired and trained professional consultants who sit there all day in different timezones answerings questions.

Disclaimer:
(Note: I said HIRED professionals, I in no way meant that the people here arent professionals, as im sure there are a handfull or two.)

 

[KenHigg]

Will dmax() work for this?

ken

 

[WayneRyan]

gblack,

Is it really the max of [file_suffix]?

strMaxNum = "DMax("[file_suffix]", "[FMS Cycle Raw Data]")

Wayne

 

[gblack]

Ok...

Thanks Dan-Cat, that worked. It was the the quotes that I was missing that was the main prob (it works without the DAO.).

NOTE: I Didn't mean to get people in a tif about the speed of the sight...it was just taking a while for me to load it yesterday.

I wasn't, nor am I now, trying to complain about it. I have often e-mailed people directly and had a bit of back and forth before some of their problems were solved, then posted the solution on the site...

Sometimes I even post solutions to my own posts just in case someone runs across the same problem...Honestly I haven't used this sight in over a year because I have been out of work, which is why my code is so rusty and I forget things like quotes I also try to post solutions when I can, while I am using the site so as pay for my "Free consultaion". So cut me some slack please as I try to ease back into the wonderful world of MS Access development

And BTW: no its just Max, Not DMax.

Thanks All...I'm sure you'll be seeing my posts again soon

Later
G

 

[KenHigg]

And BTW: no its just Max, Not DMax.

= Glad your DAO worked but I suspect you missed the point...


???
ken

 

[Mile-O]

Sight is what your eyes provide.
Site is what you visit on the web.

 

[gblack]

Man!

You people are a tough crowd...

The code that was presented to me on this "SITE" pertaining to DMax did not work when I tried it. So if I am missing the point...assume I am an idiot that doesn't know the difference between stuff like: "site" & "sight" or Max() & Dmax() and elaborate a bit...

Cya,
Gary

 

[KenHigg]

Sorry -

It's just that Dmax() is a built in access function that will do all that DAO stuff you did, but it does it with the one line, like the one Wayne presented.

???
ken

 

[dan-cat]

You people are a tough crowd...

The code that was presented to me on this "SITE" pertaining to DMax did not work when I tried it. So if I am missing the point...assume I am an idiot that doesn't know the difference between stuff like: "site" & "sight" or Max() & Dmax() and elaborate a bit...

Cya,
Gary

Ok,

I think wrong ends of the stick all round here.
It just sounded a little like biting the hand that feeds you but I must have misunderstood.

I'm not a great fan of Domain Aggregates - the performance isn't as good as using recordsets - and I think it far more useful/versatile to open up a recordset and use/manipulate values within it.

I would personally stick with how you had it.