Struggling with TimeValue

[todgers]

Hello
I have a query that I'm trying to run.
The first field is a date/time field (tbotime) which is updated hourly
I want to get the results to show just the data from 07:00 and no other times, although I need it to show for each day. So I'd get the data for 07:00 yesterday, today, tomorrow and so on.
I'm beleive the criteria should be some sort of timevalue but everything I've tried either throws up and error and returns no data. I've searched the forum most the afternoon but still seem to be getting no where and now I'm rather confused as to whether I write some code or just put a line into the query criteria.
Currently I have my criteria as TimeValue("07:00"), yes all afternoon for this, I got fed up of seeing errors on my other efforts and this just gives me no data.
Any help greatly appreciated, thanks

 

[Matt Greatorex]

I've got a feeling that "07:00" might be treated as a string value, because of the quotation marks.

You could try #07:00# or, failing that, post the full query (as it's often easier to see in context).

 

[todgers]

Ok I've substituted " for # and lost the brackets, tabbed to the next field and Access has made the criteria into "WHERE TimeValue#07:00#". When I run it I get a data type mismatch error.
The query has 4 fields

• tbotime-date/time
• EDM Total Backups-number
• EDM Queued backups-number
• Total Activity-EDM Total + EDM Queued

The first 3 fields are from a table 'monitoring' and the forth is a calculation made in the query, hope this helps
Thanks

 

[Matt Greatorex]

Could you post the query in it's SQL form? It may be easier to spot an abnormality seeing it in context.

 

[Brianwarnock]

I think you will find something like this works

SELECT Table1.tbodate
FROM Table1
WHERE (((Format([tbodate],"hh:nn:ss"))=#12/30/1899 7:0:0#));


Brian

 

[todgers]

OK my query now looks like this
SELECT Monitoring.tbotime, Monitoring.[EDM Total Backups], Monitoring.[EDM Queued Backups], [EDM Total Backups]+[EDM Queued Backups] AS [Total Activity]
FROM Monitoring
WHERE (((Format([tbotime],"hh:nn"))=#12/30/1899 7:0#));
If I try to run it I get a box appear saying 'select data source' what should this be?
Thanks

 

[Matt Greatorex]

OK my query now looks like this
SELECT Monitoring.tbotime, Monitoring.[EDM Total Backups], Monitoring.[EDM Queued Backups], [EDM Total Backups]+[EDM Queued Backups] AS [Total Activity]
FROM Monitoring
WHERE (((Format([tbotime],"hh:nn"))=#12/30/1899 7:0#));
If I try to run it I get a box appear saying 'select data source' what should this be?
Thanks

Guessing, but maybe clarifying all the sources could help.
It sound like at least part of the query isn't clear enough (although it seems clear to me).

SELECT Monitoring.tbotime, Monitoring.[EDM Total Backups], Monitoring.[EDM Queued Backups], Monitoring.[[EDM Total Backups]+Monitoring.[[EDM Queued Backups] AS [Total Activity]
FROM Monitoring
WHERE (((Format(Monitoring.[[tbotime],"hh:nn"))=#12/30/1899 7:0#));

 

[Brianwarnock]

I don't think that the message is anything to do with the time selection, however what format is tbotime, if it is General Date then I think that you cannot use the Short Time format but must quote seconds. To trace what is giving the message you may have to play with the query eliminating parts until you isolate what causes the message.

Brian

 

[todgers]

This may sound silly but what sort of query should I have

• Union
• Pass-Through
• Data Definition

I create my query in design view and then change the code by going in Query > SQL specific

I'm on access 97 if that matters

 

[Brianwarnock]

Isn't it just abog standard Select query, its not a Union nor is it SQL specific, the only reason to alter the code in SQL view rather than in the Design grid is that it is easier.

Have you tried recoding the query a bit at a time? Thus indicating when and why you get the message. Did you take note of my comment my the Format?

Brian

PS I don't think 97 alters the situation but I cannot test that.

 

[dsigner]

Try creating a calculated column TimeValue(thotime) and then filtering on this column #07:00#
Seems the simplest way to me.

 

[todgers]

WoooHoo!!!
It works.
I deleted the old query and started with a new one, I've lost the 'WHERE' bit and kept in the ':ss' (seconds)and its works a treat.
Thank you very much for your help!
Thats another couple of pints I owe the forummers!

 

[Brianwarnock]

Try creating a calculated column TimeValue(thotime) and then filtering on this column #07:00#
Seems the simplest way to me.

I think earlier in the thread he said that he got a data type match error when he tried this.

 

[Brianwarnock]

Glad it works , but don't understand the loss of the "WHERE", you couldn't post the working SQL just to satisfy my curiosity could you?

Brian

 

[todgers]

No problem
I have a fifth field which does not show
This is my field 'Format([tbotime],"hh:nn:ss")'
My Criteria is '#07:00:00#'

The working SQL is as follows, I think. Clearly there is a 'WHERE' in that but I deleted the 'WHERE' from the design view. Looking back it was like your first suggestion, I was just unsure where to add it!
SELECT Monitoring.tbotime, Monitoring.[EDM Total Backups], Monitoring.[EDM Queued Backups], [EDM Total Backups]+[EDM Queued Backups] AS [Total Activity]
FROM Monitoring
WHERE (((Format([tbotime],"hh:nn:ss"))=#12/30/1899 7:0:0#))
ORDER BY Monitoring.tbotime;

Thanks again