Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2002
    Location
    Houston, TX
    Posts
    116

    Unanswered: No Current Record

    Ok I have searched various posts and can not find an answer. I have a query that links two tables (no problem). But when I add a field I get "No Current Record". I don't know why. The SQL view is big and might freak someone out. But if you want me to post I will. I have the tables link to pull all records from the left table and link matching on the right table of the site field. Any one able to help would be greatly appreciated
    texasalynn
    It's AWL Good!

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    What do you mean by "adding a field"? And when are you adding this field? Also, is this new field part of the join?
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jun 2002
    Location
    Houston, TX
    Posts
    116
    "adding a field" I take a field from the right table and insert into the query. So yes it is a part of the join
    texasalynn
    It's AWL Good!

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Impossible to debug without seeing the query. Just pare it down to a functional example and we'll take a crack at it.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jun 2002
    Location
    Houston, TX
    Posts
    116
    ok here goes

    Code:
    SELECT [USBancorp Summary].ACCT, [USBancorp Summary].LOC, [USBancorp Summary].Property, [USBancorp Summary].[Company Name], [USBancorp Summary].Service_Address, [USBancorp Summary].CITY, [USBancorp Summary].ST, [USBancorp Summary].ZIP, [USBancorp Summary].[Company No], [USBancorp Summary].CostCenter, [Srv run w_Bill to name].BILLNAME AS [Service Provider], [USBancorp Summary].[Type service], [USBancorp Summary].QTY, [USBancorp Summary].SZ, [USBancorp Summary].VL, IIf([HDPUDY] Is Null Or Len([HDPUDY])-CharCount("-",[HDPUDY])=0,"ON CALL",Len([HDPUDY])-CharCount("-",[HDPUDY])) AS FREQ, [USBancorp Summary].HDSYCD, [USBancorp Summary].[Current StdMo Chg], [USBancorp Summary].[Transportation Cost/Haul], [USBancorp Summary].[Disposal Cost/Ton], [USBancorp Summary].[Current Taxes], [USBancorp Summary].OTHER_MISC, [USBancorp Summary].TOTAL, [USBancorp Summary].STORE_TOTAL, [USBancorp Summary].HDIDDT, [USBancorp Summary].[Monthly QTY of TONS], [USBancorp Summary].HDCTYP
    FROM [Srv run w_Bill to name] RIGHT JOIN [USBancorp Summary] ON [Srv run w_Bill to name].HHNANO = [USBancorp Summary].ACCT;
    texasalynn
    It's AWL Good!

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by texasalynn
    ok here goes

    Code:
    SELECT [USBancorp Summary].ACCT, [USBancorp Summary].LOC, [USBancorp Summary].Property, [USBancorp Summary].[Company Name], [USBancorp Summary].Service_Address, [USBancorp Summary].CITY, [USBancorp Summary].ST, [USBancorp Summary].ZIP, [USBancorp Summary].[Company No], [USBancorp Summary].CostCenter, [Srv run w_Bill to name].BILLNAME AS [Service Provider], [USBancorp Summary].[Type service], [USBancorp Summary].QTY, [USBancorp Summary].SZ, [USBancorp Summary].VL, IIf([HDPUDY] Is Null Or Len([HDPUDY])-CharCount("-",[HDPUDY])=0,"ON CALL",Len([HDPUDY])-CharCount("-",[HDPUDY])) AS FREQ, [USBancorp Summary].HDSYCD, [USBancorp Summary].[Current StdMo Chg], [USBancorp Summary].[Transportation Cost/Haul], [USBancorp Summary].[Disposal Cost/Ton], [USBancorp Summary].[Current Taxes], [USBancorp Summary].OTHER_MISC, [USBancorp Summary].TOTAL, [USBancorp Summary].STORE_TOTAL, [USBancorp Summary].HDIDDT, [USBancorp Summary].[Monthly QTY of TONS], [USBancorp Summary].HDCTYP
    FROM [Srv run w_Bill to name] RIGHT JOIN [USBancorp Summary] ON [Srv run w_Bill to name].HHNANO = [USBancorp Summary].ACCT;

    Ahhhh ... You're doing a right join adding a column from the [USBancorp Summary] table to the query correct? What field (or expression) are you trying to add? You're problem could be the result of the type of join you're using ...
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Jun 2002
    Location
    Houston, TX
    Posts
    116
    sorry I posted the wrong query. Here is the correct query. The field that causes the problem "ContainerOwnership"

    Code:
    SELECT [Invoice History Detail].HDCUNO AS ACCT, [Invoice History Detail].HHSLOC AS LOC, [Invoice History Detail]![CUUFL2] AS Property, [Invoice History Detail]![HHNAME] AS [Company Name], [Invoice History Detail]![CUADR1] AS Service_Address, [Invoice History Detail]![CUCITY] AS CITY, [Invoice History Detail]![CUSTAT] AS ST, [Invoice History Detail]![CUPOST] AS ZIP, USBancorp.[Company No], USBancorp.CostCenter, USBancorp.ContainerOwnership, IIf((Left([HDCUNO],3)="001" And Left([HDSYCD],1)<>"1") Or ((Left([HDCUNO],3)="002" And Left([HDSYCD],1)<>"7") And (Left([HDCUNO],3)="002" And Right([HDVLCD],1)<>"R")),"TRASH",IIf([HDVLCD]<>"","RECYCLE","")) AS [Type service], [Invoice History Detail]![HDCTQT] AS QTY, [Invoice History Detail]![HDCTSZ] AS SZ, [Invoice History Detail]![HDVLCD] AS VL, [Invoice History Detail].HDPUDY, [Invoice History Detail].HDSYCD, Sum(IIf([Invoice History Detail]![HDCTYP]="22",[Invoice History Detail]![HDTAMT],0)) AS [Current StdMo Chg], Sum([Invoice History Detail].HDHAMT) AS [Transportation Cost/Haul], Sum([Invoice History Detail].HDDAMT) AS [Disposal Cost/Ton], Sum([HDTAMT]-[HDHAMT]-[HDDAMT]-IIf([HDCTYP]="25" Or [HDCTYP]="29",[HDTAMT],0)-IIf([Invoice History Detail]![HDCTYP]="22",[Invoice History Detail]![HDTAMT],0)) AS OTHER_MISC, Sum(IIf([Invoice History Detail]![HDCTYP]="25" Or [Invoice History Detail]![HDCTYP]="29" And [HDTdes] Like "*TAX*",[Invoice History Detail]![HDTAMT],0)) AS [Current Taxes], [Current StdMo Chg]+[Transportation Cost/Haul]+[Disposal Cost/Ton]+[Current Taxes]+[OTHER_MISC] AS TOTAL, Sum([Invoice History Detail].HHIAMT) AS STORE_TOTAL, [Invoice History Detail].HDIDDT, [Invoice History Detail].HDSLVL AS [Monthly QTY of TONS], [Invoice History Detail].HDCTYP, [Invoice History Detail].HHNANO
    FROM [Invoice History Detail] LEFT JOIN USBancorp ON [Invoice History Detail].CUUFL2 = USBancorp.[Site ID]
    GROUP BY [Invoice History Detail].HDCUNO, [Invoice History Detail].HHSLOC, [Invoice History Detail]![CUUFL2], [Invoice History Detail]![HHNAME], [Invoice History Detail]![CUADR1], [Invoice History Detail]![CUCITY], [Invoice History Detail]![CUSTAT], [Invoice History Detail]![CUPOST], USBancorp.[Company No], USBancorp.CostCenter, USBancorp.ContainerOwnership, [Invoice History Detail]![HDCTQT], [Invoice History Detail]![HDCTSZ], [Invoice History Detail]![HDVLCD], [Invoice History Detail].HDPUDY, [Invoice History Detail].HDSYCD, [Invoice History Detail].HDIDDT, [Invoice History Detail].HDSLVL, [Invoice History Detail].HDCTYP, [Invoice History Detail].HHNANO
    HAVING ((([Invoice History Detail].HHNANO)="440"));
    Sorry it's such along SQL
    texasalynn
    It's AWL Good!

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    there are several ! where i did not expect them. e.g.
    [Invoice History Detail]![CUUFL2]
    ...and many similar: maybe it can work like that, but i am not used to it.

    parentheses?
    Left([HDCUNO],3)="001" And Left([HDSYCD],1)<>"1") Or ((Left([HDCUNO],3)="002" And Left([HDSYCD],1)<>"7") And (Left([HDCUNO],3)="002" And Right([HDVLCD],1)<>"R")
    ...i firmly believe in explicit parenthetisation:
    ((this) And ((that) Or ((other) And (else))))
    your current expression is in the arbitrary hands of the dogs at redmond.

    ...poss datatype confusion in the same expression:
    HDSYCD is text (why?????) or numeric (then why "7")
    ditto other ??possibly numeric fields.

    if it's none of these, we can dig deeper.

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Jun 2002
    Location
    Houston, TX
    Posts
    116
    Quote Originally Posted by izyrider
    there are several ! where i did not expect them. e.g.
    [Invoice History Detail]![CUUFL2]
    ...and many similar: maybe it can work like that, but i am not used to it.

    parentheses?
    Left([HDCUNO],3)="001" And Left([HDSYCD],1)<>"1") Or ((Left([HDCUNO],3)="002" And Left([HDSYCD],1)<>"7") And (Left([HDCUNO],3)="002" And Right([HDVLCD],1)<>"R")
    ...i firmly believe in explicit parenthetisation:
    ((this) And ((that) Or ((other) And (else))))
    your current expression is in the arbitrary hands of the dogs at redmond.

    ...poss datatype confusion in the same expression:
    HDSYCD is text (why?????) or numeric (then why "7")
    ditto other ??possibly numeric fields.

    if it's none of these, we can dig deeper.

    izy
    Ok I will look at the ! and make all work the same (not sure that will make a difference - but I'm no expert) I'm all for possibilities - thanks

    The HDSYCD is a text field. The other suggest on explicit is good and I thought I had so I will re-review those.

    I'll post back after looking at those things. Thanks again for the review and input
    texasalynn
    It's AWL Good!

  10. #10
    Join Date
    Jun 2002
    Location
    Houston, TX
    Posts
    116

    Solved: No current record

    Ok it has been solved. The field I was trying to add was a "Yes/No" field but nothing had been added for the rows of data in the table. Don't fully understand why that would matter. But I went back to the table and changed the field type to "Text" and it worked just fine.
    texasalynn
    It's AWL Good!

Posting Permissions

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