Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2014
    Posts
    2

    Unanswered: Access Datatype Mismatch in criteria Expression

    This is my first post so apologies if I haven't supplied all the information.
    I'm using Access 2003.
    Our staff database stores a file path to a photograph (on the network) of the staff member. This path is generated in the [image] database field when I add a new staff member whether the photo is actually there or not.
    I want to run a query which shows which new staff members haven't given us a photo yet. It works fine until I put a criteria in the query to limit the output to people who haven't given us a picture yet. The query fails with a type mismatch error.
    I've been using Dir or FileExists to check for the presence of the picture file.

    I've tried using Dir in the query expression, Dir in a function and FileExists in a function always with the same result! In the SQL below I've included both ways of testing for the file but I've tried each individually as well and it always fails with a:
    Run time error 13 Type mismatch as soon as I put in the criteria for the function in the query or if I apply the criteria to Dir within the query I get Data type mismatch in criteria expression.

    This works
    SELECT ANESTAFF.SURNAME, ANESTAFF.FORENAME, ANESTAFF.GRADE, Dir([Image]) AS ImagePres, fDoesFileExist([image]) AS test
    FROM ANESTAFF
    WHERE (((ANESTAFF.GRADE)<>"con") AND ((ANESTAFF.CURRENT)=True) AND ((ANESTAFF.STARTED)>#8/1/2014#));

    This fails
    SELECT ANESTAFF.SURNAME, ANESTAFF.FORENAME, ANESTAFF.GRADE, Dir([Image]) AS ImagePres, fDoesFileExist([image]) AS test
    FROM ANESTAFF
    WHERE (((ANESTAFF.GRADE)<>"con") AND ((fDoesFileExist([image]))=0) AND ((ANESTAFF.CURRENT)=True) AND ((ANESTAFF.STARTED)>#8/1/2014#));

    It will also fail if I use ((Dir([Image]))=False) instead of ((fDoesFileExist([image]))=0)

    The fDoesFileExist function is as follows (I've also used FileExists in the function with the same result):

    Code:
    Public Function fDoesFileExist(pstrPath) As String
        If Dir(pstrPath) > "" Then
          fDoesFileExist = vbTrue
        Else
          fDoesFileExist = vbFalse
        End If
    End Function
    I've tried running a query on the query and still get the same result as soon as I specify a criteria to limit the output from Dir or FileExists.

    Any suggestions gratefully accepted as I've run out of ideas and can't see anyone has had this problem before.

    Below is an example output when I don't specify any criteria (names removed).

    GRADE Image ImagePres test
    ST \\ntserver1\diranest\Surveys\Images\7013452.jpg 7013452.jpg -1
    CT \\ntserver1\diranest\Surveys\Images\7072395.jpg 0
    CT \\ntserver1\diranest\Surveys\Images\7072386.jpg 0
    LAT \\ntserver1\diranest\Surveys\Images\7134153.jpg 0
    ST \\ntserver1\diranest\Surveys\Images\6143412.jpg 6143412.JPG -1
    ST \\ntserver1\diranest\Surveys\Images\7036909.jpg 0

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    consider usign Allen Brownes FileExists function

    the return value fromt eh dir function is a string of the first file that matched the supplied parameter so = 0 will always be invalid. the page here suggests that the dir function returns an empty string if the file isn't found

    you could probably use
    Code:
    WHERE ANESTAFF.GRADE <> "con" AND dir(image) = image AND ANESTAFF.CURRENT = True AND ANESTAFF.STARTED > #2014/8/1#;
    Im UK based so tend to use the ISO date format to avoid any possible errors with the abominattion that is the US date format
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2014
    Posts
    2

    Datatype Mismatch

    Thanks Healdem.
    I'll try your suggestion.
    The post was getting quite long so I didn't include all the criteria options I've tried - Is Null, "" etc.
    I've even used len() in the expression to get the length of the returned result - works fine but as soon as I enter a criteria specifying a value for the length of the returned expression (eg =0) that fails too with a datatype mismatch error, despite the len() returning 0 when the file doesn't exist.

    My other thought is that it might be something to do with the image files being on a network drive so I'm going to move them to a local drive and see if this makes any difference.

    I'll let you know how I get on.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    where len(dir(image))>0 should handle that
    or
    if it returens a null string
    where len(nz(dir(image),"")) > 0
    I'd rather be riding on the Tiger 800 or the Norton

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
  •