help with date variable in vba

[Daniel M]

I am having problems getting my vba to work properly with dates in the
variables.

I currently do a query to retrieve a value (variable1) which is in date/time
format in the table.

I then do a variable2 = dateadd("m",-3,date) to subtract 3 months off todays
date.

Finally i try to do a if variable1 <= variable2 then something but the
calculation doesnt seem to be working properly.

in the debugger i see var1 is date and time and var2 is only date. if i try
to use the formatdatetime function it shows var1 as "6/23/08" and var 2 as
3/3/9 without the quotes. In either case the output is not correct.

Any ideas on what i'm doing wrong? i'm sure its something silly with
formating or storing of the data. thanks.

 

[Douglas J. Steele]

What's the actual VBA code you're using?

 

[Daniel M]

Private Sub cmdChk_Click()
On Error GoTo Err_cmdChk_Click

If Len(txtBulk) < 1 Then GoTo err_bulkempty

'clear table before starting lookup.
DoCmd.SetWarnings False
DoCmd.RunSQL ("DELETE * FROM tblTempBatteryLookup")
DoCmd.SetWarnings True

Dim i As Long
Dim snlookup As String
Dim D
Dim r

D = (Len(txtBulk))
r = D Mod 9
If (r > 0) Then GoTo err_bulkempty

For i = 1 To (Len(txtBulk) \ 9)
snlookup = Mid(txtBulk, ((i * 9) - 8), 9)

'Open the recordset
With CurrentDb.OpenRecordset("Select * From batterycheck;")

'Iterate the array imsnlookup
.FindFirst "t1.boardid = '" & snlookup & "'"

'If found, grab the field values
DoCmd.SetWarnings False
If .NoMatch Then DoCmd.RunSQL "insert into
tblTempBatteryLookup(SerialNumber,ReplacementStatus)values('" & snlookup &
"', 'New Unit')"
If Not .NoMatch Then
Variable1 = !dateinput

Judgementday = DateAdd("m", -3, Date)
If Variable1 <= Judgementday Then Variable2 = Judgementday - Variable1
MsgBox Variable2

Now the last 2 lines are test code and after this is the rest of the loop
cleanups.
thanks for the help!

 

[Douglas J. Steele]

No offense, but that's pretty ugly code! <g>

Private Sub cmdChk_Click()
On Error GoTo Err_cmdChk_Click

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Long
Dim snlookup As String
Dim D As Long
Dim r As Long
Dim Variable1 As Date
Dim Variable2 As Date
Dim JudgementDay As Date
Dim strSQL As String


If Len(txtBulk) > 0 Then

Set db = CurrentDb()

'clear table before starting lookup.
strSQL = "DELETE * FROM tblTempBatteryLookup"
db.Execute strSQL, dbFailOnError

D = (Len(txtBulk))
r = D Mod 9
If (r = 0) Then
For i = 1 To (Len(txtBulk) \ 9)
snlookup = Mid(txtBulk, ((i * 9) - 8), 9)

'Open the recordset
strSQL = "SELECT * FROM battercheck " & _
"WHERE t1.boardid = '" & snlookup & "'"
Set rs = db.OpenRecordset(strSQL)

'If found, grab the field values
If rs!RecordCount = 0 Then
strSQL = "insert into tblTempBatteryLookup(" & _
SerialNumber,ReplacementStatus) " & _
"values('" & snlookup & "', 'New Unit')"
db.Execute strSQL, dbFailOnError
Else
Variable1 = rs!dateinput
Judgementday = DateAdd("m", -3, Date)
If Variable1 <= Judgementday Then
Variable2 = Judgementday - Variable1
MsgBox Variable2
End If
End If

 

[Daniel M]

No offense taken! i'm know it's not the best but i make it work. i have been
cutting and pasting parts to get it to work as i learn access. i clean it up
as i go. I actually just got it working by doing some other things though.
and now it also runs much faster than it did. Thanks for the help. i'm
reading through your code now to make sure i understand it. I dont already
know how to write it but i can usually read it and figure out how to make it
useful