Recent content by garethl

I've got a table with two yes / no fields (Pass / Fail) behind a datasheet form For each record in the table user needs to flag a check box for EITHER pass OR fail but not both (I know I could just have one check box but my boss wants them to have to make a choice for every record rather than...

I was actually using the foreign key as well I've changed that now that helps a bit too. Yeah I should of spotted that it was just the way I'd layed out the SQL I didn't

oops! missing join.. yeah thats it cheers

Cheers JANR I tried both ways already (though I didn't realise the WHERE should be faster) Its still a slow beast Forgot to say - its returning 56 counts which total to 481054 records and that WHERE clause is selecting the 481054 records out of a table of 730832 So not ridiculous amounts...

Hello all Is there any sensible reason why this query should take like 20 minutes to run.. other than that we use Citrix! I mean I know this is a basic query (just a conditional count) but am I doing something stupid here? SELECT TBL_Agreements.IDAgent...

Ok I changed strSQL = "ALTER TABLE [TBL_TmpMonitorIn] ADD COLUMN [DupeCode]CHARACTER;" CurrentProject.Connection.Execute strSQL to strSQL = "ALTER TABLE [TBL_TmpMonitorIn] ADD COLUMN [DupeCode]VARCHAR;" CurrentProject.Connection.Execute strSQL leaving the update command the same and...

Hello Thanks for all your responses.. sorry I'm only just back online I don't work Fridays. Backend is Jet and I'm not using SageAccPac Str is string conversion function with my edition of VB - It also gets autocapitalised and the VB IDE asks you for a number argument when you type str(...

Ok so I've tried some more stuff that could shed light on this.. I was wondering if any particular field was the problem so I've tried them all in pairs and on their own and I always get the trailing spaces. More importantly I then tried something really stupid which was never going to...

Good idea, just tried it... still the same None of the fields I'm concatenating have these trailing spaces though so no idea where they are coming from!

I am adding a text field to a temporary table using the following vb.. strSQL = "ALTER TABLE [TBL_TmpMonitorIn] ADD COLUMN [DupeCode]CHARACTER;" CurrentProject.Connection.Execute strSQL and then subsequently giving it a value like this.. strSQL = "UPDATE [TBL_TmpMonitorIn] SET DupeCode =...

Ok well that solution actually works really well There is now a table in the back end database with the most horrific design you have ever seen.. well actually it just ISN'T designed at all. It has exactly the same structure as the excel file with the addition of a primary key and a boolean...

Ok if you don't thik thats a step backwards I'll give it a go. The consensus on this board seems to be that using task scheduler and a macro is the simplest way to automate tasks so I'll do that and have it normalise the data thats been loaded every night after everyones gone home. When I...

i know! sods law i read that other thread and it makes perfect sense whats happening, tho there aren't even many joins in that query which i'm addressing i thought of another idea though how easy is it to have stuff process in the background in access when the systems idle and noone is...

ok i decided i answered my own question on the function passing public variable thing it stands to reason that it should be slower now i've tried nieleg's solution with the first 27 of those queries to see what the speed was like and i get the "cannot open any more databases" error which is...

Another thing is i did have the batch ID stored in a variable intBatchID so in that loop I was putting its value into the query string that i was building in vb using this union query approach there are as i can see two ways that i can deal with it 1) what I have started doing which is to...