Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Thanked 0 Times in 0 Posts

    Public Constant (Access 2000)

    I want to replace the Year in my codes with a Public constant whih i placed in my public module:
    Public Const CnstYear = 2002

    However i cannot replace my constant in my codes. For example in the folowing code i tried to replace
    the year 2002 with my constant CnstYear but Access didnt accept it.

    My original and working code is :

    WHERE (((Year([InvoiceDate]))=2002)

    What i tried is:

    WHERE (((Year([InvoiceDate]))=CnstYear)
    Unfortunately it doesnt wotk and i cannot open the report.Access cannot recognize my constant.

    I will be grateful for any help.

    I will also cite my full code just in case i am wrong somewhere esle:

    strBuffer = " SELECT [order details].ProductID, products.grade, Sum([order details].liters) AS Liters, products.size, orders.customerid,

    customers.afid, Month([invoiceDate]) AS ContrMonth " & _
    " FROM (customers INNER JOIN orders ON customers.Customerid = orders.customerid) INNER JOIN (products INNER JOIN [order

    details] ON (products.Productid = [order details].ProductID) AND (products.Productid = [order details].ProductID)) ON orders.orderid =

    [order details].OrderID " & _
    " WHERE (((Year([InvoiceDate]))=2002 ) AND ((orders.paymentid)=True) AND ((Month([invoiceDate])) Between 1 And 12)) " & _

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: Public Constant (Access 2000)

    You can't include a constant inside the SQL string. You have to concatenate it to the string so that its actual value replaces the constant. That means you have to do something like this:

    "WHERE (((Year([InvoiceDate]))=" & CnstYear & ")"

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Crystal Beach, FL, Florida, USA
    Thanked 41 Times in 40 Posts

    Re: Public Constant (Access 2000)

    And I'd think twice about making that a constant. That really locks you in to using that value, as a constant is evaluated at compile time, not at runtime. By definition, a constant is a value that is not expected to change. Why not make it a variable that you can change from code just in case?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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