Search results

Thanks for the notepad++ tip, that looks useful

Thanks, sorted! That's just weird. I had a query which I copied to Word to amend (my eyesight struggles with lots of brackets of various types in expression builder) and word has added the odd quotes. There's only one " button on my keyboard, so why Word is just adding the wrong ones I don't...

This has got me scratching my head, am I missing something really stupid? I've got a query. One field is an expression on which I'm filtering. A shortened version of the expression is Expr1: IIf([Status]=”Active”,"Yes","No") There are other parts to the formula, hence why I'm not filtering...

Don't answer, I figured out i needed left joins not inner joins :)

I've got 2 tables, same structure, one [T-temp-Target] holds number of training units split by module a trainee needs to finish the course, the other [T-temp-Actual] holds what they've completed so far. Both tables have structure TRAINEEID MODCODE CountOfUnits I'm trying to find the modules...

You can create a simple join using 'Is Null' as criteria on the inner joined table.

There's too much data alas, 5m rows or thereabouts. Hence it's not in an Excel s/s. The query works fine, runs in about a minute. Just take 2-3 mins to open or close in design mode. It could be the printer, it's a big network colour printer that takes an age to print to but I've never printed...

Yes, it makes no difference. The SQL is TRANSFORM Count([T-TR-Performance].EVIDENCEID) AS CountOfEVIDENCEID SELECT [T-TR-Performance].[T-TRMODULESCHEMEEVENT_STATUS], [T-TR-Performance].Unfunded, [T-TR-Performance].AREA, [T-TR-Performance].SCHEME...

What it says on the tin. I've got a very simple crosstab query, 1 table, no criteria, 5 row headings, 1 column heading, 1 data... the database is 900Mb, not big, and regularly compacted. But it takes 3 mins to open design view, it runs quicker than it takes to open design view. Why is that?

Plus, the data is picked up by a few charts. The number of rows and columns is potentially dynamic and won't work with a pivot table. With an access data pull I could use table range names.

Just because it's too big for Excel, it's a database with 5m rows in a table created for this one query. I could do it using MS query but it's too slow. Copy & paste it will have to be alas.

Hi I'm setting up an Excel file to report on Access data. At the moment it's copy&paste but I'm trying to set up a connection to the crosstab table in Access. I can see the database, tables and a few queries in thge connection wizard. I can't see maketable queries which is what I'd expect...

Thanks Paul. I was hoping to give SQL a swerve but I guess there comes a time when it's the only way.

Hi Sorry if this is a very dumb question I've got two tables, both are indexed by customer ID, with a series of dates against the customer ID. One has a list of all dates a customer was visited, the other is a list of dates where activity happened on the customer account I want to get a...

Re. assorted comments about Goto's I understand people who've learnt to code using goto get prickly about it. If you are just learning to code though, treat it as something which will give you a nasty rash and haemorroids if you use it outside error trapping. Quite frankly, debugging code...

Hi Lol Namlian, that's what I did to figure out that my initial misunderstanding was wrong. I'm more used to Excel, I wrote a VBA function to parse long formulae and break it down. In Access I have to resort to notepad alas... you'd think they'd fix the SQL window to do that automatically...

I've got a query that generates a big table of data for use by multiple queries. There are criteria against a number of the fields. But when I run the query, it just ignores the criteria and returns everything. What I want is to restrict data from a Region fields to...

Cheers, could be nulls... I had to restrict the dates because they're dates students attended IT courses, some were down as taking place in 1753 and some as far in the future as 5271AD. So it wouldnt surprise me if some nulls are in there.

Hi Just found this forum and expect to make good use of it. I've some experience of Access, I've had fun with vba subforms and suchlike but in an old version of Access (2000) so if I need to use vba I'll be dipping in here for tips. I've more experience with Excel, have coded vba extensively...