Shared Database Query Fun

[AC5FF]

Here's one I have never seen before - and I can't understand why or what is happening...

I've got a shared database running great - no major issues and everything appears to be working fine. However, one of the 'tasks' that has to be done monthly is to run a batch of queries to update production slides. I have never had a problem with this; all the queries perform correctly/etc/etc/etc...

But I've started training a replacement this week and was walking him through all the production slides and how they were built - then we ran into this hiccup.

Basically, I have an Excel document that links to the database to pull all the data needed. There's 12 different tabs in this Excel document; each tab has its own query (or two) that gets the data and then creates the charts/graphs that I need. This all works fine for my replacement except for this one query. I originally thought it was a problem with the connections in Excel, but those were all set correctly. When I had him go into the database and run the query from there, he gets no data returned! However, if I am in the database the query will return the expected results.

Anyone ever see something like this happen? I checked the query's properties and it was set up correctly (I think). I didn't see a difference between this query or another query that is used and was working fine.

Not sure if it'll help, but I'm posting the SQL for the query here:

SELECT DISTINCTROW Count([IREP DWP].DOC) AS CountOfDOC, lru.Abbr, Avg([IREP DWP].Days) AS AvgOfDays, Area.Name
FROM (Area INNER JOIN (lru INNER JOIN [IREP DWP] ON lru.ID=[IREP DWP].[LRU ID]) ON Area.ID=lru.Team) INNER JOIN [IREP History] ON [IREP DWP].Date=[IREP History].Date
WHERE ((([IREP History].Date)=CDate("1-" & Format(DateAdd("m",-1,Date()),"mm") & "-" & Format(DateAdd("m",-1,Date()),"yy"))))
GROUP BY lru.Abbr, Area.Name
ORDER BY Count([IREP DWP].DOC) DESC;

Thanks!

 

[vbaInet]

CDate([COLOR=Red]"1-" & [/COLOR]Format(DateAdd("m",-1,Date()),"mm")

What is your intention there?

WHERE ((([IREP History].[COLOR=Red]Date[/COLOR])

Not a good idea to use that as a field name.

 

[AC5FF]

I didn't originally write this query; but it has always worked so I have left it alone.
Part of the data is pulled from a table "IREP History", and the items entered into that table are done through an update query on the 1st of every month, so the date data is all stored as 1 MM YY.

As far as using "Date" as a field name - I agree. But again, this DB was written a LONG time ago and would take me about 6-12 months of re-programming. Time I just don't have

 

[vbaInet]

6-12 months!!! That would be a new db

So are they stored as '1-MM-YY' or '1 MM YY'?

 

[AC5FF]

LoL yeah, 6-12 months to redesign this DB from scratch. Eliminate all the extra 'fluff' that has been put in over the years and slim it down to a more stable FE/BE setup.

The dates are stored DD-MMM-YY. But, due to your last post it got me thinking. His computer is set to a different global format. I'm betting that is the cause. Unfortunatly our systems are so locked down at work we can't get in to change the global settings like this. So, I've got our tech department logging in to the system tonight and changing his settings. Hopefully that'll fix this. It is the only thing that would make sense to me right now...

 

[vbaInet]

Could be a nice project for you. You may even get a pay rise or bonus hehe!

Let me know if the Locale settings fixed that.

 

[AC5FF]

Got a fix... New user's global date format (Vista) was set different than the date format that Access uses.

 

[vbaInet]

These New users ay! hehe!

Glad you got it sorted

 

[AC5FF]

It was your input (post #4) that got me thinking to look at date formats. Just didn't make sense why things would not work right for one user, but they would for others.. Couldn't be a DB problem - had to be external to the DB..

 

[vbaInet]

Just didn't make sense why things would not work right for one user, but they would for others..

Priviledges