Search results

It was just pseudo code. And no, using the ISNULL function does remove my issue. But it doesn't feel like a fix, technically as Doc explained, my initial query should be evaluated and "stored" in a temp table. So evaluating ISNULL on itself should be a useless step. However it does work, proving...

The_Doc_Man, thank you for this in depth explanation. Now I understand. As you said, my calculated field must be processed last. I am surprised by this because I don't remember having seen this before, but it must have happened every time I did joins and calculated fields together. here's my...

I have an issue with a calculated field that continues calculating even outside of its scope. Assume 2 queries: SubQuery FinalQuery In SubQuery, I have fields including: [Type] [T1 Res] [T2 Res] I have a field that goes: Result: iif([Type]=1,[T1 Res],iif([Type]=2,[T2 Res],"default result"))...

Checked all the references, refreshed my linked tables, no issues. All the tables are from separate files. When opening the front-end, I have "dummy" tables in each back-end being opened to create persistent connection. I tried rebuilding this union query because it had no subqueries and a "in...

Hi My company's moving from Windows 7 to Windows 10. Additionally, don't ask me why, they decided to move from Office 2010 to Office 2013. During UAT some problems came up. I got a huge UNION ALL query that takes a dozen linked tables and mash them together on some criteria, on Office 2010 it...

That's a very very strange design choice. I guess I'll build my own datasheet + subform system then. Thank you.

Hello So I wanted to use "split" form just to have a little box on the side with the picture of the product everytime someone clicks on a record. I did that, and it works, but for some reason, I can't seem to have all my table's fields in the datasheet part. Only the few fields I have bound to...

Arnelgp, I looked at this and it looks very promising. Thanks a lot for this idea, I had no idea you could do that in Excel.

I meant the execution time is much faster, when you put the data in a table and run 1 query instead of running 5000 independent queries. That's why I want to find a way to simulate that situation in Excel VBA.

That sounds interesting. I will research that possibility but at first glance it doesn't seem like you can run a joined query between a local recordset and a connection. What do you mean?

Hello, I'm trying to figure out a consistent way to quickly execute a JOIN query between an ODBC data source and an excel range. In this exemple, my ODBC source has a table with [material] and [EAN], I'm running and Excel macro that has about 5000 materials and I want to find their EAN codes...

Yes, that worked fine. I used the iif function in the where clause and I pass "empty" instead of "" in my parameter. Cheers.

That solution might do the trick. Transform the null values into something before running a WHERE LIKE on them. Will report tomorrow when I get to implement it. Thanks

Because if I used WHERE param = "A" OR IS NULL, I'll get the values of the null cumulated with the values of A. That's no good. Whereas I want to be able to get ONLY values of A and ONLY values of "". As well as values of "*" to get the total. Query is basically: SELECT * FROM tables and joins...

Hello I've got a query, it pulls sales data according to "cluster". Cluster can be A, B, C, D, or empty. I want this query to be able to pull 2 values by being run 2 times: - Sales of one cluster (empty ones are treated as one cluster). - Sales of everything. To do so, I use in my VBA module...

50k row isn't what I would call a large data set considering my databases have over 100 million rows. As for Access being designed to store an manipulate data, for human-speed interaction, sure, not for program-speed interaction. Using tables and recordsets in VBA for every operation that needs...

To answer my own question, yes it's much faster. The speed is a perfect constant whether I access item #3 or item #56103, whereas the collection really crawled to 10 times slower toward the end.

No, I make a lot of changes to the file itself, the headers are just the tip of the iceberg. I suppose doing an array of array is a good idea, it's just that I use collections almost everytime I need to store stuff in a list, and everytime I tried to use arrays, I've hit roadblocks like...

But what I'm putting in the collection should only be references to arrays, the arrays themselves should be be fully accessible, shouldn't they? Like, as a workaround, I'm making a collection of collection of object containing 1 single parameter "value", so I can do: file(i)(c).value =...

Hi there, I'm in the need of some help. I open a text file and store it in a collection of array: Open filepath For Input As #filenumber While Not EOF(filenumber) Line Input #filenumber, lineread file.Add Split("" & SEP & lineread, SEP) 'index starts at 1 for simplicity Wend Close...