Search results

I have the below , I tried NZ on every field but still get the error, removing the WHERE resolves it so you're spot on with that, I still don't really understand exactly why it doesn't like it? is it to do with how queries are almost processed "server side" and the results returned? and because...

The funny thing is there aren't any NULL values in any of the tables themselves, I've checked all the indexed columns. The only NULL is created when the SELECT query does RIGHT JOIN from tables with every item ID as a primary key, to a table with only some items Primary keys and other values...

I have a select query, it has 10 tables in, some are local, some are linked to FoxPro DBF via ODBC, some with inner joins, some with right, I'm expecting null values in some fields, however I get ODBC--call failed. [Microsoft][ODBC Visual FoxPro Driver]Index does not accept NULL. (#886) I had...

This is what I was trying to explain was my elegant solution, not that NZ isn't elegant but I was annoyed and wanted to solve it a different way :-)

I'd tried all sorts of fudge like "NOT [field1] = true" but it wasn't until I was falling asleep that it occured to me "[field1] <> True" will catch null and false, doh!

I've been troubleshooting an issue where a totals query based on the above left join query (with the left join that returns the boolean field that may not exist so can be NULL) gives 1 result and if I instead have it make a table and run the same test I get a different result, The outcome was...

Thanks all, I'm pretty happy using GoTo how I plan to as basically "Continue" or "Skip to the loop" I appreciate the separate sub/function option and I do if things repeat or are used elsewhere (as Pat says in his reply as I type this), that Criteria example looks perfectly reasonable size...

Hmm, I've not messed with GoSub, it looks a lot like splitting into functions suggested above? the "next bit of code" I want to work on ends up down at the end of the function which seems out of sequence?

Hehe, 1 thing as in, it's a function for picking stock, chunks of it aren't going to be used for anything else by other code or called from elsewhere etc

Sorry for the split replies, taking things one at a time, I don't see why you'd want the Return part of GoSub in this example?

I definitely get the nested if's approach but.... I want to avoid pyramids like this Function Func1() Dim Var3 As Long Dim Var2 As Long Dim Var1 As Long Do Until DCount("field", "table", "done = false") = 0 Var3 = DLookup("field", "table", "done = false") DoCmd.RunSQL "update...

Hmm, yes I see, it ends up being very similar, just split across two/multiple functions instead of all within a function, which i personally kinda prefer as it's all only doing one "thing", I tend to split useful & reusable things off into separate functions

Yes exactly, sorry I was struggling to explain that, I want to skip the rest of the code i've got "in that iteration" and move onto the next record This is basically what I'm trying to do, i've only written a bit of it and got to the first "guard clause" type bit and was like, "huh" i wonder...

yea but that doesn't let me skip to the next in the set

My old functions were serious pyramid code, often multiple nested if's deep, since then I've been using guard clauses and setting values/checking values though a series of separate if's instead of nesting, it's definitely easier to keep track of the "end" compared to nesting! However I'm stuck...

ahh yes, it's appeared in ODBC (32-bit) now, for some reason it didn't appear after the reboot but I left the PC on overnight and went to double check today before replying and there it was :-)

Oh, slight problem, I need 32bit not 64bit, the 32bit of version 17 doesn't want to install and the 64bit one doesn't appear in 32bit ODBC :-(

Yea, if I knew the developer I'd ask him :) there's no decimals in any of our data 🤷‍♂️

https://stackoverflow.com/questions/62996189/ms-sql-server-field-with-data-type-of-money-shows-empty-field-in-ms-access-linke I had this issue and this was the solution, I was only getting 0 from that field until I changed this, obv all with version 11, I don't know where/why I got that as the...

I'm now chasing around other ID fields that are long Int in the database, but have had to go through the totals query along with the broken currency fields and are now a mix of text etc, and still don't like being outer joined upon -_-