Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2012
    MO, USA

    Question Unanswered: IS NOT NULL Query Assistance

    If anyone can help me figure out this problem, I would be most appreciative.

    I have query1:
    SELECT EmployeeLog.PR_BADGE_ID, Count(EmployeeLog.PR_LOG_ON) AS CountOfPR_LOG_ON, Round(Sum(DateDiff("s",[PR_LOG_ON],[PR_LOG_OFF])/60),2) AS TimeLoggedOn, EmployeeLog.DATE AS DATE2
    FROM EmployeeLog
    GROUP BY EmployeeLog.PR_BADGE_ID, EmployeeLog.DATE
    HAVING (((EmployeeLog.PR_BADGE_ID) Is Not Null));

    and query2:
    SELECT Archive.PR_BADGE_ID, DateValue([DTSCAN]) AS DATESCAN, Archive.IXRNUM, Count(Archive.IXRNUM) AS CountOfIXRNUM
    FROM Archive
    GROUP BY Archive.PR_BADGE_ID, DateValue([DTSCAN]), Archive.IXRNUM
    HAVING (((Archive.PR_BADGE_ID) Is Not Null));

    both together building a query3.

    My problem is that for some reason, query1 gives me a "datatype mismatch error in criteria expression" whenever I search the EmployeeLog.PR_BADGE_ID field for IS NOT NULL, essentially returning ALL records in the table for the higher level query.

    I do not understand why query2 is fine with searching for a PR_BADGE_ID that is IS NOT NULL, but query1 continues to give me issues at every turn.

    Both fields in both tables are text fields. I have tried IS NOT NULL, <>"", and a few other longshot attempts with no success. Can anyone assist me and explain why query1 has a problem searching for IS NOT NULL but query2 does not?

  2. #2
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    In Access

    Not IsNull(FieldName)

    would be the syntax you're looking for, I believe.

    Linq ;0)>
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Apr 2012
    MO, USA


    Thank you for the response MissingLinq. I found my error with some help from a user on another forum. I had a couple fields listed as the wrong data type causing an expression calculation to go haywire.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts