Solved

SELECT MAX only returns 0

Posted on 2006-10-31
20
245 Views
Last Modified: 2008-03-10
Someone please help me, I'm very frustrated right now.  I've tried every syntactical combination I can think of.  This SHOULD NOT be so difficult.  I'm reminded why I hate Access.

SELECT MAX(idx) FROM tblTips
SELECT MAX([idx]) FROM tblTips
SELECT MAX([idx]) FROM [tblTips]
SELECT MAX(idx) AS theMax FROM tblTips

SELECT DISTINCT MAX(idx) FROM tblTips
SELECT DISTINCT MAX([idx]) FROM tblTips
SELECT DISTINCT MAX(idx) AS theMax FROM tblTips

I cannot believe I have spent this much time on a simple select max function.  No matter what it returns 0.  It should be 200 something.  Any suggestions (aside from moving it into SQL Server)?
0
Comment
Question by:phuff34
  • 6
  • 6
  • 3
  • +3
20 Comments
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17843177
is idx a number or text?
0
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17843195
this is what I would write:

SELECT Max(tblTips.idx) AS MaxOfidx
FROM tblTips;
0
 

Author Comment

by:phuff34
ID: 17843221
It is a number and that last query also returns 0.  I think there might be something wrong with the db, I'm not sure.  It sems like all of these queries should work, as they would in any other sql database, but they are not working here.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17843237
try this to verify

SELECT COUNT(idx) FROM tblTips


what do u get?

Now try this

SELECT idx, COUNT(idx) FROM tblTips GROUP by idx

what do u get?


0
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17843239
you could upload the database to www.ee-stuff.com if you feel comfortable.  to do this you have to change the extension to .txt because it won't let you upload .mdb files...
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17843240
Try a compact/repair, see if it makes a difference
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17843249
yhwhlivesinme, that site seems to be down at the moment. Since this morning, every time I try to logon I get this

Fatal error: Call to undefined function: curl_init() in /home/eestuff/public_html/login.php on line 17
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 17843253
Is idx an Autonumber field?

If not, what type of number (Integer, Long, Single, Double) is it?

-Chuck
0
 
LVL 42

Expert Comment

by:dqmq
ID: 17843269
SELECT MAX([idx]) FROM tblTips

is proper.

What does this return:

SELECT MIN([idx]) FROM tblTips

0
 

Author Comment

by:phuff34
ID: 17843289
SELECT COUNT(idx) FROM tblTips returns 0 as well.  What the heck is going on?  Could I possibly bill M$ for my wasted time for their products working poorly (wow, how rich would I be if I could do that?)?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 65

Expert Comment

by:rockiroads
ID: 17843307
just check something
can u go into the vba window, and go into tools/references. Are there any listed as MISSING?
If so, uncheck them. click ok. then go back in and check them again
0
 

Author Comment

by:phuff34
ID: 17843308
Field size is Long Integer
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17843324
phuff34

have u tried the compact/repair yet?

there is also the decompile, mind u thats more suited for code rather than data (well I found anyway)

there is also the jet compact utility
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 17843333
When you look at the tblTips table, do you see different numbers in the idx field?

-Chuck
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17843334
and one thing

just double check tblTips

is there definitely data in there?

the count returned zero, that is why
0
 

Author Comment

by:phuff34
ID: 17843343
The code is actually from a C# asp application.  no references listed as missing.  no I will upload now
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 17843354
try

SELECT  Count(*)
FROM tblTips
0
 

Author Comment

by:phuff34
ID: 17843379
Also there is a lot of data in there.  idx does not allow duplicates.  There are items from 0 (the default value) to 231.  Each field has data.  idx is set as the primary key and the person who made the db did not set it as auto increment, which is annoying, but SELECT MAX should work just fine....in a real db at least.
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 17843410
Yes, it should work fine. It seems there is something else going on here. Have you tried the suggestiong to check for missing references and compact/repair?

While Access is definitely not an enterprise database, it works fine for desktop computer and small workgroup databases. The folks pitching in with advice have all had challenges with Access and have worked through them. They will find a solution for you.
0
 

Author Comment

by:phuff34
ID: 17843420
Thanks everyone.  I'm really embarrassed, but the syntax was not the problem.  I was under pressure to do a quick report and admin page in 20 minutes and that is why I over reacted and overlooked a dumb mistake in my code.  My code was cmd.ExecuteNonQuery() when it should have been cmd.ExecuteScalar().  It was returning the number of rows affected, which was 0.  oops!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now