Date is less than Date in recordset

[snoopy92211]

Hi. I have some code that I'm using to update a table in a recordset.


I have this date function.

For j = 1 To 12

Set rst = DB.openrecordset("obligations", dbOpenTable, dbAppendOnly)
    
    rst.AddNew
       
    If j > 1 Then
    rst.Fields("DueDate") = CVDate(Str(j) & "/" & Format(Day(DueDate), "00") & "/" & Format(Year(DueDate), "0000"))
    Else
    rst.Fields("DueDate") = CVDate(Str(j) & "/" & Format(Day(DueDate), "00") & "/" & Format(Year(DueDate) + 1, "0000"))
    End If
[b]    If rst.Fields("Duedate") < origdate Then
    rst.Fields("ObligationStatus") = "Inactive"
    Else
    rst.Fields("ObligationStatus") = Me.obgstat
    End If [/b]

If the user selects 3/1/06 as a due date, the code creates 11 more records with the same due DAY, different months/years, always starting with february(j) = 1.

That part works fine.

The bolded section is what I'm having a problem with.

If the recorset date( rst.duedate) is less than the original due date (the date the user inputs into the form), I want the obligation field in the recordset to be 'inactive'

Apparently, the less than command doesn't entirely work. I've tried this, and it makes (seemingly) random dates 'inactive.

If user types 3/1/06, 11 records will be created, from 2/1/06 - 1/1/07. What I want to see is the 2/1/06 record marked as inactive, because it's less than the due date.

Make sense? HELP!

Hope so!
Thanks!

 

[jadams0173]

I think you should format the dates you are comparing before you compare them to make sure you are comparing apples to apples.

If [b]Format(rst.Fields("Duedate"),"") < Format(origdate,"")[/b] Then
    rst.Fields("ObligationStatus") = "Inactive"
    Else
    rst.Fields("ObligationStatus") = Me.obgstat

You may want to look up the arguments for the format function. This should be a start though.

 

[snoopy92211]

Jadams0173,

thanks!

I forgot to mention that I've tried the 'Format' function, and for the 2/6/06 date, the obligations that were marked inactive were the following dates: 10/6/06 - 1/6/07.

So this function didn't seem to work...am i missing something that should go within the format function?

 

[PHV]

Use the following format: "yyyy-mm-dd"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[snoopy92211]

Thanks! one more question.


This is really simple,but for the life of me, i can't figure it out.


I want to create a query that says like = *.

This is what i have in my sql. and it errs out everytime!!

dim fn as string

    If Forms!obligationselector.Check157 = -1 Then
    fn = "Like """ & Forms!obligationselector.fname & """"
    Else
    fn = "Like " * ""

select * from obligations where obligations.fornmame & fn & " & ";"

so simple, i know. Please help!

 

[PHV]

Either:
If Forms!obligationselector.Check157 = -1 Then
[tt]fn = "Like """ & Forms!obligationselector.fname & """"[/tt]
Else
[tt]fn = "Like ""*"""[/tt]
End If
Or (simpler I think):
If Forms!obligationselector.Check157 = -1 Then
[tt]fn = "Like '" & Forms!obligationselector.fname & "'"[/tt]
Else
[tt]fn = "Like "'*'"[/tt]
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[snoopy92211]

I've tried both of these options as well,and the module just errs out.


For the second option, this section '*' is commented out.

 

[jadams0173]

Error message?

Also in the immediate window (ctl + g) print out you sql string and you may see the problem.

 

[PHV]

OOps, sorry:
[tt]fn = "Like '*'"[/tt]