Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2014
    Posts
    34
    Provided Answers: 1

    Unanswered: Update Query not working

    I have a query that counts the number of absences a student has since x date. At the end of the year I want to take that count and add it to the students registration data for their permanent record. I think I should be able to do this with an update query, but it isn't working. I have the query with the count of absences and another based on the permanent record table with just the students for this year with the field where I want the absences to go. But when I try to make the update query and connect the two absences fields, I get "Type mismatch expression." But I don't understand where the mismatch is. Any help please? Thanks!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    type mismatch means that you are using a value of the wrong datatype. eg using a string where a number is expected or not setting a date value correctly.

    whether its the update bit or the where bit is hard to say as you cunningly haven't supplied the table design, the SQL of the query and sample data.....
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1
    You are asking how to make a derived field, which is a bit of a "no-no" in the database world. Once you have entered an absence date, you can always count those absences to put into reports. Why keep any count? Your type mismatch may well have to to with your condition: it should read something like

    "WHERE [adate] BETWEEN #1/1/2015# and #1/1/2016#"
    or
    "WHERE [adate] BETWEEN #" & txtDu.value & "# AND #" txtA.value & "#"

    From what I can see (I tried it), there is no problem using in an SQL statement "SELECT COUNT([aDate])".

    Hope this helps.
    J. Smith
    Aylmer, Quebec

Posting Permissions

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