Is ANSI 92 affecting my query parameters?

[Whizzo]

Hi all;
Here's one for you. I have a form open (let's call it frmMyForm) which contains a text box (txtMyText). The contents of this text box is passed to a parameter query as:

[forms]![frmMyForm]![txtMyText]

Exactly as it says so in the book, so to speak. But it's not working. It seems to be looking at the form, at least it doesn't ask me to enter the value manually, but the query doesn't return any results.

I can manually type the value I'm looking to pass over straight into the criteria box and it works. I can use [Enter parameter value:] in the criteria section, the dialog pops up, I enter the parameter there, and it works. But getting the value from a form (yes, the form is open at the time) doesn't. The only thing I can think of is that the database is set to ANSI-92 syntax, could this have something to do with it?

Thanks all, this is driving me nuts!

[Whizzo]

Sorry folks, that was a fib. When I put the criteria in as

[forms]![frmMyForm]![txtMyText]

a dialog pops up at runtime and asks me for the very same parameter that I'm trying to pass, i.e. the string in txtMyTest. Maybe that helps?

[FishVal]

Post your query SQL.

[Whizzo]

Haha, you asked for it!

SELECT DISTINCT [perscustforename] & " " & [PersCustSurname] & [BUSCompanyname1] & " " & [Buscompanyname2] AS Name, tblCustomers.CustID, tblCustomers.TotalExposure, tblCustomers.SumAssured, tblPolicies.PolicyNumber, tblPolicies.PolicyIssuerName, tblPolicies.PolicyHolder, tblPolicies.SumAssuredValue, tblPolicies.CollStatus, tblPolicies.MaturityDate, DateDiff("yyyy",#5/5/2009#,[MaturityDate]) AS [Term Remaining], tblPolicyCompaniesData.PolicyNumber AS [COMP Policy Number], tblPolicyCompaniesData.PolicyCompany AS [COMP Policy Issuer], tblPolicyCompaniesData.PolicyStatus AS [COMP Policy Status], tblPolicyCompaniesData.PolicyMaturityDate AS [COMP Policy Maturity], IIf([PolicySumAssuredLife1]>[PolicySumAssuredLife2],[PolicySumAssuredLife1],[PolicySumAssuredLife2]) AS [COMP Sum Assured], [policyholdername1] & " " & [policyholdername2] AS COMPPolicyHolderNames, tblPolicyCompaniesData.PolicyStatus AS COMPPolicyStatus, IIf(IsNull([PolicyBanksInterestNoted?] And tblPolicyCompaniesData!PolicyNumber),"NO",[PolicyBanksInterestNoted?]) AS [Noted?], DateDiff("yyyy",#5/5/2009#,tblPolicyCompaniesData!PolicyMaturityDate) AS [COMP Term Remaining], Max(tblAccounts!AdvisorBnos & " " & tblAccounts!StaffAssignedToAdvisor) AS [Advisor details], tblCustomers.CustTypeRefined, Max(tblAccounts.AcctHoldingBranch) AS MaxOfAcctHoldingBranch
FROM (tblCustomers LEFT JOIN tblAccounts ON tblCustomers.CustID = tblAccounts.CustID) LEFT JOIN (tblPolicies LEFT JOIN tblPolicyCompaniesData ON tblPolicies.PolicyNumber = tblPolicyCompaniesData.PolicyNumber) ON tblCustomers.CustID = tblPolicies.CustID
GROUP BY [perscustforename] & " " & [PersCustSurname] & [BUSCompanyname1] & " " & [Buscompanyname2], tblCustomers.CustID, tblCustomers.TotalExposure, tblCustomers.SumAssured, tblPolicies.PolicyNumber, tblPolicies.PolicyIssuerName, tblPolicies.PolicyHolder, tblPolicies.SumAssuredValue, tblPolicies.CollStatus, tblPolicies.MaturityDate, DateDiff("yyyy",#5/5/2009#,[MaturityDate]), tblPolicyCompaniesData.PolicyNumber, tblPolicyCompaniesData.PolicyCompany, tblPolicyCompaniesData.PolicyStatus, tblPolicyCompaniesData.PolicyMaturityDate, IIf([PolicySumAssuredLife1]>[PolicySumAssuredLife2],[PolicySumAssuredLife1],[PolicySumAssuredLife2]), [policyholdername1] & " " & [policyholdername2], tblPolicyCompaniesData.PolicyStatus, IIf(IsNull([PolicyBanksInterestNoted?] And tblPolicyCompaniesData!PolicyNumber),"NO",[PolicyBanksInterestNoted?]), DateDiff("yyyy",#5/5/2009#,tblPolicyCompaniesData!PolicyMaturityDate), tblCustomers.CustTypeRefined, tblAccounts.AdvisorBnos, tblAccounts.StaffAssignedToAdvisor
HAVING (((Max(tblAccounts.AcctHoldingBranch))=[Forms]![frmReportGenerator]![cboBranchName]))
ORDER BY tblCustomers.CustID;

I can do basic modifications to SQL code but I'm still a noob when it comes to writing fresh stuff for myself. I'm still using the Query Builder in Access I'm afraid. I take it it's the HAVING section that's not working properly? The parameter in the Builder is expressed exactly how Microsoft and pretty much everywhere else I've looked tells me it should be, so I wonder what the problem is? As I said, the DB is using ANSI 92 so there may be subtle differences in syntax to the standard Access SQL. Any ideas?

[FishVal]

Ok.

Post please:

• tblAccounts.AcctHoldingBranch field parameters (particularly datatype)
• [Forms]![frmReportGenerator]![cboBranchName] properties: RowSource, ColumnCount, BoundColumn

[Whizzo]

Here's your info FishVal, thanks for taking in interest!

tblAccounts.AcctHoldingBranch field is a:
Text field, all characters forced to uppercase.

cboBranchName row source is:

SELECT tblBranchList.[Sort Code], tblBranchList.[Branch Name] FROM tblBranchList ORDER BY [Branch Name];

Bound col. 1
Table col 1. Sort code
Table col 2. Branch name

Interesting. Hadn't thought of that...

[Whizzo]

And there it is. I tried the criteria with a text box instead of a combo and it worked. Then I changed the bound column for the combo box and it started behaving itself. Happy days! I always thought that if you addressed a control in a form it always picked the data from the control and ignored bound columns etc. Ah well, one lives and learns. Thanks folks!

[FishVal]

You are welcome.

Best regards,
Fish.

[NeoPa]

@Whizzo
I think to clarify, it would help to explain that the Sort code is the value being checked in the SQL, as it is the bound column of the ComboBox control.

I expect that the only column visible to the operator is the Branch name, so naturally the assumption is that it would compare it with that. No!

This is a problem that's fallen over frequently, and is even more confusing when ComboBoxes are used to show fields within the design of a table.

[NeoPa]

In case it helps anyone finding this thread looking for ANSI-92 related problems, here are a few relevant links :
ANSI Standards in String Comparisons
Quotes (') and Double-Quotes (") - Where and When to use them
Using "&" and "+" in WHERE Clause