Search results

Thanks! Yeah I'll certainly update the Update line... it was on my to do list TBH... The code as it stands today was an 'accessified' version of what I needed to do in Excel way back when the database was held there (=IF(LEN(<cell>)=5, "0"&<cell>,<cell>) and autofill!). And I appreciate the...

That means running the same query twice though. That's why I thought RecordsAffected would be the best way...

Try this one... SELECT T1.ProductDescription, Sum(Iif(T2.TransType='I',T2.Quantity,-T2.Quantity)) AS StockQty FROM tblProduct AS T1 INNER JOIN tblTransaction AS T2 ON T1.ProductID = T2.ProductID GROUP BY T1.ProductDescription HAVING Sum(Iif(T2.TransType='I',T2.Quantity,-T2.Quantity))>0;

I think you've missed a closing bracket after T2.Quantity, -T2.Quantity) You need to close out the IIF and close out the SUM too.

As Markk says, depends on what sort of relationship your second query has to your first as to whether a UNION query would be needed. But you could always write out the UNION query in full so only 1 query needs to be maintained. I like UNION queries when the second query summarises the first...

Try this... http://www.dummies.com/programming/sql/how-to-use-sql-with-microsoft-access/

Hi folks, I've written a module to import data from one table into another. I get a daily MTD table in excel that I import through a saved import procedure, then my module grabs the first date from the imported file, deletes all records >= that date, then imports the new data, adding in some...

Thanks guys. Figured it out. I'm now using the date element of the Date/Time field to subtract from itself (rather than the separate date field - don't even know why that's there anyway I'll have to ask the supplier) and it's coming out with a nice, normal, easy to use integer...

Hi folks, I am writing a query that will highlight aggregations within time periods. So I have a full date_time field on each row (TRANSACTION_DATE_TIME) and a date only field as well (Last Contact Date). I want to be able to use the time element (regardless of the date) and perform...

I got really excited then... I read all the way through this post nodding my head thinking 'this is going to solve my week long problem!' And then... it didn't. Something in my code is locking my Temp table... but only one of them! I have a Report and Sub Report run off data in two temp staging...

I've attached a very smoothed down version of the DB I'm working from. Hope this makes more sense now... Thanks for your Help so far!

Hope this is what you're asking for? I tried to attach the DB but even when zipped, it's still too large... :eek:

Thanks! I CAN post an example of the database, but I'd first need to run through all the tables cleaning them of personal employee data etc... Is this info enough for now? I have 9 BE tables which hold all of the performance data and employee/team data. 2 of the tables are populated with daily...

I should add that I cannot add the Ranked numbers as standalone queries and do it the 'old fashioned way', as the connections then exceed the 1024 (or whatever the number is) and my report fails - and apologies for the Yodaism in the title :D

Hi all, I feel I'm on the brink of fixing this one myself but seem to be meeting myself coming back and going backwards in coming forwards... Trying to remind myself that 7 months ago I'd never even HEARD of SQL or VBA, let along used them... I have a report based on a query which is...

..... It was Nz()... :banghead: Nz()..... How it has betrayed me... :( Oh well. Thank you for all your help!

Thank you for your help so far! Tried that too... removing all Format() along the way still displays the field as text... so I can't format it on the report that way. I've even gone as far as changing the table field from currency to number to see if the division of currency by a Long (as a...

Oh really? That's interesting and good to know. I can fix that. But the conversion from number to string seems to happen as soon as I calculate against the datediff calculation on the query behind the query that powers the report... I might have to redesign. Grrr

D'oh! I've just noticed that the fields are being displayed as Text not Numbers... That explains why the COnditional Formatting isn't working... now I need to figure out why...

Yeah I tried that. It's still formatting all fields as if the expression is true regardless of the value