Comparing Dates

[Harlequin007]

Morning guys

I have a database that appends the current system date to a table.

This table in turn sends that information to a query which in turn is sent to another query that simply returns the max value (which should be the last record).

What I want to do is this:

Compare the last entry in the main (appended) table to the one previous to it in that table and if the last entry is not greater than it's previous entry - do something.

I'm using VBA but have access to VB6 if it would make life easier.

Many thanks in anticipation . . .

----------------------------
Cogito Ergo Sum

http://www.discworks.co.uk

----------------------------

 

[KenReay]

Hi

I a relational database table tehre is no concept of 'order' or 'sequence' hence there is no 'last' row or 'first' row.

the only concept of order or sequence comes when you have a query with an Order By clause, so based purely on your dates, if you Order By (asc) then the 'highest' will always come last, but is it the last one added?

You need to either compare the System date with the last row of the Ordered By query, BEFORE you add the new record, OR you need a sequnce number column in the table which will allow you to return the rows in the order in which they were appended.



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Harlequin007]

Thanks Ken

There was me thinking it was easy eh...!

Let me see if I get this right then:

I need to use a query (which I am) and compare the most recent entry in that query with the current system date. Cool.

Erm - How Mate...?

----------------------------
Cogito Ergo Sum

http://www.discworks.co.uk

----------------------------

 

[KenReay]

Hi

You clearly did not read what I said. If you have a query you must have a column to Order By which will return the rows in the Order they were added to the table.

Do you have such a column?



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Harlequin007]

Sorry Ken. I did read your post. But I may not have explained properly.

My thoughts are this:

If I sort the query's column, say "Ascending", then surely the last entry will not be the most recent - just the highest value e.g. 31/12/2003 when the customer may have rolled back the system date to 31/12/2002.

Sorry If I am not explaining this very clearly.

Please persevere.

----------------------------
Cogito Ergo Sum

http://www.discworks.co.uk

----------------------------

 

[KenReay]

Hi

Yes, I see

So why not just add your row to the table, run your query sorted Descending open the record set compare the first row to the system date if it is equal the user has been dicking about with the date?

If you have a reference to DAO, then

Dim Db as DAO.Database
Dim Rs as DAO.Recordset
'
Set Db = CurrentDb()
Set Rs = Db.OpenRecordset("SELECT * FROM tblDates;")
rs.AddNew
Rs!datSystem = Date()
rs.Update
Rs.Close
Set Rs = db.openRecordset("SELECT datSystem FROM tblDates ORDER BY datSystem DESC;")
if Rs.RecordCount > 0 Then
If Rs!datSystem <> Date() Then
msgBox "Naughty, naughty"
Application.Quit
End if
End if

But...

Why do you need to have a table with a great long lists of dates in it?, surely if all you are trying to do is check for fiddling with the system date, you just need a single row table, on entry to the application

Dim Db as DAO.Database
Dim rs as DAO.Recordset
'
set db = currentDb()
set Rs = db.OpenRecordset("SELECT * FROM tblDate;")
If Rs.Recordcount = 0 Then
with rs
.addnew
!datSystem = Date()
.Update
End With
Else
with rs
if !datSystem > date() Then
msgBox "Naughty, naughty"
Application.Quit
else
.Edit
!datSystem = DAte()
.Update
End If
End With
End if

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Harlequin007]

Ken

Thanks for the advice.

Maybe I am going about this the wrong way but here's why I am using tables:

Query 1 appends system date "(Now()" to Table 1
Query 2 returns ALL values from Table 1

Query 1 is tun on Application start-up. Ergo: If I use the code you suggest, it will simply check an incorrect data.

I am assuming (rather naively) that the database will be opened at least once using the correct system date (the first time). After that, users may cotton on to the idea that it uses the computer's integral clock and turn this back.

So, in essence I have a track record of when the database was executed and simply need to verify all these entries are sequential or:

Check the last entry against it's predeccessor.

e.g. check record 31 against record 30 (where record 32 is the last entry).

Like I said though - maybe I am over complicating the issue - but I am a newbie

If I use the code you suggest


----------------------------
Cogito Ergo Sum

http://www.discworks.co.uk

----------------------------