Access Queries via Excel VBA

[mikeymay]

Posted in Excel board but no response so assuming this is a better place to post so apologies if rules are broken....

I need to perform 2 queries as follows

Query 1
A table contains the following fields
Buyer
Production

I need to return all records where a Production value contains a text string. For example there are 3 records that contain Friday Night Live, Fri Night Live and Eastenders as the Production value. Where the search string is 'Fri' I need to return all fields for the records that contain 'Fri' in the Production value.


Query 2
A table that contains the following fields
Buyer
ContactType
ContactDate
ContactDetails

I need to return records for only the last date for each buyer where the date is less than 30 days from the current date and return all table fields.

Currently I am using
SELECT Buyer, Max(ContactDate), Buyer, ContactChannel, ContactDetails FROM BuyerContact WHERE ContactDate <= #26/02/2018# GROUP BY Buyer
But this isn't working

TIA

 

[mikeymay]

This works in as much it returns the Buyer and Date but I need to return the ContactChannel and ContactDetails. Also I needed to amend the date criteria to '<='

 

[BiocideJ]

OK, then pulling everything together, I believe this is what you need for your final Q2 query.

SELECT BC.Buyer, BC_Last.LAST_CONTACT, BC.Contact Channel, BC.ContactDetails
FROM BuyerContact BC
INNER JOIN (
SELECT Buyer, Max(ContactDate) AS LAST_CONTACT FROM BuyerContact
GROUP BY Buyer HAVING Max(ContactDate) < DATE() - 30)
)BC_LAST ON BC_LAST.Buyer = BC.Buyer AND BC_LAST.LAST_CONTACT = BC.ContactDate

I basically am just typing this up so some editting may be required, but I believe it should work.
NOTE, you may possibly have some issues of duplication if there are multiple identical ContactDates for a single buyer, but I am assuming that is not the case.

EDIT: forgot a select

 

[mikeymay]

So for this query I have the following - SELECT Buyer, Production FROM BuyerProductions WHERE Production Like '*rie*'

I have a few records in the Production field of the BuyerProductions table that contain the word 'corrie' so I would have expected this query to return these records but it's returning a nil RecordCount for the query....

 

[mikeymay]

So I managed to establish the the '*' doesn't work and I need to use '%'....strange.....

Many thanks for your help though, much appreciated!!!!

 

[BiocideJ]

So for this query I have the following - SELECT Buyer, Production FROM BuyerProductions WHERE Production Like '*rie*'

I have a few records in the Production field of the BuyerProductions table that contain the word 'corrie' so I would have expected this query to return these records but it's returning a nil RecordCount for the query....

When you say a nil recordcount, are you testing that RS.EOF = True or trying to do a RS.MoveLast?
Sometimes the RecordCount of a recordset isn't actually determined until you go to iterate through it.
There really isn't any reason I can think of that it wouldn't be working as the query looks good.

EDIT: I just read your last comment and didn't even think about regional settings. Yes, % is also used as the default wildcard character for some settings.

 

[mikeymay]

Well I got there in the end and have learnt a bit as well so all Good!!!

 

[Micron]

Maybe it's an Access project and not a database? They use different character sets - ANSI 89 (* wildcard) vs ANSI 92 (% wildcard).
If it's a db, you might even find that your db properties have been set to use 92, whether or not that was by default or by intentionally switching the character set for that db.