Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    Grande Prairie, AB
    Posts
    9

    Unanswered: SQL syntax using a calculated field

    I've spent two days+ searching and thinking how to fix this problem and come to the conclusion I only know enough to be dangerous.

    My situation appears to be very similar to this thread:
    http://www.dbforums.com/showthread.p...5&pagenumber=2
    Where the solution finds birthdays that are due within 30 days.

    However I'm working for a car dealership and we're trying to calculate lease renewals within the next 8 months using Access.

    The only difference that I see is that instead of using the birthdate field I'm using the RenewalDate field which is calculated from the TransactionDate field and FinanceTerm using the DateAdd Function.

    Here is my SQL:
    SELECT [Manual-Vehicles].ID, [Manual-Vehicles].TransactionDate, [Manual-Vehicles].FinanceTerm, DateAdd("m",[Manual-Vehicles]![FinanceTerm],[Manual-Vehicles]![TransactionDate]) AS RenewalDate, DatePart("y",DateAdd("d",-DatePart("d",Date())+1,DateAdd("yyyy",DateDiff("yy yy",RenewalDate,Date()),RenewalDate)))-1 Between 0 And 240 AS DueNow, [Manual-Vehicles].[Finance Option], [Manual-Vehicles].VehicleYear, [Manual-Vehicles].ModelNumber, [Manual-Vehicles].Manufacturer, [Manual-Vehicles].Model, [Manual-Vehicles].StockNumber, [Manual-Vehicles].EmployeeID
    FROM Customers INNER JOIN [Manual-Vehicles] ON Customers.CustomerID = [Manual-Vehicles].UniqueID
    WHERE ((([Manual-Vehicles].[Finance Option])="Lease") AND ((Customers.TypeOfCustomer)="Individual") AND (([Manual-Vehicles].SaleCategory)="Retail") AND (([Manual-Vehicles].InventorySource)="New"));

    and here is the SQL I was trying to copy from:
    Where datepart("y", dateadd("d", -datepart("y",Date())+1, dateadd("yyyy", datediff("yyyy", @MemberExpiryDate, Date()), @MemberExpiryDate)))-1 between 0 and 30

    I believe the error is in trying to identify the RenewalDate field properly. It seems as if I've tried every possible combination of field descriptions. What am I missing?

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    In most of the places in your SQL you use:

    [TABLENAME].[FIELDNAME]

    But for some reason, in the formulation of the RenewalDate field, you decided to use:

    [TABLENAME]![FIELDNAME]

    Correct this first.

  3. #3
    Join Date
    Feb 2004
    Location
    Grande Prairie, AB
    Posts
    9

    Tried []

    The SQL may look bad to an experienced writer. I was pasting the SQL but designing the query in Access' grid.

    However when I put the table name and field name in square brackets [] with a . in the middle I get a parameter question.

    Which seems to tell me Access is not interpreting the RenewalDate field properly.

    Any suggestions, even how to search the forum for the right answer, would be appreciated

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    You get a parameter question for what ?

    For [Manual-Vehicles].[FinanceTerm] ?

    For [Manual-Vehicles].[TransactionDate] ?

    For RenewalDate] ?

    For what ?

  5. #5
    Join Date
    Feb 2004
    Location
    Grande Prairie, AB
    Posts
    9

    SQL options eliminated

    Here is what I'm getting:

    1)[RenewalDate] = produces a -1 or 0 value.

    2)[Manual-Vehicles].[RenewalDate] causes the Enter Parameter Value box to open with Manual-Vehicles.RenewalDate as the requested input.

    3) [Manual-Vehicles]![RenwalDate] gets the same result as 2) except the ! is between table & fields

    4) RenewalDate also produces a -1 or 0 value.

    5) @RenewalDate is the same as 2) except it asks for the @RenewalDate value

    http://r937.com/sql.cfm
    exlains the formula and it seems to fit the application so up till now I've thought that it was just my syntax.

    Thanks for your reply.

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Okay, let's back up.

    Whenever you solve a problem, you break the problem into smaller, simpler problems, and you isolate the issue.

    So, how an we do that here ?

    In your select statement, get rid of every field, except the RenewalDate column. That way, you will be able to test if the problem is in that field.

    Now, if the problem still exists, you see that that field is the following formula:

    DateAdd("m",[Manual-Vehicles].[FinanceTerm],[Manual-Vehicles].[TransactionDate])

    So, what can go wrong with this formula ?

    Is the [Manual-Vehicles].[FinanceTerm] field producing the data you expect to go into this DataAdd function ? How do you tell ? You take the query and you eliminate all output fields except [Manual-Vehicles].[FinanceTerm].

    Do the same thing for [Manual-Vehicles].[TransactionDate].

    You have to work through this problem by breaking it down and studying the individual elements of the problem.

Posting Permissions

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