Search results

Thanks Chris, that's a handy extension to the capability of VBA that overcomes one of the more glaring omissions. In the example above though you appear to be replacing the existing GetObject() command? i.e I stick that in a module because I want to pass stuff between forms then I lose the...

What you are also doing is using GOTO programming disguised as error handling. You can test if outlook is open without needing to use GOTO code blocks, if it's not open you can create it yourself. Error Handling ideally should be for things you can't anticipate or can but can't sort out in...

I'd suggest it's a reasonable way to start getting your feet wet into the world of VBA. You know what the macro does and are presumably comfortable with creating them, so you can compare the macro to the VBA. I don't tend to go near macros if I can help it so this might no longer be the case...

Ironically, your comment for that line acknowledges the issue (it's not an issue per sé, the count just begins at 0 rather than 1). AS vbainet says you can put debug.print ..... in the immediate window while you're stepping through code or while you're writing it for that matter, which can be...

Perhaps it boils down to having spent so long writing a piece of code to achieve this that abandoning it in favour of spending 5 minutes creating an update query is unthinkable? I've re-invented the wheel a few times for sure, admitting that's what you're doing and abandoning it though is quite...

Type Mismatch generally means you're trying to compare two values of an incompatible type. you're iterating one row too many and trying to add NULL to your total. For VarRow = 0 To CtrlLst.ListCount - 1 When you're getting problems like this, turn off your error handling so you can see what...

Right now, your code suggests that the second query can effectively be considered your master data. i.e. if any of the fields are different then the value in the second query overwrites the value in tt_generalinfo. On that basis, why bother to check if anything is different? Just update the...

It's a bit of a blunderbuss approach for sure but yeah SELECT columns FROM Table t INNER JOIN ( select ID, 1,2,3, 'Prod' as criteria FROM prodTable UNION [ALL] SELECT ID, 4,5,6 'DEV' as criteria FROM devTable ) as u ON t.ID = u.ID AND t.colA = u.criteria

Suggests you're sending the literal strings "Table" and "Number" to the function rather than the Tablename and field. "Syntax error in the FROM clause" generally tends to mean that the table name is spelt wrong.

That'd do it, perish the thought we'd all agree on a common date format :D

debug.Print HourTotalProgress(2012,400,48) = 375 debug.Print HourTotalProgress(2013,400,48) = 275 debug.Print HourTotalProgress(2014,400,48) = 175 debug.Print HourTotalProgress(2015,400,48) = 75. I'm going to blame your data :D Never had so much problem with 3 functional lines of code...

You can always use the query to generate a string of SQL and pass that to sp_executesql. Granted that's still essentially running two queries as is the other alternative that springs to mind of using a UNION [ALL] SELECT The Final Output FROM table INNER JOIN ( Select 1,2,3, 'criteria' as...

Right. Stop. Step 1. replace whichever function you have currently with this one, which takes the graduation year, Required Hours and the duration in months of the course as parameters: Function HourTotalProgress(ByRef Grad_year As Integer, ByRef hours_req As Integer, byref intCourseMonths...

I suspect that what you might need to do is something more along the lines of If somevalue = 'foo' SELECT 1,2,3,4 FROM etc etc etc else if somevalue = 'bar' Select 5,6,7,8 FROM etc etc end

Don't over-estimate it either :D I've used systems that take decimal values for times and I agree they're a pain in the backside 1.5 hours is 1:30 in my head, 1.25 = 1:15 and so on. Yeah you do eventually get used to it but I think any element of a system where your users have to get used to...

I don't think you can do it this way, but what are you actually trying to achieve? I think as you suspect that if you're trying to substitute a number of columns (in a select statement) that you'll have to do it column at a time. ie SELECT .... CASE @test WHEN @test = 1 THEN t.thiscolumn...

The code was just to illustrate the method, on its own it doesn't do anything useful as such. personally I probably wouldn't store hours and minutes separately, you've got two methods to convert hours and minutes into either Total Minutes and back again, or as a decimal value then you only need...

Glad you got it working and thanks for sharing what you did to fix the issue.

Sub test() Dim hours As Integer Dim minutes As Integer Dim ExampleTotalMinutes As Integer ExampleTotalMinutes = 250 hours = fix(ExampleTotalMinutes / 60) - fix ensures we round down i.e. 2.99 = 2 minutes = ExampleTotalMinutes Mod (60) Debug.Print hours & " " & minutes...

Just to chip in, I agree with Everything RainLover's said. Flexibility and accuracy. You have a "Name" Field at the moment, presumably formatted "Firstname" space "Surname". What happens if you want everyone called Smith in your output? You have to use a LIKE clause rather than just WHERE...