Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Join Expression (2003)

    While I was on holiday, we had our production software upgraded, and this has caused some of my databases to throw a wobbly. Some of the fields have changed names so I have had to amend queries, etc. I have tried to amend one, and when I save it, I get the error message 'Join Expression not supported'. All I've changed is the table/field names. Could someone have a look at the the SQL and see if anything is obvious. Thanks.

    SELECT Invoice1.code, Invoice1.invdate, Invtrans1.assignment, Invtrans1.Reason, Invtrans1.Quantity, Invtrans1.reference, Invtrans1.Price, Invtrans1.Conversion, [Price]*Invtrans1.Conversion AS [Price Each], Stock1.descline, [Price Each]*[Quantity] AS [Total Price], Invtrans1.assignment, COF1.custpart INTO tblInvoices
    FROM COF1, (Invtrans1 RIGHT JOIN Invoice1 ON Invtrans1.code = Invoice1.code) LEFT JOIN Stock1 ON Invtrans1.[Stock/Payment Code] = Stock1.CODE
    WHERE (((Invoice1.code) Between [From ] And [To ]) AND ((Invtrans1.Quantity)>0) AND ((Invtrans1.Reason)<>" ") AND ((Invoice1.Customer)="PRE005") AND ((Invoice1.type)="COSTOCK"));

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Join Expression (2003)

    Hard to see in this light, could you posted a zipped version of your database

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

    Re: Join Expression (2003)

    We don't know which fields changed, so it is impossible for us to tell what is wrong. Check whether the field names in

    (Invtrans1 RIGHT JOIN Invoice1 ON Invtrans1.code = Invoice1.code) LEFT JOIN Stock1 ON Invtrans1.[Stock/Payment Code] = Stock1.CODE

    are correct for the new situation.

  4. #4
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Join Expression (2003)

    Thanks for your replies, but I've had to totally re-write the query, but in the same database, I'm getting a problem with a selection form. Previously I had help with this in post <post#=480297>post 480297</post#> and everything worked well, until our guys got involved. The code I had on the OK button was :-

    Dim strFilter As String
    Dim varItem As Variant

    If Me.lstInvoices.ItemsSelected.Count = 0 Then
    MsgBox "Please select one or more invoices.", vbExclamation
    Me.lstInvoices.SetFocus
    Exit Sub
    End If

    For Each varItem In Me.lstInvoices.ItemsSelected
    strFilter = strFilter & "," & Chr(34) & Me.lstInvoices.ItemData(varItem) & Chr(34)
    Next varItem
    strFilter = "[Invoice NUMBER] In(" & Mid(strFilter, 2) & ")"

    DoCmd.Close
    DoCmd.OpenReport "rptInvoice2", acViewPreview, , strFilter
    End Sub

    The table is still called tblInvoices, but Invoice NUMBER no longer exists. This is now called 'code'. So now the code reads:-

    Dim strFilter As String
    Dim varItem As Variant

    If Me.lstInvoices.ItemsSelected.Count = 0 Then
    MsgBox "Please select one or more invoices.", vbExclamation
    Me.lstInvoices.SetFocus
    Exit Sub
    End If

    For Each varItem In Me.lstInvoices.ItemsSelected
    strFilter = strFilter & "," & Chr(34) & Me.lstInvoices.ItemData(varItem) & Chr(34)
    Next varItem
    strFilter = "code In(" & Mid(strFilter, 2) & ")"

    DoCmd.Close
    DoCmd.OpenReport "rptInvoice2", acViewPreview, , strFilter
    End Sub

    The Invoices still appear for selection but the ones I de-select, still appear in the report. I've managed to get the relevant data into a smaller database. If you could have a look, I would be greatful.
    Attached Files Attached Files

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

    Re: Join Expression (2003)

    I think you stripped out too much. The report in the database doesn't work. It contains references to non-existing fields Date, To, From, Carriage, VAT and Weight, and the code refers to a non-existing control Address.

  6. #6
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Join Expression (2003)

    Sorry Hans, they aren't missing fields, the user enters the data. To and From, are the Invoice ranges, and the Carriage, VAT and Weight could all be left blank at the moment. Perhaps the non-existing control Address is the problem. Which is it?

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

    Re: Join Expression (2003)

    There is event code in the report module:

    Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
    If Me.Page <> Pages Then
    Me.Address.Visible = False
    Else
    Me.Address.Visible = True
    End If
    End Sub

    but there is no control named Address on the report, so the code will throw up an error.

  8. #8
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Join Expression (2003)

    Sorry, I forgot about the code. The Address was deleted in the attachment, as this is a customer address. The main problem was that the selection doesn't seem to be saving and all the records are printed.

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

    Re: Join Expression (2003)

    Apart from the things I mentioned, it works OK for me. If I select 4 records in the list, then click OK, the report will display 4 records.

  10. #10
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Join Expression (2003)

    Thanks Hans. It may have been something that I have since removed, that was causing the problem, but I certainly didn't give me the selection I wanted. Thanks again for your help.

Posting Permissions

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