SQL: LAST() doesn't seem to return the right value

[drewpee]

I have a test table where I'm using the Last() function but it seems to not work, it always return the 2nd record when i have 10 records..

SELECT LAST(Date_Received) FROM serviceRS;

returns the 2nd record

SELECT FIRST(Date_Received) FROM serviceRS;

returns the 3rd record

X_X

[vb5prgrmr]

If you did an order by Date_Recieved, I'm betting that the 3rd record would be first and the second record would be last as that would be the order of the dates... Which means your functions are returning the correct value/records... Which means, if you want the last record, you should do your last on the auto number field...

Good Luck

[drewpee]

CCIF_No	        Date_Recieved
00000001	1/1/2001
00000002	2/4/2010
00000003	2/5/2010
00000004	2/10/2010
00000005	2/10/2010
00000006	2/10/2010
00000007	2/15/2010
00000008	2/15/2010
00000009	2/15/2010

This is the table I'm using. I can't see how the last() and first() should return the 2nd and 3rd record.

[vb5prgrmr]

Hmmm... I see what you mean... Is that field actually defined as a date field or a text field?

Good Luck

[drewpee]

It's a date field with a short date format.

[vb5prgrmr]

Well, since that is not working... Try...

strSQL = "SELECT TOP 1 * FROM tablename ORDER BY datefield DESC"

or ASC for first...

Good Luck

[drewpee]

TOP 1 still gives me the 3rd record. WTH is wrong with this DB? /facedesk

[vb5prgrmr]

What does an order by give you???

[drewpee]

never mind this. I'll just find a walkaround. thanks. =)