Search results for query: *

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...

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?

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 =...

BAM!!

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#] + ') ' &...

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...

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] &...

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...

Thank you Duane!

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...

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...

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...

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, *...

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...

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)

Hoping someone can help with this wild and wooly IIF statement.. fields in table: 1aPN, 1aSN 1bPN, 1bSN 2aPN, 2aSN 2bPN, 2bSN I was using this statement in my query when I was only capturing info for 2 parts... Part Info: IIf([1aPN]="NONE","NONE",IIf([1aPN] Is Null,"",IIf([2aPN] Is...