Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    where clause (Access 2000)

    A where string


    I have a working code where i want to substiture a part of the Where clause with another string.But somehow
    i cannot hit the right syntax and i receive a syntax eror.
    My working code is as follows
    Dim StrBas As String
    Dim strAfid As String
    strAfid = "And (([customers].[afid])=1))"

    StrBas = " SELECT [order details].[ProductID], [products].[grade], Sum([order details].[liters]) AS Liters,

    [products].[size], [orders].[customerid], Month([invoiceDate]) AS ContrMonth, [customers].[afid] " & _
    " 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]))=2001) And (([orders].[paymentid])=True) And ((Month([invoiceDate])) Between 1 And 12) And

    (([customers].[afid])=1)) " & _
    " GROUP BY [order details].[ProductID], [products].[grade], [products].[size], [orders].[customerid], Month([invoiceDate]),

    [customers].[afid] " & _
    " ORDER BY [products].[grade];"

    Me.RecordSource = StrBas


    i want to rewrite the above valid code and replace a part of the Where clause with the string StrAfid as follows



    Dim StrBas As String
    Dim strAfid As String
    strAfid = "And (([customers].[afid])=1))"

    StrBas = " SELECT [order details].[ProductID], [products].[grade], Sum([order details].[liters]) AS Liters,

    [products].[size], [orders].[customerid], Month([invoiceDate]) AS ContrMonth, [customers].[afid] " & _
    " 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]))=2001) And (([orders].[paymentid])=True) And ((Month([invoiceDate])) Between 1 And 12) &

    strAfid " & _
    " GROUP BY [order details].[ProductID], [products].[grade], [products].[size], [orders].[customerid], Month([invoiceDate]),

    [customers].[afid] " & _
    " ORDER BY [products].[grade];"

    Me.RecordSource = StrBas


    However i receive alsways errors and i cannot right the syntax properly.Can somebody help me?

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: where clause (Access 2000)

    you wrote this code:

    " WHERE (((Year([InvoiceDate]))=2001) And (([orders].[paymentid])=True) And ((Month([invoiceDate])) Between 1 And 12) &

    strAfid " & _



    It needs to be:

    " WHERE (((Year([InvoiceDate]))=2001) And (([orders].[paymentid])=True) And ((Month([invoiceDate])) Between 1 And 12)" & srAfid & _
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    thank you

    Dear Sir,

    i will check up and study your suggeston but furst of all i want to thank cordially for the pains you have taken to answer
    Best regards

Posting Permissions

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