Replies

Every so often I get snagged by this one myself. ADO doesn’t use the asterisk (*) for a wildcard. ADO uses the percent symbol %. So replace the * with a percent symbol, and you should be good to go.

Odd, isn’t it? You can paste the SQL directly into a query, and it works, but ADO does not see the * as a wildcard.

Hope this helps!

Every so often I get snagged by this one myself. ADO doesn’t use the asterisk (*) for a wildcard. ADO uses the percent symbol %. So replace the * with a percent symbol, and you should be good to go.

Odd, isn’t it? You can paste the SQL directly into a query, and it works, but ADO does not see the * as a wildcard.

Hope this helps!

Oh, I had always thought it was SQL Server, because Jet doesn’t mind getting an actual date variable. So Jet just ‘comprimises’ I guess. Learn something knew everyday.

Oh, I had always thought it was SQL Server, because Jet doesn’t mind getting an actual date variable. So Jet just ‘comprimises’ I guess. Learn something knew everyday.

Not a waste of time. Sometimes we all need a second pair of eyes on something, that’s why the lounge is here!

As for the date formatting, if you stick with Access, you can’t go wrong with #” & dteSomDateVariable & “# because the date variable is a constant format (double variable, whole numbers for days, decimal for time), which is Universal in Access. If you want it to work in SQL server, then I believe SQL Server wants MM/DD/YYYY.

Not a waste of time. Sometimes we all need a second pair of eyes on something, that’s why the lounge is here!

As for the date formatting, if you stick with Access, you can’t go wrong with #” & dteSomDateVariable & “# because the date variable is a constant format (double variable, whole numbers for days, decimal for time), which is Universal in Access. If you want it to work in SQL server, then I believe SQL Server wants MM/DD/YYYY.

Aha. adCmdTableDirect has always worked for me for naming a table in the Open method. Honestly don’t think I tried adCmdTable. Quite frankly, for the past year or two, I simply leave the default, because I’m using SQL statements, and if I am just adding a record, I don’t even use a recordset, I just use INSERT INTO statements….though, if I had a ‘massive’ data dump, I would go back to recordsets.

Either way, it’ll be interesting to find out if the Options argument is the cause of the actual problem.

Aha. adCmdTableDirect has always worked for me for naming a table in the Open method. Honestly don’t think I tried adCmdTable. Quite frankly, for the past year or two, I simply leave the default, because I’m using SQL statements, and if I am just adding a record, I don’t even use a recordset, I just use INSERT INTO statements….though, if I had a ‘massive’ data dump, I would go back to recordsets.

Either way, it’ll be interesting to find out if the Options argument is the cause of the actual problem.

That’s what I thought, I always leave it blank, which defaults to -1, which I am not sure what that is. But I always thought adCmdTable, and adCmdTableDirect was used when you refer to a table, and not to a SQL string. When I saw this post, I checked the help file, and quite frankly felt more confused about the issue. I have opened tables directly with ADO (if I am just adding a record), and put “tblSomething” where a SQL statement would go, and I have problems if I don’t use adCmdTableDirect (though I don’t think I tried adCmdTable).

That’s what I thought, I always leave it blank, which defaults to -1, which I am not sure what that is. But I always thought adCmdTable, and adCmdTableDirect was used when you refer to a table, and not to a SQL string. When I saw this post, I checked the help file, and quite frankly felt more confused about the issue. I have opened tables directly with ADO (if I am just adding a record), and put “tblSomething” where a SQL statement would go, and I have problems if I don’t use adCmdTableDirect (though I don’t think I tried adCmdTable).

Hmm, you’re right, I guess I deal with Callback functions too much, because with a callback function, it DOES size the slider based on the number of items. Try populating the combo with a callback function.

Hmm, you’re right, I guess I deal with Callback functions too much, because with a callback function, it DOES size the slider based on the number of items. Try populating the combo with a callback function.

That’s not entirely true. A combobox has to get the initial ‘count’ when it loads, so when the list is dropped down, the data my not be ‘all there’, but the slider will be the appropriate size. You may want to look into using a callback function, if you suspect you are ‘losing’ the connection somehow.

That’s not entirely true. A combobox has to get the initial ‘count’ when it loads, so when the list is dropped down, the data my not be ‘all there’, but the slider will be the appropriate size. You may want to look into using a callback function, if you suspect you are ‘losing’ the connection somehow.

Here’s the SQL for a single query solution:

SELECT ProdID, yearno, weekno, T1.sold, (SELECT TOP 1 itemsperbox FROM itemsize WHERE (itemsize.yearno=T1.yearno AND itemsize.weekno<=T1.weekno) OR itemsize.yearno<T1.yearno ORDER BY yearno DESC, weekno DESC) AS ItemsPerBox, (SELECT TOP 1 itemsperbox*T1.sold AS total FROM itemsize WHERE (itemsize.yearno=T1.yearno AND itemsize.weekno<=T1.weekno) OR itemsize.yearno<T1.yearno ORDER BY yearno DESC, weekno DESC) AS total
FROM sold AS T1
ORDER BY ProdID, yearno, weekno;

I used itemsize and sold as the table names, per your post, but I would recommend changing them to tblItemSize and tblSold. (the sold table has a sold field…which can lead to some confusion.

Note, the query results will have 'nulls' instead of zeros, if there is no itemsperbox value 'yet'. If you must have zeros, then use this (it will just take longer…)

SELECT ProdID, yearno, weekno, T1.sold, IIF(IsNull((SELECT TOP 1 itemsperbox FROM itemsize WHERE (itemsize.yearno=T1.yearno AND itemsize.weekno<=T1.weekno) OR itemsize.yearno<T1.yearno ORDER BY yearno DESC, weekno DESC)),0,(SELECT TOP 1 itemsperbox FROM itemsize WHERE (itemsize.yearno=T1.yearno AND itemsize.weekno<=T1.weekno) OR itemsize.yearno<T1.yearno ORDER BY yearno DESC, weekno DESC)) AS ItemsPerBox, IIF(IsNull((SELECT TOP 1 itemsperbox*T1.sold AS total FROM itemsize WHERE (itemsize.yearno=T1.yearno AND itemsize.weekno<=T1.weekno) OR itemsize.yearno<T1.yearno ORDER BY yearno DESC, weekno DESC)),0,(SELECT TOP 1 itemsperbox*T1.sold AS total FROM itemsize WHERE (itemsize.yearno=T1.yearno AND itemsize.weekno<=T1.weekno) OR itemsize.yearno<T1.yearno ORDER BY yearno DESC, weekno DESC)) AS total
FROM sold AS T1
ORDER BY ProdID, yearno, weekno;

You can get zero's with the first query (which would be faster, by putting records in the itemsize table like a,1,1,0, but that may disrupt other systems in your database)

Have fun….