SQL Syntax Error 1

[wvSusie]

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] & "'",Chr(13) & Chr(10))

Thank you!

 

[PHV]

Install Info3: Concatenate("SELECT [Issue#] & ') INSTL''D: ' & [InstallDate] & IIf(IsNull([PartTOW]),[!]''[/!], '; TOW: ' + [PartTOW]) FROM tblCompAnal WHERE ExHistID = '" & [ExHistID] & "'",Chr(13) & Chr(10))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

Another way:
Install Info3: Concatenate("SELECT [Issue#] & ') INSTL''D: ' & [InstallDate] & '; TOW: ' + [PartTOW] FROM tblCompAnal WHERE ExHistID = '" & [ExHistID] & "'",Chr(13) & Chr(10))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

I believe the most recent suggestion from PH matches the syntax I suggested to wvSusie a day or so ago. It uses the + rather than & to propogate the Null and remove the "label" portion of the string.

Duane
Hook'D on Access
MS Access MVP

 

[wvSusie]

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, Concatenate("SELECT [Issue#] + ') ' & [PartName] & '; P/N:' + [PartNbr] & '; TOW:' + [PartTOW] & Chr(13) & Chr(10) & 'UNS RMVLS:' & Chr(13) & Chr(10) & [PartUnsRemTY] & Chr(13) & Chr(10) & [PartUnsRemTY-1] & Chr(13) & Chr(10) & [PartUnsRemTY-2] FROM tblCompAnal WHERE ExHistID = """ & [ExHistID] & """",Chr(13) & Chr(10)) AS [Component Info]
FROM tblExHist;

returns..

1A) COOLING FAN; P/N: 29680; TOW: 2601
UNS RMVLS:
7
7
11
1B) THERMAL SWITCH; P/N: xxx0
UNS RMVLS:



1C) CIRCUIT BREAKER; P/N: xxxx-066-20
UNS RMVLS:



1D) FLOW SWITHC ASSY; P/N: Pxxx583; TOW: 81
UNS RMVLS:
6
7
10


Issue 1) Can I use a field in tblExHist in the subquery below?

Component Info: Concatenate("SELECT [Issue#] + ') ' & [PartName] & '; P/N:' + [PartNbr] & '; TOW:' + [PartTOW] & Chr(13) & Chr(10) & 'UNS RMVLS:' & Chr(13) & Chr(10) & [PartUnsRemTY] & Chr(13) & Chr(10) & [PartUnsRemTY-1] & Chr(13) & Chr(10) & [PartUnsRemTY-2] FROM tblCompAnal WHERE ExHistID = """ & [ExHistID] & """",Chr(13) & Chr(10))

I would like to prefix [PartUnsRemTY] with the appropriate year.. The year comes from the [ExDate] field in tblExHist.

Example:
Format([tblExHist].[ExDate],"yyyy") & ": " & [tblCompData].[PartUnsRemTY] & Chr(13) & Chr(10)

Format(DateAdd("yyyy",-1,[tblExHist].[ExDate]),"yyyy") & ": " & [tblCompData].[1aPartUnsRemTY-1] & Chr(13) & Chr(10)

Format(DateAdd("yyyy",-2,[tblExHist].[ExDate]),"yyyy") & ": " & [tblCompData].[1aPartUnsRemTY-2] & Chr(13) & Chr(10)


Issue 2) If no data in [PartUnsRemTY], do not add the data or UNS RMVLS prefix?

Ultimately, I need the result to look like...

1A) COOLING FAN; P/N: 29680; TOW: 2601
UNS RMVLS:
2012: 7
2011: 7
2010: 11
1B) THERMAL SWITCH; P/N: xxx0
1C) CIRCUIT BREAKER; P/N: xxxx-066-20
1D) FLOW SWITHC ASSY; P/N: Pxxx583; TOW: 81
UNS RMVLS:
2012: 6
2011: 7
2010: 9

Any help would be greatly appreciated!

 

[dhookom]

You can try something like:

Component Info: Concatenate("SELECT [Issue#] + ') ' & [PartName] & '; P/N:' + [PartNbr] & '; TOW:' + [PartTOW] & Chr(13) & Chr(10) & 'UNS RMVLS:' & Chr(13) & Chr(10) & " & Year(PartsUnsRemTY) & "': ' & [PartUnsRemTY] & Chr(13) & Chr(10) & [PartUnsRemTY-1] & Chr(13) & Chr(10) & [PartUnsRemTY-2] FROM tblCompAnal WHERE ExHistID = """ & [ExHistID] & """",Chr(13) & Chr(10))

I'm not sure about the other question. You might need to provide the raw data using TGML in a fixed font so we can get a better idea what you need.

Duane
Hook'D on Access
MS Access MVP

 

[wvSusie]

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#] + ') ' & [PartName] & '; P/N:' + [PartNbr] & '; TOW:' + [PartTOW] & Chr(13) & Chr(10) & 'UNS RMVLS:' & Chr(13) & Chr(10) & Year(tblExHist.[ExDate]) & ': ' & [PartUnsRemTY] & Chr(13) & Chr(10) & [PartUnsRemTY-1] & Chr(13) & Chr(10) & [PartUnsRemTY-2] FROM tblCompAnal WHERE ExHistID = """ & [ExHistID] & """",Chr(13) & Chr(10))

...but it gives me a run-time error 3061. Too few parameters. Expected 1.

sorry, but i don't know how to "provide the raw data using TGML".. however, the data does appear in my query results exactly as it does above.

 

[PHV]

Replace this:
& Year(tblExHist.[ExDate]) &
with this:
& " & Year([ExDate]) & " &

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[wvSusie]

BAM!!