Search results

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 :-)

Ah, I think I see the confusion. I meant a separate table with a list of all course codes in one field and the relevant increase (one year, two, etc) in the next field. This could then be linked to the original table in a report by course ID to calculate the new due-date for each course for each...

Any ideas why it contains duplicate dates? If you're trying to pull this data into a table you'll be able to eradicate duplicates on import I would presume. Or does it already exist in table form?

@plog: I'm guessing I'm right in saying it would normally be better to make use of a table of courses & the corresponding increase in date for each rather than rely on a long Iif for this? Would mean if criteria for date changes would be easier to alter at a later date, no?

Back up a minute, can I just check it's not keeping records separate because of another field not being counted or summed?

Welcome to the forum. I'd suggest looking up the Min & Max functions. You'll probably need Count function to get the number of orders for each product as well. Good place to start would be http://www.techonthenet.com/sql/min.php Do a bit of digging and then come back with more details of exactly...