Compare dates

[anil]

hi all
I have 2 tables tblnewone and tbllog.I am changing data every week in
tblnewone and keeping the log in tbllog.What I am trying to do is to
compare the dates in two table and run the procedure.I am Appending the
table tbllog and ADate is in ascending order.Some times it works well
but some times do not.Can u please tell me the error,where I am
wrong.Here I am only trying to compare for last day,but actually I need
to Compare date in tbllog for last week or for fixed one day say monday
or friday.Can u help me to change that.
thanks
anil

Private Sub Command0_Click()
Dim str As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblLog")
rst.MoveLast
If DMax("ADate", "tblLog") <> DMax("Bdate", "tblnewone") Then
Call newtblnsites 'call another procedure
str = "INSERT INTO tblLog ( SiteCode, [ADate],SiteAddress)"
& _
"SELECT tblnewone.SiteCode, Now() AS
[ADate],SiteAddress " & _
"FROM tblnewone;"
DoCmd.RunSQL str
Else
MsgBox ("You already have generated the table")
Exit Sub
End If
Set db = Nothing
Set rst = Nothing
End Sub

 

[Douglas J Steele]

Sorry, but you have no way of knowing that "ADate is in ascending order".
You can never make any assumptions about the order in which records are
returned in a recordset unless you have an ORDER BY clause in your SQL.
Relational Database theory doesn't worry about physical ordering of records
in tables: tables are large "sacks of data", with the records existing
wherever they fit.

That having been said, I have no idea what your code is supposed to be
doing. You open a recordset, but don't apear to do anything with that
recordset.

 

[anil]

hi douglas
I am trying to add the data from tblnewone(which in generated using
'sub' newtblnsites)into tbllog.
I have to run the 'sub' every week and the results in the tblnewone are
modified or changed means generate new results every week and the old
results are deleted.
I just want to save those deleted results in the log table.
Also I want to make sure that the data is not generated twice in the
tblnewone.So I am trying to compare the date of tblnewone(generated
now) and the date of tbllog(generated last time/day/week).
I tried using order by SiteCode but in that case date becomes different
and it runs.When I do order by 'ADate' I get different SIteCode at the
last as output and the code asks for the parameter value for ADate.
Please help me in 2 problems( even in different way)
1)Compare the date of 2 tables and run 'sub' only when Dates are not
same.
2) append the tbllog from the last.See if I start on 1st day of month
then it will work fine till 2nd day of month.But if I run on 2nd day
twice Cursor starts from field one and program/sub runs every time.If I
try to move the cursor to last position then results are different
every time.Some times it works fine sometime it do not work means
sometime 'sub' run sometime it gives message in else statement.
thanks
anil