Redundancy Preventative

  • Thread starter charleswoods via AccessMonster.com
  • Start date
C

charleswoods via AccessMonster.com

Hello and Thanks in Advance!
I am new to VBA and need some advice. I am building a datawarehouse and I
have created a Macro that I can run periodically to append my summarized
records to. If I don't remember the last date, I could easily append the same
record again & again. I want to automate this process so that every night at
2am my program kicks off and loads yesterday's records.
I have started by trying to build a check based upon my system date. Below is
my beginning:

Public Sub LastRecordCheck()
Dim db As DAO.Database
Dim rstd As DAO.Recordset
Dim lastdate As Date
Set db = CurrentDb
Set rstd = db.OpenRecordset("SELECT Format(tblData_10Patient.ARRVDATE,
'mm/dd/yyyy') AS RecordExists "& _
"FROM tblData_10Patient GROUP BY Format(tblData_10Patient.ARRVDATE,
'mm/dd/yyyy') "& _
"HAVING (((Format(tblData_10Patient.ARRVDATE,'mm/dd/yyyy'))=Format(Now(),
'mm/dd/yyyy')));"
lastdate = rstd.Fields("RecordExists")
If lastdate Is Null Then End
Else
MsgBox "The date entered, " & lastdate & " already exists in the records."
MsgBox "Please enter a different date."
rstd.Close

End

I keep getting the following error : Compile Error : Expected: List separator
or )
Any Advice?
Charles Woods
 
A

Allen Browne

You are using Format() as a way to lose the time component in the field, to
see if today's date exists?

Format() generates a string. DateValue() would be better:
SELECT TOP 1 DateValue(tblData_10Patient.ARRVDATE)
AS RecordExists
FROM tblData_10Patient
WHERE DateValue(tblData_10Patient.ARRVDATE) = Date();

If you have many records, this would be faster still (assuming you have an
index on the ARRVDATE field):
SELECT First(tblData_10Patient.ARRVDATE) AS ARecord
FROM tblData_10Patient
WHERE tblData_10Patient.ARRVDATE >= Date()
AND tblData_10Patient.ARRVDATE < Date() + 1;

DLookup() might do away with the need for the function all together:
Dim strWhere As String
strWhere = "(ARRVDATE >= Date()) AND (ARRVDATE < Date() + 1)"
If Not IsNull(DLookup("ARRVDATE", "tblData_10Patient", strWhere))) Then
MsgBox "Date exists"
End If

If DLookup() is new, see:
http://allenbrowne.com/casu-07.html
 
C

charleswoods via AccessMonster.com

Thank you! Works perfectly...
and Yes I was trying to remove the time element of the date. Instead I have
changed my mind and would like to find the last date/time and add 1 minute
for my beginning append record value.
To do this, I continue in the Sub by finding the last date and trying to
assign 1 minute. The problem is that no matter how I try to assign it, I keep
getting a Run_Time 13 data mismatch error. This always occurs at the DateAdd
function. How can I fix this? Can I force the Variant "LastDateUsed" to be
type 7? Must I format the Variant in the DateAdd? I tried using DateValue()
but it doesn't seem to change the result.


SetVar:
'Dim rstd As DAO.Recordset
'Set db = CurrentDb
Set rstd = db.OpenRecordset("SELECT Max(tblData_10Patient.ARRVDATE) AS
ARecord" & _
" FROM tblData_10Patient;")
Set LastDateUsed = DateValue(rstd.Fields("ARecord"))
Set LastDateUsed = DateAdd("n", 1, LastDateUsed)
Set NextDay = LastDateUsed
Set NextDay = DateAdd("d", 2, NextDay)
MsgBox " First Date= " & LastDateUsed & "Last Date= " & NextDay
 
C

charleswoods via AccessMonster.com

Figured this one out...it doesn't like the SET command...
 
A

Allen Browne

charleswoods via AccessMonster.com said:
Figured this one out...it doesn't like the SET command...
Click to expand...

Great. You're there.

FYI you use SET with an object, but not with ordinary number/string
variables, e.g.
Dim frm As Form
Set frm = Forms!Form1
but:
Dim i As Integer
i = 5

There's an antique syntax that uses Let with ordinary variables:
Let i = 5
but I don't recommend you do that. Let has another use (class properties),
and you don't want to confuse VBA or anyone reading your code.
 
Top