Replies

That’s marvellous – and bizarre. Armed with this information I reapplied SP2 and do you know what? It works fine! I didn’t have to run Dcomcnfg.exe or anything. That is strange. Nevertheless I’m one happy bunny now, particularly as it solves my MS Access problem (see Post 97731).

Thanks a lot.

Simon

Oooh, that’s interesting. On my main development machine I’m running A2K (9.0.3821 SR1) and Win 2K (SP1). On my other (standalone) PC I’m running A2K with no service packs installed and Win 2K (SP2). The query runs as it should on my other machine. I wanted to upgrade my other PC to A2K SR1 (just to prove to myself that it isn’t a difference in the query results in different Access versions), but I can’t seem to download the right set of files (I can’t use the standard online installation on my other PC as it has no internet connection).

Most irritatingly, I can’t upgrade my main PC to W2K SP2. I’ve tried and I always get problems – particularly with the Search for Files and Folders functionality.

Most PCs that will run my Access application are using NT 4 Workstation, so hopefully the problem won’t occur there either. Thanks for your help.

Simon

> Would they be better as saved queries?
>Or is there a time lag inherent with loading Access (or any DB) first time around?

Probably and yes. If there are no issues in saving the queries then do so. If the query is built up at run-time because its functionality depends on various other factors that can only be determined at run-time then it becomes a balancing act. Do you complicate the query so it can cater for the various situations; do you create multiple queries; or is it easier to leave things as they are?

There is definitely an extra time lag before the first access to an Access DB through ADO which has nothing to do with compiled or uncompiled queries – it happens even if the Access DB contains nothing more than tables which you attempt to access, as tables, through ADO. I don’t imagine there’s anything that can be done about it, but someone may know of a way of minimising it.

Hope this helps,

Simon

If you’ve got the start and end times of the existing bookings you can elaborate on this by doing something like:

SELECT BookingDate FROM tblBooking WHERE BookingStart[ThisBookingStart] GROUP BY BookingDate HAVING BookingDate =[ThisBookingDate] AND Count(BookingDate) >=[MaxBookings]

[ThisBookingDate] is whichever date you’re trying to book on and [ThisBookingStart] and [ThisBookingEnd] are the start and end ranges of the period you’re trying to book. MaxBookings is the maximum number of bookings allowed at any given time. You can get these values into the query a number of ways.

If the query returns a record, you can’t book a session, otherwise there are vacant PCs and you can.

I’m a bit confused as to what you’re trying to do, but I’ll have a go…

Firstly, if you just want the year, you can use the Year() function. To get the current year, use Year(Date()).

I’m not quite sure if you want to store this date into one record of a different table or in each record that you’ve appended. If it’s the latter than add Year(Date()) as a column in the append query and set the field you’re appending to accordingly.

If you just want to update a single value in a record in a table, you could do it in code, but if you know which record(s) you want to update then you could create an update query containing something like:

UPDATE CR_table SET CRID = Year(Date())

If this table contains multiple records and you only want to update one of them then you can add a WHERE clause to the update query.

Hope this helps,

Simon.

Unless you need to find the sum total of differences for a number of records for the same person, you don’t need the “SUM” part ADMITtoEOM:[ENDING DATE]-[ADMIT DATE] should do the trick.

However, it doesn’t seem to work properly if you’re relying on the query assuming all undefined “variables” are query parameters, you need to actually define [ENDING DATE] as a parameter of type DateTime in the query.

A further alternative is to use ADMITtoEOM:DateDiff(“d”,[ADMIT DATE],[ENDING DATE]).