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

    replacing constants in a string (Access 2000)

    Can somebody help me in replacing some parts of a string with constants?

    I will show at first the record source i have and then how i want to change it

    The current record source is as follows


    Dim Acc as string
    Acc = " SELECT DISTINCTROW Orders.paymentid, Orders.invoicedate, Orders.PaymentMethodID,

    Customers.Customerid, Customers.CompanyName, products.Productid, products.grade, products.code,

    [order details].UnitPrice, [order details].Quantity, Customers.afid, Customers.kindid, Customers.bulstat,

    Customers.taxid " & _
    " FROM products INNER JOIN (Customers RIGHT JOIN (Orders INNER JOIN [order details] ON

    Orders.orderid = [order details].OrderID) ON Customers.Customerid = Orders.customerid) ON

    products.Productid = [order details].ProductID " & _
    " WHERE (((Orders.paymentid) = True) And ((Year([invoicedate])) = 2003) And ((month([invoicedate])) = 1))

    ORDER BY Orders.invoicedate;"
    ==================

    Now i want to replace as follows
    Dim Jahr As String Jahr = And ((Year([invoicedate])) = 2003)
    Dim Monat As String Monat = And ((month([invoicedate])) = 1))
    and the string part under question to read:

    " WHERE (((Orders.paymentid) = True) & Jahr & Monat & ORDER BY Orders.invoicedate;"



    I get a syntax error and i understand somewhere there is a mistake in my code.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: replacing constants in a string (Access 2000)

    In
    <hr>Dim Jahr As String Jahr = And ((Year([invoicedate])) = 2003)<hr>
    there are two errors:
    - You can't put two instructions on one line (unless you separate them by a colon)
    - You must put quotes around the value
    And if you really mean a constant, you should use Const instead of Dim.
    So it becomes either

    Dim Jahr As String
    Jahr = "And ((Year([invoicedate])) = 2003)"

    or

    Const Jahr As String = "And ((Year([invoicedate])) = 2003)"

    You also forgot some quotes and a space in the WHERE condition:

    " WHERE (((Orders.paymentid) = True) " & Jahr & " " & Monat & " ORDER BY Orders.invoicedate;"

Posting Permissions

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