Results 1 to 3 of 3
  1. #1
    Join Date
    May 2013
    Posts
    2

    Post Unanswered: Update all records with current date

    Hi all, first post.

    I have a table called Orders. A query returns all the orders that are awaiting despatch - this is based on the despatch date field being blank.

    I'm looking to mark off the orders as completed (i.e. no longer awaiting despatch) by inserting the current date into the "Despatch Date" field.

    I assumed I could create a SQL type query behind a command button, similar to:

    Update Orders set [Despatch Date] = Date()
    Where [Despatch Date] = "";

    But I'm not having any luck I'm hitting an error "Data Type Mismatch in criteria expression".

    I assumed that was relating to the orders table Despatch Date field not being a date / time format. But it is.

    Any help on how to do this would be greatly appreciated

    Regards
    James

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    My guess is that [Despatch Date] is going to be datatype datetime, so specifying

    Code:
    ...
    Where [Despatch Date] = "";
    isn't going to work. as "" is not a valid datetime value. I think you would be better off trying

    Code:
    Update Orders set [Despatch Date] = Date()
    Where isnull ([Despatch Date]) = true;
    to the human eye "" may be the same as NULL, but to pernickety computers they most certainly are not.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2013
    Posts
    2

    Smile

    Brilliant, thank you, that worked a treat!

    Regards
    James

Posting Permissions

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