Dates don't compare right...

[Twanne]

I've got this date I queryd from a table, and I want to compare this date to the same field as where it came from but it keeps returning an empty recordset. Can anyone help me.

    Set rst2 = db.OpenRecordset("SELECT DISTINCT Datum FROM patParameters WHERE eadnr =" & Form_Testinvoer.eadnr & ";")
 

This is the query where I get the date first.

Do Until rst2.EOF
        dag = DateValue(rst2.Fields(0))
        If Not (rst2.EOF) Then
            Set rst = db.OpenRecordset("SELECT p.Datum, p.Parameter, p.Waarde FROM PatParameters p WHERE p.eadnr=" & Form_Testinvoer.eadnr & " AND p.Datum=#" & dag & "#;")
            Do Until rst.EOF
                Debug.Print rst.Fields(0) & " " & rst.Fields(1) & " " & rst.Fields(2)
                rst.MoveNext
            Loop
            rst2.MoveNext
        End If
    Loop

This is where I try to compare it.

I'm using access 2003.

Kind regards

Twanne

[Rabbit]

Replace Form_Testinvoer with Me

[Twanne]

Thanx

I'll try it out and hopfully I'll be on my way then.

Greetz

[Twanne]

Hi again,

Apparently my problem wasn't an error in comparing dates. I'm not sure why but when there is only one record to select from my table, and I try to read it, an error emerges saying there ain't no record. From the moment there are more the problem disapears.

Greetz

[missinglinq]

I think the "why" is that with only 1 record, you are at the EOF (end of file) immediately! I haven't used looping in a longggggggggg time, but I think if you changed your looping syntax from

Do Until rst2.EOF...Loop

to

Do Loop...Until rst2.EOF

it would run with only a single record.

Linq ;0)>

[Twanne]

Thanx

I figured it out the way you said and it's doing something now :)

Normally I'll be on my way progging like hell :D

Greetz

[Twanne]

And again we got a problem

I've got to check a date in a table, and I select it from the same table.

Now when I check with most of the dates it gives a good result, but when access checks the date it does this in mm/dd/yyyy format if it can.

Now all my dates are in dd/mm/yyyy format so for some it gives a weird result.

example:
When I have 14/12/2006 it check like it should because there is no other option to check it. When I have 11/01/2007, access cheks this date as 01/11/2007.

Is there a way to get around this?

I use this function to make sure it is in dateformat.
dag = DateValue(rst.Fields(0).Value)

Greetz