Solved

SELECT MAX only returns 0

Posted on 2006-10-31
20
287 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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
 
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

690 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