Error 3085 Format$ being changed to [Format$] in query by one user

[Steven Chicago,Illinois]

I have a report that is query driven. It works great from two different
computers. A third user tries to run it and mysteriously the query changes
and she receives Error 3085. Then if I or another user tries from our
machines, we get the error too. I have figured out that something is changing
the :"Session Date By Day: Format$([Asshanrpt Work Table].[Session
Date],'Short Date')" statement to:
"Session Date By Day: [Format$]([Asshanrpt Work Table].[Session
Date],'Short Date')". The change from Format$ to [Format$] ? Why? I fix the
query and the report runs fine on all machines (but her's), until she gets on
the database and tries to run the report again. We are using Access2002-2003
on Windows XP (on our machines) with the database out on our network server
drive. This is where I wish there was a way to LOCK a query/report/etc so no
user or? could change the code. Anybody come across the same problem?

I have searched the discussion group history and seen this error mentioned,
but not in the way it is effecting us. I am also a little confused as to if I
should have put this question in the General area or queries or another area?

 

[Tom Lake]

I have a report that is query driven. It works great from two different
computers. A third user tries to run it and mysteriously the query changes
and she receives Error 3085. Then if I or another user tries from our
machines, we get the error too. I have figured out that something is
changing
the :"Session Date By Day: Format$([Asshanrpt Work Table].[Session
Date],'Short Date')" statement to:
"Session Date By Day: [Format$]([Asshanrpt Work Table].[Session
Date],'Short Date')". The change from Format$ to [Format$] ? Why?

It sounds like a broken reference on that one machine is causing Access to
interpret
Format$ as a field name or something.

Tom Lake

 

[Steven Chicago,Illinois]

Tom, I checked the references and found seven on the two machines that work
ok with the access database. On the machine that causes the problems I found
only five references. I tried to add the two but they were not on the
machine. I will check the CD disks that came with the machine and if necessay
check with Dell the MFGer of the machine.
I think you are right and this will resolve the problem. If it doesn't I
will come back to the discussion group.

Thanks for your quick help.

--
Thanks for your Help and Patience.

I have a report that is query driven. It works great from two different
computers. A third user tries to run it and mysteriously the query changes
and she receives Error 3085. Then if I or another user tries from our
machines, we get the error too. I have figured out that something is
changing
the :"Session Date By Day: Format$([Asshanrpt Work Table].[Session
Date],'Short Date')" statement to:
"Session Date By Day: [Format$]([Asshanrpt Work Table].[Session
Date],'Short Date')". The change from Format$ to [Format$] ? Why?

It sounds like a broken reference on that one machine is causing Access to
interpret
Format$ as a field name or something.

Tom Lake

 

[Dale Fye]

Steve,

When you say it "works great from two different machines" do you mean that
the application is sitting on a server or one of the individual machines, and
you are running that mdb over the network, or do you mean that each computer
has a copy of the frontend (forms, queries, reports) and the backend (data)
is on a networked computer?

If you are working in a multi-user environment, you should:

1. Always split your database into a frontend (put a copy on each users
machine) and a backend (data stored on a server or file sharing machine).
Link the data tables from the backend to the frontend

2. Never allow the users to use a instance of the frontend that is actually
loaded on the network. They should always have to use the copy that is on
their desktop or on their hard drive.

It sounds like you have violated both of these rules. Clean up the file,
run the database splitter (Tools->Database Utilities -> Database splitter) to
separate the front end from the backend. Then copy the front end to each of
the individual users computers. Open the mdb in design view on each computer
and make sure that the references are all there. Try again.

HTH
Dale

 

[John W. Vinson]

