Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    79

    Unanswered: Query with 3 files

    Please tell me what I'm doing wrong.

    "SELECT tblInvoiceDtl.BOL#, tblCustomers.CustName, " & _
    "tblInvoiceHdr.InvoiceNumber FROM tblInvoiceHdr LEFT JOIN tblCustomers ON " & _
    "tblInvoiceHdr.BillToNumber = tblCustomers.CustNumber LEFT JOIN tblInvoiceDtl ON " & _
    "tblInvoiceHdr.InvoiceNumber = tblInvoiceDtl.InvoiceNumber " & _
    "WHERE tblInvoiceHdr.Posted Is Null ORDER BY tblInvoiceDtl.BOL#, tblInvoiceHdr.InvoiceNumber;"

    Thanks

  2. #2
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Unhappy

    Hi,

    Though I'm not 100% sure, I think the (#) is the culprit. I thought Access reserved that for Dates. Try removing that. However, if you actually named the field that way, I would really suggest anyway that you rename it something like BolNum or BolNo. Not a good practice to put symbols in field names, causes conflicts.

    have a nice one,
    BUD
    Last edited by Bud; 01-28-05 at 04:57. Reason: fix tags

  3. #3
    Join Date
    Mar 2004
    Posts
    79
    Still isn't working.

  4. #4
    Join Date
    Dec 2004
    Location
    Haifa
    Posts
    6

    Question Do u receive an error ?

    Do u receive an error ?

  5. #5
    Join Date
    Mar 2004
    Posts
    79
    No error...just no data.

  6. #6
    Join Date
    Dec 2004
    Location
    Haifa
    Posts
    6

    if no error message displayed, I think that it's not a syntax problem.
    it some kind of logic problem,
    can u post an example (a picture ) of tblInvoiceDtl, tblCustomers, tblInvoiceHdr tables data ?

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Query problem

    brmk,

    I recreated the tables and query statement. My query statement looks like this:

    SELECT tblInvoicedtl.[BOL#], tblCustomers.CustName, tblInvoiceHdr.InvoiceNumber FROM (tblInvoiceHdr LEFT JOIN tblCustomers ON tblInvoiceHdr.BillToNumber = tblCustomers.CustNumber) LEFT JOIN tblInvoicedtl ON tblInvoiceHdr.InvoiceNumber = tblInvoicedtl.InvoiceNumber WHERE (((tblInvoiceHdr.Posted) Is Null)) ORDER BY tblInvoicedtl.[BOL#], tblInvoiceHdr.InvoiceNumber;

    NOTE: I took out all the & _ syntax. Copy the above statement as is to your SQL Select statement and see if it works. Seems like there's a problem with your " & _ uses.

    Hope that helps.

    Paul Kohn
    Database Administrator

  8. #8
    Join Date
    Mar 2004
    Posts
    79
    I copied your statement exactly and then put the & _ back in and it worked!
    Thanks so much!

Posting Permissions

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