Replies

You know, I think that could be what I intended to to in the first place.

Any idea how unhappy Access would be with a table with approx 170 fields – hopefully, the fields wouldn’t contain much text and there’s a fair chance that half or more of them would be empty.

We’ve got to potentially accomodate 32 plot numbers though, meaning 32 x 5 fields.

The second table you describe would still have 70 fields (i.e most of the data). Most of the fields would be defined as very short text fields, storing one or two word answers/phrases or 2 character text codes.

I guess I was wondering whether it was worth splitting the questions into groups. I can’t see what that would achieve though really, it would just be a lot more work.

A Totals query on the Keywords table seems to be working ok.

Next problem is allowing for multiple single quotes in the keyword ‘words’ – that for another thread though

Thanks again

Thanks again.

I’m most of the way there but I think I might need to allow to the possibility of single quotes in the words (keywords) but I’ve started another thread for that one!

Not sure, I’ve got horribly confused.

I only want the filter to return Documents containing any of the keywords picked, not all of them so its either lots of ‘ORs’ or an ‘IN’.

As I said before the SQL statement worked where the ‘Keywords’ field was an actual field (in the Documents table) not a calculated one in a query (using the concat function on tblKeywords)

(the documents and keywords tables have a one to many relationship)

Edit:

I guess I should be using a totals query instead (i.e. using the raw keyword data, the the concatenated output), something like

SELECT tblFiles.FileName
FROM tblFiles INNER JOIN tblFileKeywords ON tblFiles.FileID = tblFileKeywords.FileID
WHERE (((tblFileKeywords.Keyword) In (“BLUE”,”ORANGE”,”INDIGO”,”RED”)))
GROUP BY tblFiles.FileName;

Thanks Hans, that’s not quiet what I’m doing.

I have used the Concat function to populated a calculated field in a query ‘qryDocumentsWithKeywords’. I’m attempting to use this query, filtering the records returned by added a where clause using ‘IN’, to populate a list box.

Using

SELECT FeatureGroup, Report, DocumentType, Feature, Filename FROM qryDocumentsWithKeywords WHERE
Keywords IN (“BLUE”,”GREEN”,”INDIGO”) ORDER BY FeatureGroup, Report, DocumentType, Feature, Filename

returns no records

Cheers,

I seem to have got that working fine, just a small hitch on accessing the data in another query.

I have query comprising a list of documents with a keywords field (populated by the concat function).

I’m trying to constuct some sql to form the control source for a list box (listing documents), essentially I want to add a where clause on the keywords. The where clause is constructed by looping through items in another listbox and populating the bit between the brackets of an ‘IN’ statement e.g. (“BLUE”,”GREEN”,”INDIGO”)

This worked where the keywords were typed in a memo field but not now where they are calculated field.

This is the SQL for the documents list box

strSQL = “SELECT FeatureGroup, Report, DocumentType, Feature, Filename FROM qryDocumentsWithKeywords WHERE ” _
& vbCrLf & “Keywords IN (” & strIn & “)”

Do you know if this (using ‘IN’) just won’t work here or if I’ve got something wrong?

Thanks

I seem to be getting an error if I try to add a record for the first time (as I’m already on a new record I guess)

‘RunCommand acCmdRecordsGoToNew not available’

Edit.

I had

If Me!fsubDocument_KeywordsCont.NewRecord Then

instead of

If Me!fsubDocument_KeywordsCont.Form.NewRecord Then

Works okay now

That’s it.

Many thanks

Is there any way that could that be achieved ‘on the fly’, creating an sql statement in vba?

The actual problem involves companies and products, the guy wants to allow the user to pick products from a list (multiselect) and return companies that sell them (or something like like – it wasn’t entirely clear, hence the student/subjects example). There would potentially be any number of criteria, each therefore requiring their own query , all then being joined – sounds hideous!

Is this one of those questions that sounds straightforward but isn’t?

Edited by HansV to update URL and make it clickable – see Help 19

This isn’t a question but the solution to a problem I had earlier today.
I’ve had a quick search and didn’t find anything here but apologies if I didn’t look hard enough and this info is duplcated elsewhere in the lounge.

I was opening a recordset in code using ado but it was returning EOF = True. The query forming the source of the recordset definitely contained records.
I eventually realised this was because the WHERE criteria in the query contained the wildcard symbol * and ADO requires %.

Changing the * to % in the query returned the correct number of records in the recordset.

BUT opening the query from the databse window returned nothing

Eventually found this article Using the Right Wildcard Characters in SQL Statements

And discovered changing the word ‘Like’ to ‘ALike’ allows the use of the ADO wildcard in the Access query grid

Ah, that was rather obvious, I should try opening my eyes.

I suspect leaving it set to ‘No Duplicates’ was an oversight and that whoever changed it never imagined data being appended on-mass.

I’ve just found the same setting it in a couple of other tables.

Thank you for your help

Hans

I posted the db a few posts up. It has all the records (119) and fields but is still fairly small.

I’ve been repeatedly running the query (getting an error each time) and can eventually populate the destination table with all the records from the source table. Knowing me it’s probably something obvious, but it just seems really odd.

Required=Yes
Allow Zero Length=No

In both tables.

I’ve just done the select query you suggested.

With the few added records the select query returns the same number. With the destination table cleared and empty the query returns nothing.

I’ve exported the 2 tables plus the query to another db – same behaviour.

?