David - sometimes queries misbehave. See Sarah's post. Unfortunately, Access has the SQL plus whatever is stored in the properties box. This can override the plain SQL without you knowing it.
We'll be glad to offer insight should you need it.
Regards,
Bill Mosca
------ Original message------
From: david.pratt@outlook.com [MS_Access_Professionals]
Date: Tue, Jul 10, 2018 3:55 PM
Cc:
Subject:[MS_AccessPros] Re: IIF Statement in Building VBA Query
Please ignore this question. Now it is working with the single quotes. Not sure what I am doing at this point I am too frustrated to think clearly and don't want to use up anyone's time until I get a clear question.
---In MS_Access_Professionals@yahoogroups.com, <david.pratt@...> wrote :
Oh, the spaces around the = sign don't belong there. I somehow got them in there when I was playing around with quote marks of various kinds and numbers...
---In MS_Access_Professionals@yahoogroups.com, <david.pratt@...> wrote :
---In MS_Access_Professionals@yahoogroups.com, <david.pratt@...> wrote :
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: wrmosca <wrmosca@comcast.net>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (4) |
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