checking date when recordcount > 0

[JeroenMioch]

Im struggeling with the following :

Dim strSQL3 As String
strSQL3 = "SELECT DISTINCT Pnummer FROM tblkrpDeelnemersTotaal " _
    & "WHERE Pnummer = '" & Me.Pnummer & "'"
With CurrentDb.OpenRecordset(strSQL3)
    If .RecordCount > 0 Then

After this piece of code i want to check the system date against a field called Miva_tot in the table tblkrpDeelnemersTotaal.

If the field is left empty or the date value inserted in that field is lower then todays date then

me.slvMIVA = true
else
me.slvMIVA = false

The point of this is to check if a disabled person's personell (pnummer) number is in the table, and the date (Miva_tot) is still valid.

[pbaldy]

What specifically are you struggling with? If you mean to check the recordset, you'd have to include that field.

[JeroenMioch]

Yeah well i tryed something like :

If .recordcount > 0 and date <= dlookup("miva_tot", "tblMindeValide", "pnummer = '" & Me.Pnummer & "'") or isNull (dlookup("miva_tot", "tblMindeValide", "pnummer = '" & Me.Pnummer & "'")) then

But that doesnt seem to work.
In my first example ive got the wrong tablename btw.

[pbaldy]

I'd break it down and see what each component is returning. Set a breakpoint and in debug mode you should be able to use the Immediate window or hover over variables to see their contents.

[Missinglinq]

Is pnummer defined as a Text Datatype or a Number Datatype?

"pnummer = '" & Me.Pnummer & "'"

is the correct syntax if the Field is defined as Text, but for a Numeric Field the correct syntax would be

"pnummer = " & Me.Pnummer

The other thing I wonder about is whether the Field and Control that holds the person's personell number is actually pnummer, as you have it in your code, or if it should be pnumber.

Linq ;0)>

[JeroenMioch]

The field is defined as text.
Pnummer is Dutch, in English it would be Pnumber :P

The first piece of code in my first post is correct and this works as well as the last piece of code.
Its just the inbetween part where i want two more things checked :

If the field "Miva_vanaf" (wich is defined as a datefield) containes a value lower or equal to the system date, or contains a vale that is an empty string.

If above is true (empty, or equal and lower then system date) then me.slvMIVA is true, else its false

[Missinglinq]

If you go into the tblMindeValide Table and look at the miva_tot column, does it only show dates, or does it include times, as well?

Linq ;0)>

[JeroenMioch]

Im still struggeling with this.

If i put the code below then it works except if the Miva_tot date isnt valid anymore (thus, lower then the system date)

Dim Miva_tot As Date
Dim strSQL3 As String
strSQL3 = "SELECT DISTINCT Pnummer, Miva_tot FROM tblMindervalide WHERE Pnummer = '" & Me.Pnummer & "' "
With CurrentDb.OpenRecordset(strSQL3)
    If .RecordCount > 0 Then
       Me.slvMIVA = True
       Else
       Me.slvMIVA = False
    End If
End With

[pbaldy]

How about


strSQL3 = "SELECT DISTINCT Pnummer, Miva_tot FROM tblMindervalide WHERE Pnummer = '" & Me.Pnummer & "' AND Miva_tot >= Date()"

[JeroenMioch]

Hi Paul, thanks for your reply.
That part works.
But when miva_tot is left empty it needs to flag the record as true as well.

I tried

strSQL3 = "SELECT DISTINCT Pnummer, Miva_tot FROM tblMindervalide WHERE Pnummer = '" & Me.Pnummer & "' AND Miva_tot >= Date() or isNull(miva_tot)"

But then every record gets flagged :P

I have a feeling we are close to the solution !

[pbaldy]

Whenever you have a mix of AND & OR, you should use parentheses to clarify the logic.

(A And B) Or C

is different than

A And (B Or C)

[JeroenMioch]

That sounds ..logical

And youve hit it spot on, IT WORKS !!
You have no idea howmuch i stuggeld with this.

Many thanks Paul !

[pbaldy]

Happy to help! Without parentheses, you're guessing as to what logic Access will decide on. I'm sure it has a built-in set of rules it follows, but I prefer to clarify matters.

[JeroenMioch]

Not only did you clarify it so the code knows what to do, but youve clarified it to me so i might solve this problem myself next time i have to use a similair piece of code.

So i thank you twice for that

[pbaldy]

You're welcome twice!