Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Recent content by wvSusie

  1. wvSusie

    Update query - key violations?

    Hi lameid! tblExHist is the one side; tblCompData is the many. ExHistID is PK in tblExHist and is the field in tblCompData that the relationship is set on. A few other fields have indexes but they all allow for duplicates, and there are no multi-field indexes. I turned on Referential...
  2. wvSusie

    Update query - key violations?

    I've determined the issue has to do with a one-to-many relationship between tblExHist and tblCompData. If I select Enforce Referetial Integrity to Cascade Delete Related Records, I get a key violation for each record in tblExHist where there's a related record in tblCompData. Any ideas?
  3. wvSusie

    Update query - key violations?

    I've been running an update query for months without issue. Today I started getting key violation errors and it will not perform the update. UPDATE tblExHist RIGHT JOIN tmpEXCPTDWN ON tblExHist.ExHistID = tmpEXCPTDWN.ExHistID SET tblExHist.ExHistID = tmpEXCPTDWN!ExHistID, tblExHist.ExDate =...
  4. wvSusie

    SQL Syntax Error

    BAM!!
  5. wvSusie

    SQL Syntax Error

    Thanks for the response, but my question is about getting the "year" from a field in tblExHist. I would like to prefix [PartUnsRemTY] with the appropriate year.. The year comes from the [ExDate] field in tblExHist. I tried this... Component Info: Concatenate("SELECT [Issue#] + ') ' &...
  6. wvSusie

    SQL Syntax Error

    d- i agree.. sorry, but i didn't notice the +, and didn't realize what it was actually doing until i saw phv's post. I've been using the concatenate code with much success, thank you for sharing! but now i've hit another road block... my query SQL: SELECT tblExHist.ExHistID...
  7. wvSusie

    SQL Syntax Error

    I know I'm missing some quotes in/around the IIf statement.. Would someone please show me the correct syntax? Install Info3: Concatenate("SELECT [Issue#] & ') INSTL''D: ' & [InstallDate] & IIf(IsNull([PartTOW]),"", '; TOW: ' + [PartTOW]) FROM tblCompAnal WHERE ExHistID = '" & [ExHistID] &...
  8. wvSusie

    Another Nested IIF Statement ?

    Install Info: Concatenate("SELECT [Issue#] & ') ' & [InstallDate] & '; TOW:' & [PartTOW] FROM tblCompAnal WHERE ExHistID = """ & [ExHistID] & """",Chr(13) & Chr(10)) returns... 1A) 10/11/03; TOW:2681 1B) ; TOW: 1C) ; TOW:EI COMPONENT - NOT TRACKED 1D) 05/01/11; TOW:247 I would like it to...
  9. wvSusie

    Another Nested IIF Statement ?

    Thank you Duane!
  10. wvSusie

    Another Nested IIF Statement ?

    Duane - In the debug window, I get... 1A, PN: FAN, RACK COOLING, P/N:xxxxxx, S/N:xxx 1B, PN: FAN THERMAL SWITCH, P/N:xxxx, S/N:xxx 1C, PN: EXHAUST FLOW CIRCUIT BREAKER, P/N:xxx-xxx-xx, S/N:xxxx 1D, PN: FLOW SWITCH ASSY, P/N:xxxx-xx, S/N:xxxx ...which looks good. The issue now is the query...
  11. wvSusie

    Another Nested IIF Statement ?

    I currently have the SQL statement in a query (SQL view). I now see that it is actually returning the correct values in PartData, however the query results also include every record in tblExHist along with every field in that table. I assumed it wasn't returning anything in PartData because I...
  12. wvSusie

    Another Nested IIF Statement ?

    Thanks for the quick response! ExHistID is text. Using.. SELECT Concatenate("SELECT [Issue#] & ', PN: ' & [PartName] & ', P/N:' & [PartNbr] & ', S/N:' & [PartSerNbr] FROM tblCompAnal WHERE ExHistID = """ & ExHistID & """" ,Chr(13) & Chr(10)) AS PartData, * FROM tblExHist ; the query returns...
  13. wvSusie

    Another Nested IIF Statement ?

    Hi Duane, I'm still trying to get this to work, but not having much joy. Am I on the right track? SELECT Concatenate([tblCompAnal].[Issue#] & ", PN: " & [tblCompAnal].[PartName] & "', P/N:" & [tblCompAnal].[PartNbr] & ", S/N:" & [tblCompAnal].[PartSerNbr],Chr(13) & Chr(10)) AS Part Data, *...
  14. wvSusie

    Another Nested IIF Statement ?

    hi duane, yes. but, all the part data must be concatenated into one cell/field for reports and export to excel. i considered your code for concatenating records from a one-to-many. but my VBA skills are "beginner" at best. i could not figure out how i'd get the additional formatting that is...
  15. wvSusie

    Another Nested IIF Statement ?

    Yes, those are 8 of about 30 fields in the table. No relation, other than its the part # and serial # for each of up to four parts - 2 for "issue" 1 (1a,1b), and 2 for "issue" 2 (2a,2b)
See more

Part and Inventory Search

Back
Top