I have a report that is query driven. It works great from two different
computers. A third user tries to run it and mysteriously the query changes
and she receives Error 3085. Then if I or another user tries from our
machines, we get the error too. I have figured out that something is changing
the :"Session Date By Day: Format$([Asshanrpt Work Table].[Session
Date],'Short Date')" statement to:
"Session Date By Day: [Format$]([Asshanrpt Work Table].[Session
Date],'Short Date')". The change from Format$ to [Format$] ? Why? I fix the
query and the report runs fine on all machines (but her's), until she gets on
the database and tries to run the report again. We are using Access2002-2003
on Windows XP (on our machines) with the database out on our network server
drive. This is where I wish there was a way to LOCK a query/report/etc so no
user or? could change the code. Anybody come across the same problem?

I have searched the discussion group history and seen this error mentioned,
but not in the way it is effecting us. I am also a little confused as to if I
should have put this question in the General area or queries or another area?

Check to be sure that Name Autocorrect (aka Name Autocorrupt) is turned OFF -
perhaps it is on in her machine and off in the rest. As Tom suggests, checking
for broken references would be prudent as well.

John W. Vinson [MVP]

 

[Steven Chicago,Illinois]

Dale, Sorry It took a while to get back to your questions. I tried twice
yesterday to post and got errors connecting to the server site. So I will see
if this works today.
We are single-user database. I wish only one person to be updating/using
the database. That person has a backup person that takes over on days that
the first person is sick/vacation. Of course the backup person would like to
use their own computer to get into the database. Yes, the database is on the
network drive. The Boss wanted it there so the Network daily backup would
cover backing up the database each night. I have tried to set each person
that might go into the database to go in for Exclusive rights. This is our
first database. Centralized or Distributed? That is a good question. The Boss
picked Centralized. I have to go the way of the person that signs the
paycheck (though I did mention my concerns). For a small shop with no I.T.
staff and one user and one backup user, I hope the centralized (one copy of
the database on the Network server drive) will be a safe starting point.

If you have a link to where these Rules are posted, I would be very happy
to see them. Thanks for your concern and input,
Steve
--
Thanks for your Help and Patience.

Steve,

When you say it "works great from two different machines" do you mean that
the application is sitting on a server or one of the individual machines, and
you are running that mdb over the network, or do you mean that each computer
has a copy of the frontend (forms, queries, reports) and the backend (data)
is on a networked computer?

If you are working in a multi-user environment, you should:

1. Always split your database into a frontend (put a copy on each users
machine) and a backend (data stored on a server or file sharing machine).
Link the data tables from the backend to the frontend

2. Never allow the users to use a instance of the frontend that is actually
loaded on the network. They should always have to use the copy that is on
their desktop or on their hard drive.

It sounds like you have violated both of these rules. Clean up the file,
run the database splitter (Tools->Database Utilities -> Database splitter) to
separate the front end from the backend. Then copy the front end to each of
the individual users computers. Open the mdb in design view on each computer
and make sure that the references are all there. Try again.

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.

I have a report that is query driven. It works great from two different
computers. A third user tries to run it and mysteriously the query changes
and she receives Error 3085. Then if I or another user tries from our
machines, we get the error too. I have figured out that something is changing
the :"Session Date By Day: Format$([Asshanrpt Work Table].[Session
Date],'Short Date')" statement to:
"Session Date By Day: [Format$]([Asshanrpt Work Table].[Session
Date],'Short Date')". The change from Format$ to [Format$] ? Why? I fix the
query and the report runs fine on all machines (but her's), until she gets on
the database and tries to run the report again. We are using Access2002-2003
on Windows XP (on our machines) with the database out on our network server
drive. This is where I wish there was a way to LOCK a query/report/etc so no
user or? could change the code. Anybody come across the same problem?

I have searched the discussion group history and seen this error mentioned,
but not in the way it is effecting us. I am also a little confused as to if I
should have put this question in the General area or queries or another area?

 

[Steven Chicago,Illinois]

John, I did change the settings to not autocorrect. But I don't want to
test until I get the references fixed, that is in the Bosses hands and could
be a another week. I will come back after I have tested and post my results.
Till then I will keep the backup user using the primary user's PC if she
needs to.
Thanks,
Steve
--
Thanks for your Help and Patience.

I have a report that is query driven. It works great from two different
computers. A third user tries to run it and mysteriously the query changes
and she receives Error 3085. Then if I or another user tries from our
machines, we get the error too. I have figured out that something is changing
the :"Session Date By Day: Format$([Asshanrpt Work Table].[Session
Date],'Short Date')" statement to:
"Session Date By Day: [Format$]([Asshanrpt Work Table].[Session
Date],'Short Date')". The change from Format$ to [Format$] ? Why? I fix the
query and the report runs fine on all machines (but her's), until she gets on
the database and tries to run the report again. We are using Access2002-2003
on Windows XP (on our machines) with the database out on our network server
drive. This is where I wish there was a way to LOCK a query/report/etc so no
user or? could change the code. Anybody come across the same problem?

I have searched the discussion group history and seen this error mentioned,
but not in the way it is effecting us. I am also a little confused as to if I
should have put this question in the General area or queries or another area?

Check to be sure that Name Autocorrect (aka Name Autocorrupt) is turned OFF -
perhaps it is on in her machine and off in the rest. As Tom suggests, checking
for broken references would be prudent as well.

John W. Vinson [MVP]