Query works on 1 PC not another

[Supremacy]

Hi all,

First time post here and looking for some assistance with a query I have.

Running Access 2010 and developed this query to filter out data from a table between times on 2 dates (day before report run and day of report). Covers data from a shift that carries over to the next day.

Trouble is, the PC i developed on still operates the query as expected. However, on the PC the database resides (not networked just stored) and operates, the query brings up no data at all unless I remove the Time filtering.

This PC used to operate correctly up until early this year (about 18 months operated correctly) when the PC was replaced due to failure. Access version is the same and I am at wits end to what the cause is. Here is what my query looks like:

SELECT Breakdowns.BreakdownDate, Breakdowns.Time, Breakdowns.Shift, Breakdowns.Downtime, Breakdowns.Equipment, Breakdowns.Conveyor, Breakdowns.Fault, Breakdowns.Stopper, Breakdowns.Gate, Breakdowns.Dolly, Breakdowns.Carrier, Breakdowns.FaultType, Breakdowns.Comments, Breakdowns.Tradesman
FROM Breakdowns
WHERE (((Breakdowns.BreakdownDate)=Date()) AND ((Breakdowns.Time) Between #00:00:00# And #6:29:00#) AND ((Breakdowns.Shift)="Night")) OR (((Breakdowns.BreakdownDate)=Date()-1) AND ((Breakdowns.Time) Between #22:30:00# And #23:59:00#) AND ((Breakdowns.Shift)="Night"));

If anyone can shed light on why it is not giving me the data I want on this certain pc that would be great.

Thanks,
James

 

[JHB]

Try to compare both computers, how Windows display dates and times, (the Windows System Region and Language settings).

 

[CJ_London]

Since the two systems are working off different datasets, I presume it is not as simple that the other computer does not have any data to return for that criteria?

Also

...Breakdowns.Time...

Time is a reserved word and it's use can have unpredicatable results. At the very least it should be in square brackets but I would recommend changing it to say BreakdownTime.

...Breakdowns.[Time]...

 

[Supremacy]

Thanks for the replies. I have checked and double checked the windows settings. Both are identical.

In regards to the naming, I have changed it as suggested and still getting the same result.

I know for certain there is data there that should appear within the limits.

 

[vbaInet]

By the way James, it's Code tags you use for code not quote and it will look like below:

WHERE (((Breakdowns.BreakdownDate)=Date()) AND ((Breakdowns.[COLOR="red"][[/COLOR]Time[COLOR="red"]][/COLOR]) Between #00:00:00# And #[COLOR="red"]0[/COLOR]6:29:00#) AND ((Breakdowns.Shift)="Night")) OR (((Breakdowns.BreakdownDate)=Date()-1) AND ((Breakdowns.[COLOR="red"][[/COLOR]Time[COLOR="Red"]][/COLOR]) Between #22:30:00# And #23:59:00#) AND ((Breakdowns.Shift)="Night"));

You're using Time as a field name which is a reserved keyword for Access so enclose that in square brackets (as above). And for completeness I would prefix 6:26... with a 0 (as above). Here's a comprehensive list of reserved keywords:

http://allenbrowne.com/AppIssueBadWord.html

If that doesn't work, show us some records that you think should meet the criteria.

 

[Supremacy]

By the way James, it's Code tags you use for code not quote and it will look like below:

WHERE (((Breakdowns.BreakdownDate)=Date()) AND ((Breakdowns.[COLOR="red"][[/COLOR]Time[COLOR="red"]][/COLOR]) Between #00:00:00# And #[COLOR="red"]0[/COLOR]6:29:00#) AND ((Breakdowns.Shift)="Night")) OR (((Breakdowns.BreakdownDate)=Date()-1) AND ((Breakdowns.[COLOR="red"][[/COLOR]Time[COLOR="Red"]][/COLOR]) Between #22:30:00# And #23:59:00#) AND ((Breakdowns.Shift)="Night"));

You're using Time as a field name which is a reserved keyword for Access so enclose that in square brackets (as above). And for completeness I would prefix 6:26... with a 0 (as above). Here's a comprehensive list of reserved keywords:

If that doesn't work, show us some records that you think should meet the criteria.

Thanks for the info. Still showing no data after those changes either.

If I remove time filter altogether I get all records filtered by dates but it doesn't suit requirements since some fall on a previous shift.

I have a at least 2 records that fall within 10.30pm and 11.59pm on previous day and 12.00am and 6.29am on day query is run. Date filter works correctly. It's only as soon as I add the time filter.

I do not understand how code works on one PC and not on another when only the file has been opened on another computer.

 

[JHB]

Try to run the query in QBE window (at the computer with the problem), remove the time filter, then insert a simple time criteria, (input, not copy and paste), how does the criteria looks like, is it still in 24 hour format?

 

[Supremacy]

When I remove one of the 2 time filters it gives me data but it is only filtered by the date.

2 time filters = no data
1 time filter (regardless of which) = all data under date filter. Much like having no time filter in place.

 

[vbaInet]

Are you sure your Time field is a true Date/Time field? Does it contain a Date part as well?

 

[Supremacy]

Time field is definitely a date/time field set to short time (24 hour format). I have a seperate field for the date.

I keep saying to myself why it worked on one computer but stopped once I ran the database on another.

 

[CJ_London]

We seem to be trying to solve why a query isn't working when the question is about it works on one machine and not the other.

Having said that, why do you keep breakdown time as two separate fields? it would be easier if they were combined.

can you try

WHERE (BreakdownDate+BreakdownTime) Between Date()-1+#22:30:00# AND Date()+#06:29:00#

 

[vbaInet]

Hmmm... there's got to be an explanation. Is the date/time on the PC correct?

The Short Time format is just a format, it's not the real data that is stored. So the format itself could be hiding the Date part of your field so try both these filters:

WHERE Breakdowns.BreakdownDate = Date() AND TimeValue(Breakdowns.[Time]) Between #00:00:00# And #06:29:00#

WHERE Breakdowns.BreakdownDate = Date() AND (TimeValue(Breakdowns.[Time]) >= #00:00:00# Or TimeValue(Breakdowns.[Time]) <= #06:29:00#)

 

[RainLover]

The Field in the TABLE must be set as Date/Time.

Get rid of any formatting.

Design and run a query in the Grid that filters to one date only. Save this as a Temp Query. Then try to operate with some time on the filtered query. > 12:00 as one example. < 12:00 as another. Keep playing until you get a result or solve your problem.

Make sure you are working on Dummy Data. Also relink the back end.

One thing at a time. Do something test it then try another.

 

[CJ_London]

@vbaInet great minds think alike

 

[RainLover]

My guess without testing is that your AND and your OR are causing the problem.

I should have looked there first.

 

[vbaInet]

@vbaInet great minds think alike

They sure do!

 

[vbaInet]

My guess without testing is that your AND and your OR are causing the problem.

I should have looked there first.

You have a point there Rain but that was the first thing I checked and it would appear that the parentheses are in the right places. It's like this:
(1st Set of Conditions) OR (2nd Set of Conditions)

 

[Supremacy]

Tried the first code and it seems to have done the trick. Added the additional filter for previous day and now giving me correct data.

I don't know why it stopped but glad it's back working again. Doing my head in.

Thanks for the help. Will see how it lasts.

 

[vbaInet]

Remove the Short Time format and you'll probably find that there's a date part to one or more of the records. If you don't need the date part, run an update query to remove it and keep only the time. That way you won't (hopefully) need the TimeValue() function.

 

[RainLover]

Well done guys.

I seam to remember that Allen Brown has something to say about time or is it Dates.

Not sure.