trying to count bats: log transit times and direction

[cmp20]

Im on Access 2003
it fails on 1st part

 

[the_net_2.0]

then you must figure out why the query statement isn't gathering any records. put a break point after the 'openrecordset' code and hover over any variables.

are you familiar with the vba tools that are available for debugging purposes? i can recreate this error on the second occurance of the code if I DON'T change the eventtime field to a short date and leave it as a date/time combination field.

is that it I wonder?

 

[cmp20]

then you must figure out why the query statement isn't gathering any records. put a break point after the 'openrecordset' code and hover over any variables.

are you familiar with the vba tools that are available for debugging purposes? i can recreate this error on the second occurance of the code if I DON'T change the eventtime field to a short date and leave it as a date/time combination field.

is that it I wonder?

sorry, it was the 2nd occurrence
I am slightly familiar with debugging tools.
i have changed the data/time to short date
but it still crashes and the only variables showing anything on hover are the parrays values, temp=0, and i = 0)

 

[the_net_2.0]

there's really nothing else I can think of, sir.

I think your best bet now would be to have me or someone else take a look at the db you're actually trying this on. is that a possibility? what does 'crash' mean? is it stilll breaking on the same line? with the same error?

it probably wouldn't take but a couple minutes to figure out why the recordset is bringing back 0 records.

 

[cmp20]

there's really nothing else I can think of, sir.

I think your best bet now would be to have me or someone else take a look at the db you're actually trying this on. is that a possibility? what does 'crash' mean? is it stilll breaking on the same line? with the same error?

it probably wouldn't take but a couple minutes to figure out why the recordset is bringing back 0 records.

hi thenet2
its the same database you supplied earlier, but with the change to short time format in the events table
attached again with my changes now
thanks for all your help on this, I suspect you are getting a bit tired of it..

 

[the_net_2.0]

i'm using 07. which version are you using?

and yes i'm getting tired of this, but your charming wit is keeping me coming back.

I recorded a screen capture of how it works, but the file is too large to upload. but I attached two pics of what you see after running it...

 

[cmp20]

i'm using 07. which version are you using?

and yes i'm getting tired of this, but your charming wit is keeping me coming back.

I recorded a screen capture of how it works, but the file is too large to upload. but I attached two pics of what you see after running it...

ill have to get a copy of 2007 and try it, im on 2003

 

[the_net_2.0]

ill have to get a copy of 2007 and try it, im on 2003

you'd be better off figuring out the error, cmp. there's only one issue: the return of 0 records.

are you a complete novice with vba? this problem is in 1 of 2 places. this is the code that is erroring:

Set rstemp = db.OpenRecordset("SELECT [State], [EventTime], [EventTicks] " & _
                              "FROM EVENTS " & _
                              "WHERE [EventTime] = #" & udays(0, i) & "#" & _
                              " ORDER BY [EventTicks] ASC", dbOpenDynaset)

99.9% there is nothing wrong with the SELECT statement. all 3 fields in the table, right? so that leaves one possible problem: the udays() array. most likely, the 'udays(0, i)' criteria is not being found in the EventTime field. so break it on the above 'set' statement and hover over udays(0, i). if it is in error, it will probably say 'empty'. if it does, go back and break it again after the 'rstemp.getrows' line because that's where the array is populated. then hover over '.recordcount' portion of the 'getrows' action. see what it says. if it says 0, then obviously the error you're dealing with is in this 'set' statement:

Set rstemp = db.OpenRecordset("SELECT DISTINCT [EventTime] " & _
                              "FROM Events", dbOpenDynaset) 'DISTINCT RECS NEEDED

udays() = rstemp.GetRows(rstemp.RecordCount)

 

[cmp20]

you'd be better off figuring out the error, cmp. there's only one issue: the
...


the udays() array. most likely, the 'udays(0, i)' criteria is not being found in the EventTime field. so break it on the above 'set' statement and hover over udays(0, i). if it is in error, it will probably say 'empty'. i


udays() = rstemp.GetRows(rstemp.RecordCount)[/code]

Hi thenet2,
using access 2003 I can only see variable contents for some variables, not all; udays does not display its contents. Perhaps this is one difference between 2003 and 2007 -or perhaps my Access is broken and needs to be reinstalled (help is not working properly)
I will try a reinstall now.
thanks

 

[the_net_2.0]

there is no difference about hovering vars in both versions. but note that help will issue a red-x 'critical' message and block help from opening in vba and ms access both if you don't have enough memory to open it. It's happened to me before.

I think that might also be a sign of corruption of access, but I can't say for sure.

 

[cmp20]

there is no difference about hovering vars in both versions. but note that help will issue a red-x 'critical' message and block help from opening in vba and ms access both if you don't have enough memory to open it. It's happened to me before.

