Search results

Makes logical sense, I just don't know how to do it!

I have two great bits of code, which work well in Excel and Access. This works well in Excel to identify gaps and close them up for me. Thank you @Gasman Sub Tidy_Address() Dim iLoop1 As Integer, iLoop2 As Integer, iLastRow As Integer iLastRow = ActiveSheet.UsedRange.Rows.Count...

Thank you all - the subroutine works brilliantly.

Sorry, terrible explanation. So, in the image above, there's an empty cell between 'Braunston' and 'Northamptonshire'. To the system that these addresses get imported into, that looks like an empty address line and so it would reject that address. For it to be correct, 'Northamptonshire' would...

I have an import template for one of our system which requires all of the address lines to be 'aligned left'. It's a pain, so I wondered if there's a formula which could identify gaps where there is a blank cell. For example, between Smith's Wood and Birmingham below.

Ah, ok - it's [pick_no] in the qry_to_print_BOUN

That's exactly why I was thinking it wasn't going to be possible, but just wondered.

It's fine, I can do it in VBA. I was just being a bit lazy really, and hoping there was a feature in that interface that I could take advantage of. No worries, I'll do it the long way!

I feel like this might not be possible, but how can I use a value from the query to create a dynamic file name where I've indicated [VARIABLE HERE] in the image below? The function makedir() will have created the dynamic folder name, ready to be used.

Works like a charm, thank you. I wondered if it was overwriting, but would've had no clue how to fix it!

The code below isn't functioning as I expected it to... In short, I've added in the 'While Not rs. EOF', down to 'Wend', in the expectation that it would loop through all the records in my query and drop them all into the text body of the email that I'm sending. Oddly though, the only record...

Works like a charm - thank you!

I'm using the code below to strip certain non ascii characters in a query. It works nicely, apart from where it comes across a single quote. I understand why it's breaking, but I don't understand how I'd alter the function below to cope with single quotes in the fields that it's looking at...

Solved, corrected the syntax and now it sums correctly.

Spoke too soon - it counts, but I need the running total to SUM. Tried the obvious of changing Count to Sum, but didn't work!

Solved. I was trying to use this on a query which had a GROUP BY in it. I now use a Make Table to create a new table, query off that and it works perfectly.

I've tweaked the code to the below; SELECT qry_future_despatch.ProductCode, qry_future_despatch.days_to_show, qry_future_despatch.SumOfOrderQuantity, (SELECT Count("1") FROM qry_future_despatch As A Where A.ProductCode = qry_future_despatch.ProductCode and A.days_to_show <=...

I feel like I'm on a better track now. In the attached, columns A-C are the live query from my DB. Column D, highlighted in yellow is what I'm trying to achieve. Can this be done? An expression in the query itself would be nice, but I'm happy deploying VBA (although I couldn't write it!)

Thank you for your reply Pat. A VBA solution has felt inevitable from the moment I started working on this. I appreciate that a database is not a spreadsheet, however I've developed the query in that way as a) I'm a relative novice, intermediate at best, and b) it makes the problem easier to...

Evening All, I have a bit of a conundrum here. Have a look at the attached Excel file. Columns C-Q show the stock which is committed for despatch on days 1-14. Column B shows the stock which is available. The yellow highlighted cells in J3 and N4 show the days on which there will be no stock...