Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.
Allen Browne is for sure one of the top experts in Access - but that doesn't mean that everything is right what he says...:-)
The article describes only Form references used as parameter - I'm sorry to say that, but that is a complete misunderstanding of the correct usage of parameters in a query. The reason is the error which he gets which he describes as bug - I would describe it as "wrong way of using it", like with your Interface problem.
1.) A query, especially a parameterized query, should simply NEVER contain any reference to an object which has nothing to do with SQL, except VBA functions. A control reference is one of the really badest things MS allowed in Access, there is no other SQL based database which allows you to do that and you will get in heavy problems if you ever need to upgrade your backend from Access to a normal database server.
The thing is: You create a hard link between a form and a query. If the form is not open, the query fails. If the form is renamed (and autorenaming is off like recommended from many experts), the query is broken. If the query should be upsized it fails again as no database server can work with such references.
The point is: It is unnecessary in ALL cases (my opinion). You see that if you work a while with ADP and SQL Server where you can't use queries with control references. It's always possible to do it without such references. I always try to keep the objects (forms, reports, class modules, modules, queries and so on) completely independent from each other.
2) a control value is nothing else than a variant. You can enter any type of value into it and also the NULL value is possible. You can declare a type in the PARAMETERS list which seems to be only the expected type but can (in opposite to VBA) also contain NULL, so SQL data types are always the specified type plus the possibility that it can also be NULL (in VBA you can declare a string, but it can't get the NULL value). That's important in future cases when you use any other database, too.
3) Allen says that's a bug if the control value will be converted to a "", I say, in the way he designed the query, it's more a bug that it works with a long value.
Let's look into his query:
erID] Text (255);
omerID] Is Null)
OR (Orders.CustomerID = [Forms].[Form1].[txtCustom
Open in new window
So he defines that a control on a form (which has nothing to do with a SQL datatype) should be handled as Text. Then the query says "return to me all records where the contents of the form's control is NULL" - when should that be true? Always, if the field is empty (NULL). So I would expect to get all records as you say "WHERE True Or ...". As the textbox control indeed returns "" instead of NULL it works this way if you use "WHERE (([Forms].[Form1].[txtCust
omerID] ="")". So that's pure nonsense - sorry,Allen.
You can't decide if the textbox is really NULL or "", you don't see that, in both cases you have an empty field on the form. So to handle both, you must use Nz. As the field which should be compared, can be NULL (or, depending on the definition of the field in the table), can also be "". So the only really meaningful way of comparing these two is this:
WHERE Nz(Orders.EmployeeID) = Nz([Forms].[Form1].[txtEmp
Open in new window
As that works in any case you simply don't need the PARAMETERS list, and this is also another surprising thing: That MS programmers allowed to do such things.
4) Only use variable names as parameters!!
A really meaningful way of using the PARAMETERS list is to define variables which then can be used outside of the query like this:
PARAMETERS strMyTextParam Text ( 255 ), datMyDateParam DateTime, bolMyBooleanParam Bit;
WHERE Table1.Test = strMyTextParam
AND Table1.Test2 = datMyDateParam
AND Table1.Test3 = bolMyBooleanParam;
Open in new window
Now wherever you need to call this query, you can fill the parameters using their names and the Parameters collection of the QueryDef object. That's by the way the same method you would use to fill parameters of a stored procedure in ADO. The best thing with this method is that you don't need to think about if you must use #, ' or nothing - always the value will be assigned to a parameter, nothing else.
So a parameter list is very useful, you have a typed variable and you can assign that with the Parameters collection.
5) As JDettman said earlier: Nz always returns a string to the query, so that has nothing to do with a calculated field. You must use a convert function to convert it back to the wanted type. So again, no points, Mr. Browne...:-)
6) the rest of the list is really no argument for not using a saved query. The number of brackets for example is irrelevant, add as many as you want as long as the logic is not changed - and that was never the case in all the time I used Access. "Running out of rows in the designer" - I would not have thought that someone like Allen has a problem with this, as he should be able to simply change the SQL command...:-)
That the SQL editor of Access is really trash is not new, I don't think MS will ever change that (keep the professional possibilities bad to push other MS products...). The target group for Access is an Office user and he would never use the SQL view...:-)
A saved query is always MORE efficient as a dynamic SQL.
Join the community of 500,000 technology professionals and ask your questions.