I think that might also be a sign of corruption of access, but I can't say for sure.

Ok, reinstalled, help working ok now but still no data on hovering over some variables.
UBound(udays, 2) is showing as = 0
so I suppose this is the problem I need to work out why. Is there any way of seeing what a dao.dataset conatins?

 

[the_net_2.0]

Ok, reinstalled, help working ok now but still no data on hovering over some variables.
UBound(udays, 2) is showing as = 0
so I suppose this is the problem I need to work out why. Is there any way of seeing what a dao.dataset conatins?

if that variable is showing as 0, cmp, than the problem is in the first recordset. why don't you just make a query object, and stick the recordset's sql in it and see if you get 0 records back? so, type the following into a query:

 SELECT DISTINCT [EventTime] 

"FROM Events ORDER BY [EventTicks]

If that returns 0 records, than the problem is elementary: the table is empty.

 

[cmp20]

hi thenet2,

ok, I added new records in the table
but
udays() = rstemp.GetRows(rstemp.RecordCount) ... = 0 Is this a problem?
and
For i = 0 To UBound(udays, 2) 'loop a table for every unique date... here i = 150

I dont know why we need tha short date, can you explain that?
thanks

 

[cmp20]

Actually, this would be pretty easy if you were using SQL Server or another db that has stored procedures along with triggers. Well, good luck. If you are ever in Dingle, raise a pint for me. I probably will not be back for a few years.

Chipper T,

can you tell me how you would do it using SQL server (express) with triggers and stored procedures? Presumably I could setup an ODBC link to the Access Events table?

if you are interested, attached is jpg taken tonight of bats in the IR beams. (I have remote access to webcams in the roost.)
cheers.

 

[the_net_2.0]

cmp,

this really should not be complicated. for your new post, try doing it in sql server express and see if that is easier for you. I'm starting to think that I'm wasting your time, and you might be getting frustrated. so, see if there is another program that is easier for you to work with this data in first.

 

[cmp20]

I cannot for the life of me get this query to work even though that date/time exists in the events table. If I remove the date criteria it works. What am I missing?

SELECT Events.State, Events.EventTime, Events.EventTicks
FROM Events
WHERE (((Events.EventTime)=#11/23/2010 16:11:34#))
ORDER BY Events.EventTicks;

 

[cmp20]

ok, thenet2 (
actually, I suspect I'm the one wasting your time, but seriously, many thanks for all the assistance. you're Ace.

 

[the_net_2.0]

if you really want to stick with me cmp, have you ever heard of gotomeeting?

it would be interesting to show you a little bit about access, but if you're at work I doubt that would be possible. security risk. but I'd do it if you would. be interesting!

and no, you're not wasting my time. I'm answering because I'm in downtime, you're at work (or at least I suspect you are). I can answer all day long and not waste anything. It's been fun actually, seeing you go through the learning process!

I cannot for the life of me get this query to work even though that date/time exists in the events table. If I remove the date criteria it works. What am I missing?

SELECT Events.State, Events.EventTime, Events.EventTicks
FROM Events
WHERE (((Events.EventTime)=#11/23/2010 16:11:34#))
ORDER BY Events.EventTicks;

I suspect it doesn't work because Access records milliseconds along with seconds, but only displays seconds. I don't know this 100%, but I think that's right.

If you store a long date in there like what is output by the Now() function, I believe getting an exact value from a WHERE clause in a query is impossible. but again, just guessing.

 

[ChipperT]

Chipper T,

can you tell me how you would do it using SQL server (express) with triggers and stored procedures? Presumably I could setup an ODBC link to the Access Events table?

if you are interested, attached is jpg taken tonight of bats in the IR beams. (I have remote access to webcams in the roost.)
cheers.

I will post some specifics hopefully after this weekend. Just wrapping up a major project here so I don't have time to sit down and think about it for the next couple of days. But basically it would entail a stored procedure that would be invoked by a trigger each time a record is posted to the database. This proc would look at the last 8 (I think it was) records posted and if a pattern was matched showing a bat entering or leaving then a new record would be posted to that effect to a separate table. In effect, your current table would be a "raw data" table and the new table a filtered data table that you would use for your information extracts. As I said, will post examples when I get the time.

Edit: I just re-read your post. I don't think you can use triggers and maybe not stored procedures either with SQL express. I have never used SQL Express so I am not sure.

 

[cmp20]

Hi thenet2,
sorry if Im being "greedy" but I would like advice from both you and chippert if possible!
Since you are approching the prob from 2 different aspects it may be that one approach is better than the other or perhaps a combination of both is most appropriate.

I am at work but homeworking thus gotomeeting isnt a problem.
This bat counting is by far the most interesting aspect of my work -something I find fun -but I dont get enough oportunities with Access in the rest of my job to learn it properly
thanks for the help