Results 1 to 6 of 6
  1. #1
    rayted is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    34

    New column with subtraction (using two existing columns)


    Hi guys,

    Can someone advise how I need to do this please?

    I have a column which has varying numbers i.e. 10, 11, 12 etc. Let’s say this is called: ‘Days’.
    I have a new column I have setup whereby I want to do this:
    Take ‘Start Date’ (column) and subtract the number from the ‘Days’ column. So if I input a search for a customer, I want to tell them the date of when they get access to a booking by looking at the ‘start date’ subtracted by the days. Can anyone advise?

    Click image for larger version. 

Name:	tabledel.PNG 
Views:	7 
Size:	7.0 KB 
ID:	36483

    image attached. you can see the first column has dates and then the second column has '28'. this represents days before a customer can view there booking. I want an SQL to write in the third column the date that a customer can view their booking based on the number of days in the 2nd column. I.e. 28 days from their start date (column 1) is when the booking can be viewed. Thank you for any help in advance!

    Thank you!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In SQL you can simply subtract, or use the DateAdd() function. It looks like your days field is text; it should be a numeric field, Integer or Long.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    rayted is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    34
    Hi pbaldy

    but the days (where it says 28) has different data (i.e. a mix of text etc) it's only because I have applied a filter on a corresponding column to say: return 'days' i.e. 28 instead of 'xxxxxx', I am seeing these values. This might explain why the field may not consist of numbers>

    THANK you

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Have you tried either solution? Access may handle it okay, or you may need to apply Val() or CInt() to the result of your filter.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    rayted is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    34
    hi pbaldy

    Sorry can you please provide me with an example SQL for me to better comprehend what you're suggesting?

    thank you!

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    SELECT StartDate, Days, StartDate - Days AS Whatever
    FROM TableName
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 4
    Last Post: 11-29-2017, 10:01 AM
  2. Replies: 8
    Last Post: 08-31-2016, 07:55 AM
  3. Replies: 3
    Last Post: 03-02-2015, 09:50 AM
  4. Insert Column between existing columns?
    By JC8865 in forum Access
    Replies: 2
    Last Post: 12-06-2014, 09:05 PM
  5. Replies: 1
    Last Post: 11-12-2013, 02:19 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