Results 1 to 15 of 15
  1. #1
    JeroenMioch's Avatar
    JeroenMioch is offline Competent Performer
    Windows Vista Access 2003
    Join Date
    May 2012
    Location
    Den Haag, Netherlands
    Posts
    368

    checking date when recordcount > 0

    Im struggeling with the following :



    Code:
    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

    Code:
    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.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What specifically are you struggling with? If you mean to check the recordset, you'd have to include that field.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    JeroenMioch's Avatar
    JeroenMioch is offline Competent Performer
    Windows Vista Access 2003
    Join Date
    May 2012
    Location
    Den Haag, Netherlands
    Posts
    368
    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.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    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.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    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)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    JeroenMioch's Avatar
    JeroenMioch is offline Competent Performer
    Windows Vista Access 2003
    Join Date
    May 2012
    Location
    Den Haag, Netherlands
    Posts
    368
    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

  7. #7
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    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)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    JeroenMioch's Avatar
    JeroenMioch is offline Competent Performer
    Windows Vista Access 2003
    Join Date
    May 2012
    Location
    Den Haag, Netherlands
    Posts
    368
    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)

    Code:
    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

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    How about


    strSQL3 = "SELECT DISTINCT Pnummer, Miva_tot FROM tblMindervalide WHERE Pnummer = '" & Me.Pnummer & "' AND Miva_tot >= Date()"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    JeroenMioch's Avatar
    JeroenMioch is offline Competent Performer
    Windows Vista Access 2003
    Join Date
    May 2012
    Location
    Den Haag, Netherlands
    Posts
    368
    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

    Code:
    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 !

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    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)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    JeroenMioch's Avatar
    JeroenMioch is offline Competent Performer
    Windows Vista Access 2003
    Join Date
    May 2012
    Location
    Den Haag, Netherlands
    Posts
    368
    That sounds ..logical

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

    Many thanks Paul !

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    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.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    JeroenMioch's Avatar
    JeroenMioch is offline Competent Performer
    Windows Vista Access 2003
    Join Date
    May 2012
    Location
    Den Haag, Netherlands
    Posts
    368
    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

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You're welcome twice!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. RecordCount after filtering is incorrect
    By George in forum Access
    Replies: 2
    Last Post: 05-29-2012, 01:02 PM
  2. Which is better? checking in VBA or SQL
    By allstar45 in forum Queries
    Replies: 1
    Last Post: 02-17-2012, 12:13 PM
  3. Recordcount with filter on
    By injanib in forum Forms
    Replies: 3
    Last Post: 06-15-2011, 03:07 PM
  4. Date error checking
    By oediaz in forum Programming
    Replies: 2
    Last Post: 03-26-2010, 12:08 PM
  5. Replies: 3
    Last Post: 10-20-2009, 06:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums