Results 1 to 9 of 9
  1. #1
    Zekii61 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2013
    Location
    USA
    Posts
    8

    Post Changing data in existing rows in a table

    Assignment : Change credit limit to $27.00 for employees who have credit_limit = 15.00 or credit_limit field = 'null'






    I input the following code:


    update sec0411_employees
    set credit_limit = '27.00'
    where credit_limit = 'null'
    and credit_limit = '15.00';



    And I get...

    Click image for larger version. 

Name:	data type mismatch.png 
Views:	14 
Size:	131.1 KB 
ID:	13988




    CAN SOMEONE PLEASE HELP? WHAT IS WRONG WITH THE CODING? I DID EVERYTHING IT SAYS IN THE BOOK BUT NO SUCCESS...!

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    Your data base is going to have a hard time finding a field where the value IS NULL AND = 15.

    Maybe you can try the OR operator in place of the AND operator.

  3. #3
    Zekii61 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2013
    Location
    USA
    Posts
    8
    I replace the AND with the OR functions and still no avail ... Any other suggestions?

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,166
    The quotes indicate text --- I think you want numeric.
    Also, nothing = Null, not even Null.
    Your current constraint on credit_limit does not make sense , as has been pointed out by ItsMe.

  5. #5
    Zekii61 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2013
    Location
    USA
    Posts
    8
    I'm still having a hard time. Can you "re-write" the code in an understandable form? Because I've tried taking out the quotes... The null I thought was fine the way it was. What I think is the problem is my understand of the syntax..

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    Have you tried using the Criteria field and the Or field at the bottom of your query window while in Design View. See if you can get the results you are after that way and then take a look at the SQL view.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,166
    You could try the following
    Code:
    update sec0411_employees
    set credit_limit = 27.00
    where credit_limit Is Null
    OR credit_limit = 15.00;

  8. #8
    Zekii61 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2013
    Location
    USA
    Posts
    8
    I've tried the code you gave me and it works!!! I really appreciate your help Orange Thank you

    My only question to you is, why did --- credit_limit = null --- not work, yet --- credit_limit is null --- work? I need to understand these small issues So I can develop my skills...

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,166
    From my earlier post
    Also, nothing = Null, not even Null.
    Use Google or Bing with "Database NULL"

    see this and other links regarding NULL
    http://databases.about.com/cs/sql/a/aa042803a.htm

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

Similar Threads

  1. Replies: 7
    Last Post: 09-20-2013, 09:14 AM
  2. Adding data from linked table to existing table???
    By claysea in forum Import/Export Data
    Replies: 3
    Last Post: 02-21-2013, 12:23 PM
  3. Replies: 2
    Last Post: 12-17-2012, 03:46 PM
  4. Replies: 11
    Last Post: 07-24-2012, 07:50 PM
  5. Help in changing an existing database
    By adamaphar in forum Access
    Replies: 6
    Last Post: 02-14-2012, 09:55 AM

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
  •  
Other Forums: Microsoft Office Forums