Requery a Query

[ddrew]

Making some good progres in this now. I want to refine my query so that records are displayed when the time goes over 10 mins but less than 15 mins. The query at the minute is:

Select Time_Reported
from TechnicalIncidentReport
where DateAdd("M",10, [Time_Reported]) <= Now()

How would I put in a 'between' criteria?

 

[namliam]

Where now between start and end

Where start is 10 minutes and end is 15 minutes.

 

[ddrew]

Sorry I dont follow!

 

[namliam]

The between syntax is...
Field between value1 and value2

I.e. Field between 5 and 10
will return all records with values 5,6,7,8,9 and 10

Alternatively you can do the same with AND
Where Field >= 5
and Field <= 10

This also returns values 5,6,7,8,9 and 10

You should be able to addapt this to suite your needs.

 

[ddrew]

Still having a problem with this! This my query but it dosen't return anything, I want it to return records from 10 mins to 15 mins from the Time_Reported

SELECT Technical_Incident_Report.BoxNo, Technical_Incident_Report.Time_Reported
FROM Technical_Incident_Report
WHERE (((DateAdd("n",10,[Time_Reported]))>=Now()) AND ((DateAdd("n",15,[Time_Reported]))<=Now()) AND ((Technical_Incident_Report.Fault_Rectified)=False));

 

[namliam]

WHERE (((DateAdd("n",10,[Time_Reported]))>=Now()) 
  AND ((DateAdd("n",15,[Time_Reported]))<=Now()) 
  AND ((Technical_Incident_Report.Fault_Rectified)=False ));

The easy way to debug this is to just fill in data yourself...
Time Reported: 11:30
Time Reported + 10: 11:40
Time Reported + 15: 11:45
Now, ie. : 11:41
Now this record you want to show, yes???

Right now if you fill in the values it looks like:
11:40 >= 11:41 (False)
11:45 <= 11:41 (False)
Instead of both returning True they both return False

So you tell me, what is the solution to this problem?

 

[ddrew]

Was it because the greater than/less that symbols were round the wrong way?

11:40 <= 11:41 (True)
11:45 >= 11:41 (True)

 

[namliam]

That would seem to be the problem yes...

or ... reverse the times...
11:41 >= 11:40 (True)
11:41 <= 11:45 (True)

Both work...

 

[ddrew]

So instead of this:

SELECT Technical_Incident_Report.BoxNo, Technical_Incident_Report.Time_Reported
FROM Technical_Incident_Report
WHERE (((DateAdd("n",10,[Time_Reported]))>=Now()) AND ((DateAdd("n",15,[Time_Reported]))<=Now()) AND ((Technical_Incident_Report.Fault_Rectified)=False));

Your saying this:

SELECT Technical_Incident_Report.BoxNo, Technical_Incident_Report.Time_Reported
FROM Technical_Incident_Report
WHERE (((DateAdd("n",10,[Time_Reported]))<=Now()) AND ((DateAdd("n",15,[Time_Reported]))>=Now()) AND ((Technical_Incident_Report.Fault_Rectified)=False));

 

[namliam]

I guess so... have you tried that???

 

[ddrew]

Yes I tried it. but it didnt work. played around some more and I can get results more that 10 mins or I can get results les than 15 mins but I cant get results between 10 amd 15 mins

 

[namliam]

I made a dummy table in an empty database...

SELECT Table30.Time_Reported, Table30.Fault_Rectified
FROM Table30
WHERE (((DateAdd("n",10,[Time_Reported]))<=Now()) 
  AND ((DateAdd("n",15,[Time_Reported]))>=Now()) 
  AND ((Table30.Fault_Rectified)=False));

This seems to work for me???

 

[ddrew]

Any chance yopu might post your dummy DB so I can see where Im goin wrong?

 

[namliam]

The code is right there...

Just create a table called "Table30"
It has 2 columns Time_Reported (date) and Fault_Rec (True/false)

Fill some dummy data... paste query... done.

 

[ddrew]

Yup thats exactly what I did, and It dont work for me! Ive got it open in front of me as I write this,

I have a table ith a time in it of 15:24, the other coloum reads false. My query is as you suggested and when I open the query it shows no records!

Ive attached the dummy DB

 

[namliam]

Acc 2007 I am stuck in 2002...

Find attached my database... offcourse you have to adjust the times....

could it be you are mistaking the lay out, confusing MM/DD/YYYY with DD/MM/YYYY?? Or something like that?

 

[ddrew]

I think I may have identified what the issue is! My Time_Reported field is set as Short Time and having tried changing yours to Short Time and then changing the test data again it didn't call any records. Is there a way that the query can be adjusted to suit?

 

[ddrew]

Figured it out, had to change Now() to Time(). Thanks for all your help Mailman

 

[namliam]

You only store Time in that field??? Big no no!!!

11 am today will be equal to 11 am tomorrow! Why not add the date to differentiate between today and tomorrow?? A 'time' field always contains a date anyway, because it is actually a "date/time" field. No possiblity to split them off.

 

[ddrew]

Dont forget what I said though, the entries get deleted on a daily basis, so it shouldnt impact!