Recent content by student

Since this is coming from an Excel starting point, please point me elsewhere if needed, but was hoping a kind soul could assist as Google isn't helping me enough today... I have a 'dashboard' in Excel which calls through a number of queries from an Access DB which in turn pulls data from...

No problems. Sage can be a bit funny like that. Let us know how you get on.

I'd suggest looking at the nz() function if you haven't already: http://office.microsoft.com/en-us/access-help/nz-function-HA001228890.aspx but if you have then prob best to post your query code here for the experts to check over. One issue I had recently (pulling from tables in Sage) was...

I'd start here and work forwards depending on exactly what you're trying to show: http://office.microsoft.com/en-us/access-help/examples-of-query-criteria-HA010066611.aspx If not then come back with clearer details of what dictates the required dates.

I think looking at the VBA example of the TOP function on that link could be a little over-complicated tbh. I just write it straight in SQL view of query builder for simple queries like this. Example below of one I used recently. Bear in mind that the data must be sorted by a field to get the...

So did that solve the problem for you? How did you get on?

Woohoo! I'm getting better! :cool: Cheers for confirmation. Nothing worse than giving out rubbish advice with total confidence!

Do'h! :eek: Would this look like being the best way of doing it then? If so, I'm guessing a CDate or two in there would be required &/or safer from #ERROR results?

But that would cause the same problem mentioned above, would it not? "If, for example, start 2300 end 1200, the result would be 11 hours, not 13 as worked"

I'd convert the field to long date format (dd/mm/yy hh:nn:ss), but I suspect there will be a better way forward using the two fields together so prob best to wait for the experts on this one. Perhaps DateDiff("n",([StartDate] & " " & [StartTime]),([EndDate] & " " & [EndTime])) for your working...

This is returning a negative value because 1000 is prior to 2300. Without dates this will always be negative. If, for example, start 2300 end 1200, the result would be 11 hours, not 13 as worked. I presume you meant 11 hours worked for 2300-1000? Or do you intend to add an hour to the...

Having looked at the query, you have set it to only return results for where the table entries are dated 3 or more years ago. Your WHERE clause excludes records more recent than 3 years prior to the current date.

That will depend upon the structure and fields used for the dates, but would be along the lines of criteria placed against the relevant date marking which records to update/display - CourseCompletionDates I presume. Criteria in Design view would be similar to >Date()-1095 for a rolling three...

One way would be to do a 'count' on the LiveOrder field in the query to give you number of live orders as well. It depends on exactly what you want the output to be?

Most welcome. Come back if you're having any problems :-)