Programming in Access

[Alan]

I'm going to set myself up for a big fall here! If you have questions about Access, post 'em here. Intermediate to advanced Access (without using VBA) is well covered by lots of books out there so I don't want to deal with those (too hard in a forum TBH)

But if you are working with VBA and DAO (sorry - not ADO) get peculiar error messages - particularly error 3061, post here and I'll see if I can help.

I'll post later with an example of a problem that people can get when using a SQL statement to return a record set based upon one or more parameters.

[Alan]

You're using a function either to open a recordset or to carry out an ApplyFilter command and rather than use a query, you want to use a SQL statement. Either way, you have a WHERE clause in the SQL string. that refers to an open form i.e.

DoCmd.ApplyFilter "Select Table1.Field1, Table1.Field2 FROM Table1 WHERE (((Table1.Field1)= Forms!FormName!Control1));"

It won't work properly. With certain combinations of O.S. and certain .dll files it will work the first time but not a second time.

The main reason is that the field being tested is within a string (labelled by the use of ") and Access can't determine the data type within the literal string.

So you have to concatenate the variable in the WHERE clause with the string which makes up the SQL statement. If the variable being tested is a number, change the statement to:

"... WHERE (((Table1.Field1)= " & Forms!FormName!Control1 & "));"

If the variable being tested is itself a string use:

"... WHERE (((Table1.Field1)= """ & Forms!FormName!Control1 & """));"

Explanation was a bit garbled but the solution is spot on.:D

[Alan]

Usually, if you forget the extra quotation marks you will get an error 3061 - unexpected missing parameter - 1

In certain circumstances, which I've never bottomed out, even using the right quotation marks will not make it work and the solution then is to write a normal query and use the query instead of the SQL statement.