Results 1 to 5 of 5

Thread: Access SQL - Grragh

  1. #1
    Registered User
    Join Date
    Mar 2005
    Posts
    3
    Thanks
    0
    Thanked
    0 times in 0 posts

    Access SQL - Grragh

    Im totally new to this forum, was told by a friend that you lot know your stuff and to ask

    Basically im trying to so an UPDATE query, that will update a field based upon the value of another. So far ive had no luck, tried IF ... THEN ... ELSE but it didnt like that at all, so im now trying CASE statements but again .... random errors:

    "Syntax Error (missing operator) in query expression Case" atm my code is

    Code:
    UPDATE Villas INNER JOIN Bookings ON Villas.VillaID=Bookings.VillaID 
           SET Bookings.TotalCost  =
                     CASE
                       WHEN Bookings.BookingStart Between #6/5/2005# And #12/12/2005#
                       THEN Bookings.Weeks*Villas.PricePerWeek *1.2
                     ELSE Bookings.Weeks*Villas.PricePerWeek
                     END;
    laugh at me if so be ... but help !! this is for a project due in ... yesterday.
    Last edited by Roast; 17-03-2005 at 12:21 PM.

  2. #2
    Pixel Abuser Spunkey's Avatar
    Join Date
    Nov 2003
    Location
    Milton Keynes
    Posts
    1,523
    Thanks
    0
    Thanked
    0 times in 0 posts
    Welcome to the forums! give this a try...

    UPDATE Villas INNER JOIN Bookings ON Villas.VillaID=Bookings.VillaID
    SET Bookings.TotalCost = Booking.Weeks*Villas.PricePerWeek *1.2
    WHERE Bookings.BookingStart > #06/05/2005# AND Bookings.BookingStart < #12/12/2005#

    should work in Access, wont in SQLS.
    HTH!

  3. #3
    Registered User
    Join Date
    Mar 2005
    Posts
    3
    Thanks
    0
    Thanked
    0 times in 0 posts
    Tried that before, but the problem is that i need it to do the non peak bookings aswell, so there needs to be an alternative outcome if the BookingStart isnt between those dates. Not sure if that was clear in the original post. E.G

    if bookingstart = 05/05/2005 it needs to just do price perweek * weeks
    but
    if bookingstart = 06/05/2005 it needs to do priceperweek*weeks*1.2

    ignore the dates, i was just testing another query because it was just giving me 0 records, the actual end of peak is 31/05/2005.

    Also unrelated, is it possible to make it something like 05/06/**** it didnt seem to like that, might have been because its a date field :S

  4. #4
    Pixel Abuser Spunkey's Avatar
    Join Date
    Nov 2003
    Location
    Milton Keynes
    Posts
    1,523
    Thanks
    0
    Thanked
    0 times in 0 posts
    *slaps head* completely missed the ELSE statement.

    Anyway, IIRC Access doesnt have the CASE like SQL Server does. You should be able to use an Inline If statement though - give this a whirl...

    UPDATE Villas INNER JOIN Bookings ON Villas.VillaID=Bookings.VillaID
    SET Booking.TotalCost = IIF(Bookings.BookingStart > #06/05/2005# AND Bookings.BookingStart < #12/12/2005#, Bookings.Weeks*Villas.PricePerWeek *1.2, Bookings.Weeks*Villas.PricePerWeek)

  5. #5
    Registered User
    Join Date
    Mar 2005
    Posts
    3
    Thanks
    0
    Thanked
    0 times in 0 posts
    thanks alot !

    Had to chance one small thing (a few []) and it works fine !

    heres the final code
    Code:
    UPDATE Villas INNER JOIN Bookings ON Villas.VillaID = Bookings.VillaID SET Bookings.TotalCost = IIf([Bookings].[BookingStart]>#6/5/2005# And [Bookings].[BookingStart]<#12/12/2005#,[Bookings].[Weeks]*[Villas].[PricePerWeek]*1.2,[Bookings].[Weeks]*[Villas].[PricePerWeek]);
    Now thats fixed ... is there a way to have it between the months instead of the months and year, so that it wouldnt need to be changed every year.
    Last edited by Roast; 17-03-2005 at 03:39 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. FTP access via Java
    By Kezzer in forum Software
    Replies: 11
    Last Post: 11-03-2005, 08:47 AM
  2. Access SQL Issue...
    By Stoo in forum Software
    Replies: 3
    Last Post: 03-03-2005, 01:02 PM
  3. Replies: 7
    Last Post: 05-02-2005, 03:53 PM
  4. Access Reports - help....
    By Lead_Head in forum Software
    Replies: 5
    Last Post: 22-10-2003, 12:19 PM
  5. NT4 Domain - A: drive access
    By joshwa in forum Software
    Replies: 1
    Last Post: 27-08-2003, 09:07 AM

Posting Permissions

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