SELECT MAX only returns 0

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)?
phuff34Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try

SELECT  Count(*)
FROM tblTips
0
 
yhwhlivesinmeCommented:
is idx a number or text?
0
 
yhwhlivesinmeCommented:
this is what I would write:

SELECT Max(tblTips.idx) AS MaxOfidx
FROM tblTips;
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
phuff34Author Commented:
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
 
rockiroadsCommented:
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
 
yhwhlivesinmeCommented:
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
 
rockiroadsCommented:
Try a compact/repair, see if it makes a difference
0
 
rockiroadsCommented:
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
 
Chuck WoodCommented:
Is idx an Autonumber field?

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

-Chuck
0
 
dqmqCommented:
SELECT MAX([idx]) FROM tblTips

is proper.

What does this return:

SELECT MIN([idx]) FROM tblTips

0
 
phuff34Author Commented:
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
 
rockiroadsCommented:
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
 
phuff34Author Commented:
Field size is Long Integer
0
 
rockiroadsCommented:
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
 
Chuck WoodCommented:
When you look at the tblTips table, do you see different numbers in the idx field?

-Chuck
0
 
rockiroadsCommented:
and one thing

just double check tblTips

is there definitely data in there?

the count returned zero, that is why
0
 
phuff34Author Commented:
The code is actually from a C# asp application.  no references listed as missing.  no I will upload now
0
 
phuff34Author Commented:
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
 
Chuck WoodCommented:
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
 
phuff34Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.