Search results

I'm creating a query to breakdown timesheet hours by activity, this much I can do: TRANSFORM Sum(tbl_Timesheets.Duration) AS SumOfDuration SELECT tbl_Activities.Activity FROM tbl_Employees RIGHT JOIN (tbl_Activities RIGHT JOIN tbl_Timesheets ON tbl_Activities.Activity = tbl_Timesheets.Activity)...

I must be missing something basic here. I'm looking for today's date, but when I run this, Msgbox Date I get yesterday's date. I've checked that the system date on the computer is definitely correct, so what else could be affecting it? I've already searched for answers, but no luck

Thanks guys. Got it working using CJ's method, but Arnelgp's method looks more flexible for if I need to add or change activities. I'll keep that one in the back of my mind for the next major re-write.

Thanks CJ. I'll Give this a crack.

I've trying to produce a crosstab query with employee numbers as the row headings, billed activities for the columns, and duration sums as the values. When I run the code below, the output is correct and shows all activities, even activities with no hours billed to them: TRANSFORM...

Ok, maybe I need to go back to first principles. What I’m doing is pulling out all records from employee timesheets that haven’t yet been extracted for SAP. The problem is that SAP has a couple of requirements: * All records must be in the current month. * the cumulative duration for any date...

Thanks Ridders. I did try your solution, but no dice I'm afraid. The issue doesn't appear to be the brackets as I've reduced the 'where' statement to a single criteria: "WHERE (Month([COLLATEDATE])<=Month(Date()));" and this still crashes. I've been messing around with dates for a while now...

This was working yesterday, but I've obviously changed something that was seemingly unrelated.:banghead::banghead::banghead: When using an SQL string with .OpenRecordset, I can use "WHERE [COLLATEDATE]<=Date();" without issue, but when I use "WHERE (Month([COLLATEDATE])<=Month(Date()));" my...

Oh, it's wonky alright. I need the spreadsheet to be in this very specific format because it is then uploaded to our SAP accounting system, and if everything isn't just so, SAP throws its toys out of the cot. Currently the reason why I initially need the datatype as Date is because I've got a...

Thanks Ridders. I tried that, but my date field comes out blank on the table.:banghead: "SELECT CDate(Format([ActivityDate], ""dd.mm.yyyy"")) AS [DATE]" etc I'm generating my query by creating it as a string, which is why I have double quotes, then using .CreateQueryDef to run it. If...

So there's no way to do this dynamically? I can use cDate to set the datatype in the query. I can use format to get the numbers in the right order (using 'dd.mm.yyyy'), but I can't use them both at once?

I've been beating my head against this one for a while now, and I'm sure I'm not the first person on the planet with this issue. I'm using a query to create a table. I then perform some VBA manipulation before outputting the modified table to an excel spreadsheet. The issue I have is that one...