Search results

I have a continuous form where the field "Notes" is reliant on the results of a few tests done on other fields. All of the test need to be done on each record as the form loads. These tests include checking for no data in a field, dcount a list for duplicate entries and comparing the entries in...

My original code was missing the "HAVING" clause and this was causing it not to run but was also creating no error message. My original code. Public Function getCollSearch() As String sQry = "SELECT herbarium_collection.Collector " & _ "FROM Herbarium_Collection " & _...

First, an apology. I posted the wrong function code. By aggregate in the SELECT statement I assume you mean DISTINCT. I've tried this and there is no return from the functions. The first of the two images below show the result of modifying the code as you suggested. The second is the result...

When the blue button is pressed, the "Family" value is set to "" which, in turn, sets all the other controls to ""

<Why are you using ANSI92 SQL syntax? Is there any benefit?> What is that and what is the alternative? My knowledge of SQL is very limited. Thank you for your reply David. As for your suggestion, nothing worked. In the "getMColl" function, which was working, using "DISTINCT" and "WHERE"...

I have a series of functions that return sql strings as variable rowsources depending on requirements. For the most part these functions perform as expected except for one that refuses to work, "getCollSearch". see the code below. When replaced by a query with the same sql the control performs...

I use an external drive for for developing and it had only just been connected to the laptop for the first time when this happened. I have since had it disconnected and on re-connection all is good. Access being consistently inconsistent. Thank you for the reply. John

Hi Doc_Man. I'm having a similar problem on a laptop in develop mode with no one else connected and open shared. Deleting the laccdb file says unable to deleted because the file is open in Access. The file was closed and Access closed. retry with the same message if I try to modify code or a...

Thank you for your offer of help and, in particular, for recognising that it is my application to manage. John

Tell me what field names need to be clarified and I'm happy to do that. I have added 60 or so records to the database. Bear in mind that not all fields are used in every record and some are rarely used but required for consistency, none the less. It does seem, however, that nobody listens to...

I've been away for a while so apologies for the slow response. Attached is a sample of one of the tables. I have included some data from the beginning and end of the table. NOTE. I have changed my email address to segref23@gmail.com.

Plog. Firstly, normalised, or not. Separate tables, or not. It is what it is and will remain that way as it is the way I was instructed to do it. I understand normalisation and how it works and if you're a purist you will go to any lengths to accomplish this. There are, however, many ways to...

It seems wrong but the sub is called "ssetSources" not "GetSources". It is as it should be. sTable is a private variable with form scope.

Point of interest here. I replaced the "HAVING" clauses to "WHERE" and nothing works, changed them back again and it's all good. John

Thanks for the reply Plog. The similar table structures are due to a requirement to keep the data from separate sources in their own tables and the data differs greatly from one source to the next, albeit there are a few common fields. --Herbarium_Collection -- I don't know where you got the...

I have created a series of sql strings from stored queries. The object being to reduce the number of queries doing essentially the same thing with a different table. The sql strings utilise " " & sTable & " as A " construct. In the code below,the functions getMFamily, getMGenus and getMColl...

I find using the alternate "A." is the best of both worlds

Thank you both. That clears things up for me. I only ever use standard characters in my table names and have removed all of the spaces. John

I am slowly coming to grips with SQL but this is bugging me. I have resorted to using the following when using a variable in the from clause. "FROM [" & sTable & "] as A " & _ Which seems a bit clunky. Sometimes, but not always this seems to work "FROM sTable as A " & _ and other times...

Thanks Docman. Looks like I've a bit of reading to do.