I am trying to build an append query in VBA and am stuck on one of the columns where I want to use an IIF statement to determine what string goes into each row. The column is a column of test results and if the comparator for the result is the equal sign, I want the column just to show the result. If the comparator is the less than sign, I want the column to show the comparator and the result (<5, etc).
I got help long ago from here to build the query in the design window and it works great there. However when I try to build the SQL string in VBA it does not work. Instead of returning the string I am looking for to fill the column, the IIF statement is being evaluated as FALSE and messing up my string build.
Here is my Select statement. I have tried many combinations of single quotes, double quotes around the = sign and so far nothing will work. Help is greatly appreciated. I have wasted about 6 hours just on this.
strSelect = _
"SELECT " & _
"tbl101Sites.SiteID, " & _
"tbl101Sites.SiteName, " & _
"tbl110SamplePoints.SamplePointName as SampleName, " & _
"tblSamplesLIMS.SampledDateTime as SampleDate, " & _
"tblParametersBetzmanLIMS.BetzmanParameterName & tblParametersBetzmanLIMS.Units as ParameterName, " & _
"IIf([Comparator] = " = ", [Result], [Comparator] & ' ' & [Result]) AS Results, " & _
"tblSampleResultsLIMS.Result/(lu_tblAmineN.NitrogenCount*14) AS ResultAsN, " & _
"tbl110SamplePoints.SampleSort, " & _
"tblSampleGroups.SampleGroupID, " & _
"lu_tblAmineN.MoleWeight/(lu_tblAmineN.NitrogenCount*14) as N_Factor, " & _
"tblParametersBetzmanLIMS.ParameterDefaultSort "
Debug.Print "strSelect = " & strSelect
"SELECT " & _
"tbl101Sites.SiteID, " & _
"tbl101Sites.SiteName, " & _
"tbl110SamplePoints.SamplePointName as SampleName, " & _
"tblSamplesLIMS.SampledDateTime as SampleDate, " & _
"tblParametersBetzmanLIMS.BetzmanParameterName & tblParametersBetzmanLIMS.Units as ParameterName, " & _
"IIf([Comparator] = " = ", [Result], [Comparator] & ' ' & [Result]) AS Results, " & _
"tblSampleResultsLIMS.Result/(lu_tblAmineN.NitrogenCount*14) AS ResultAsN, " & _
"tbl110SamplePoints.SampleSort, " & _
"tblSampleGroups.SampleGroupID, " & _
"lu_tblAmineN.MoleWeight/(lu_tblAmineN.NitrogenCount*14) as N_Factor, " & _
"tblParametersBetzmanLIMS.ParameterDefaultSort "
Debug.Print "strSelect = " & strSelect
__._,_.___
Posted by: david.pratt@outlook.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (1) |
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
SPONSORED LINKS
.
__,_._,___
Tidak ada komentar:
Posting Komentar