By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,067 Members | 1,227 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,067 IT Pros & Developers. It's quick & easy.

Is ANSI 92 affecting my query parameters?

P: 44
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:

Expand|Select|Wrap|Line Numbers
  1. [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!
Apr 17 '09 #1
Share this Question
Share on Google+
9 Replies


P: 44
Sorry folks, that was a fib. When I put the criteria in as
Expand|Select|Wrap|Line Numbers
  1. [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?
Apr 17 '09 #2

FishVal
Expert 2.5K+
P: 2,653
Post your query SQL.
Apr 17 '09 #3

P: 44
Haha, you asked for it!

Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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
  3. 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
  4. HAVING (((Max(tblAccounts.AcctHoldingBranch))=[Forms]![frmReportGenerator]![cboBranchName]))
  5. 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?
Apr 17 '09 #4

FishVal
Expert 2.5K+
P: 2,653
Ok.

Post please:
  • tblAccounts.AcctHoldingBranch field parameters (particularly datatype)
  • [Forms]![frmReportGenerator]![cboBranchName] properties: RowSource, ColumnCount, BoundColumn
Apr 17 '09 #5

P: 44
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:
Expand|Select|Wrap|Line Numbers
  1. 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...
Apr 17 '09 #6

P: 44
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!
Apr 17 '09 #7

FishVal
Expert 2.5K+
P: 2,653
You are welcome.

Best regards,
Fish.
Apr 17 '09 #8

NeoPa
Expert Mod 15k+
P: 31,709
@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.
Apr 17 '09 #9

NeoPa
Expert Mod 15k+
P: 31,709
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
Apr 17 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.