Format problem on users PC but not on mine.....same app
RLN
I have two issues that came up when I deployed my app to my user.
Problem1:
My Report contains a field that is formatted.
My total field in the Report Footer has this for its control source:
=Format(Sum([QryTotal]),"$#,###.##")
Error message: “Format” comes up as a parm when the report
is run.
Problem2:
My form contains a field that is formatted.
a field called “txtAcct” has for its control source:
=Left([key1aba],6)
Error : instead of showing the first 6 positions of "KEY1ABA", it
shows up as “Name?#".
This app in question runs just fine on these machines:
1. my development PC (Win2000Pro)
2. a test PC in our development lab (WinNT4 SP4)
Clients's PC is WINNT4 w/ SP4.
I re-installed Access97 on the client's machine, which fixed a couple
of control errors that occurred earlier. I did a custom install and
choose everything, doing a "select all",...so they've got the *entire*
Access97 product. I also installed MDAC 2.5, which is what I
developed that app with.
If you can think of anything else I can do to fix Problems 1 & 2
above, I would appreciate the help very much.
Cordially,
RLN
Albert D. Kallal
to see if any of the references are messed up.
If the references come out ok, then you still might have mis-matched
version. Are you running the SR updates? (you should..as they contain some
serious bug fixes).
I seen this problem quite a few times...
This actually still a broken references problem. In fact, this problem is
really nasty, since functions like date, time left etc in general FAIL when
a reference is out of whack. Thus, the problem is easy to find...as
functions don't work.
However, there is also another case where these functions *STILL* work.
However, the symptoms are that functions work, but not when the expression
builder is used (that means NON compiled expressions, and those are ones
that occur in form controls, and also expressions in a query). Thus, code
works, but expressions that use functions in forms, and queries will not
work. Hence..any conditions that are based on a expression can fail.
My bet is that your version of jet is mismatched. Download the update
to jet, and *always* make sure all users are running the *same* version
of jet.
As far as I know...uninstalling office does not delete jet...since so many
other applications use jet.
I always do the following to avoid the above:
Install Access 97
Install sr1 (if required)
Install sr2b service pack
Install jet35 update.
If you don't do the above, then the breakage you describe will crop up from
time to time. You need all users to be on the *same* version of Access
*and* the same version of JET. I would also develop in Access with
references set to the DAO 3.51.
Albert D. Kallal
Edmonton, Alberta Canada
kal...@msn.com
RLN
> I always do the following to avoid the above:<
> Install Access 97<
> Install sr1 (if required)<
> Install sr2b service pack<
> Install jet35 update.<
I think this is a good idea.
User has SR2 on their machine. What version of Jet they have..I'm not
sure.
How can I tell what version of Jet a given machine is using?
"Help/About" said my user has SR2 installed.
Help About on my system did not list that I have *any* service
releases. Yikes!
On the Service updates, can I download them from the Microsoft Site?
Do I have to do them in backwards order: ie. SR1 first, then SR2,
then SR3, etc...?
One other example:
I have a report based on a query. This query has a field called
'KEY1ABA'. When this query is run in query design, it runs just fine
and KEY1ABA shows up with the other columns just fine.
In my report, that is based on this query, I have a field in my detail
line section of the report and its control source reads exactly like
this:
=LEFT$([KEY1ABA],6) --(picks up the first 6 positions of this 10
digit numeric field)
The value in this field shows up on the report as "#Name?".
All references are OK on user's PC, none are reported missing.
You mentioned you had sr1, sr2b & jet35 update. Try this please:
1. Create a table with one column- 10 digits long and call it KEY1ABA.
2. Create 2 records with data of 10 digits in each row.
3. Create a query that pulls this column.
4. Create a simple report that pulls this complete column for field1,
and =LEFT$([KEY1ABA],6) as its control source for field2.
5. Run the report and tell me if field2 returns "#Name?", or the
first 6 digits of KEY1ABA.
....and let me know your results.
Thanks so much,
Ron
"Albert D. Kallal" <kal...@msn.com> wrote in message news:<Sxya8.27194$A44.1...@news2.calgary.shaw.ca>...
RLN
> Install sr2b service pack<
> Install jet35 update.<
I have looked all over Microsoft's download center for files to
install to satisfy these three requirements, and have come up nill....
If you have links where I can obtain these updates (and in what
specific order they should be applied, I would be grateful.
Thanks.
Ron
rlnnew...@hotmail.com (RLN) wrote in message news:<fc01c86f.0202...@posting.google.com>...
Albert D. Kallal
news:fc01c86f.0202...@posting.google.com...
> Albert,
> > I always do the following to avoid the above:<
> > Install Access 97<
> > Install sr1 (if required)<
> > Install sr2b service pack<
> > Install jet35 update.<
>
> I think this is a good idea.
> User has SR2 on their machine. What version of Jet they have..I'm not
> sure.
The reason I install the jet35 is that I don't have to care what versio they
have. Once I run the above we are on the same version. The version of jet
from above is 3.51. There is a bit of confusion in the Micorsfot docs as
they mention it as 3.52 on occasions. Just remember that any of hte 3.5x
updates will show as 3.51 in the references list. Thus if you install
3.52...it will show as 3.51.
> How can I tell what version of Jet a given machine is using?
In access you can go help->About. On that there is a tab called system info.
Run that, and the win informaion window will appear. In there you can check
out the OLE DB providers.
As mentioned above, it really does not matter if you just install the jet
update.....then you don't have to worry.
> "Help/About" said my user has SR2 installed.
> Help About on my system did not list that I have *any* service
> releases. Yikes!
As mentioned...you must develop and distrubtion to the same setup.
>
> On the Service updates, can I download them from the Microsoft Site?
> Do I have to do them in backwards order: ie. SR1 first, then SR2,
> then SR3, etc...?
Yes...only the very old office 97 cd's are pre-sr1. There is a program that
is part of the sr2 download that checks your sr release......or simply just
install both of them in order.
A good starting point at ms is
http://www.microsoft.com/office/access/
>
> One other example:
> I have a report based on a query. This query has a field called
> 'KEY1ABA'. When this query is run in query design, it runs just fine
If the left function works in a query...then it should work in the report.
You are simply doing something wrong here. I would check to make sure your
name of the text box control on the report is NOT the same as the field name
when you use the left() function. Since text boxes take the same name as
fields by default, then Access can get confused if you edit the data source.
In fact, build a query and get the left function working in the query. If it
works in the query..then you can for sure get it to work in the report. In
fact, once you get it working in a query..then just make your report based
on that query.
--
RLN
updates to get all of my necessary Access configurations the same.
for reference, setups are:
My PC: Win2000 Access97 (no SR numbers shown in Help/About)
Test PC: WinNT4 SP4 Access97 (no SR numbers shown in Help/About)
Client PC: WinNT4 SP4 Access97-SR1
As for the Access97 installs on these three machines, I chose "Custom"
and did a "Select all"...gave each machine a *complete* installation
of Access97.
I have some problems on a client's PC using:
...the 'Format' function in a report,
...the 'Mid' function in a query,
...and the 'format' function in a report (to show proper currency
formatting)
For reference here, my report is Rpt1, the query that is the data
source for it is Qry1. My form is Frm1.
A control source in my report header for a total field will not work
either:
=Format(Sum([QryTotal]),"$#,###.##")
Here, on the client PC, When I run the report, the report comes up &
asks for a Format, as if "Format" was a parameter. 'QryTotal' is a
valid field in my query and it runs fine when the query is run by
itself. (this rpt has a query as it's data source) On my PC and our
test PC, the format error I described here does not occurr and the
report runs beautifully and shows up in preview print mode like it
should.
A field in my query is: Bu2: Mid([key1aba],7,2)
BU2 is a custom field consisting of only 2 positions from the KEY1ABA
field.
Here, on the client PC when this query is run, BU2 comes up as a parm
request dialog. (Like it does not recognize the 'Mid' function.)
But on my PC and the test PC this query runs just fine and BU2 show
the correct data with no parm dialogs displayed when the query is run.
Then finally on my form, I have three fields:
'txtAcct' whose control source is: "=Left([key1aba],6)"
'txtBU' whose control source is: "=Mid([key1aba],7,2)"
'txtAff' whose control source is: "=Right([key1aba],2)"
On my PC and the test PC, these fields show the data just fine.
On my client's PC each of these shows "#Name?"
All three PC's have the **exact same** .mdb file.
This ACC97 app runs fine on my machine (Win2K), and on a test machine
in our
lab (WinNT4), but bombs on the users machine (WinNT4) when trying to
run the
left$, Mid or format function as described above. I know for sure the
user has Office 97-
SR1, but am unsure if they have the Jet35 update.
Question regarding Jet versions:
My OLEDB configuration on my machine shows:
Microsoft Jet 3.51 OLE DB Provider 3.52.1527.4
Microsoft Jet 4.0 OLE DB Provider 4.0.4331.4
....so which version of Jet is Access97 using when I'm in
my development environment?
Thanks for your help..
Ron
Rick Brandt
> I checked the MS site you mentioned, and I still cannot find the
> updates to get all of my necessary Access configurations the same.
>
> for reference, setups are:
> My PC: Win2000 Access97 (no SR numbers shown in Help/About)
> Test PC: WinNT4 SP4 Access97 (no SR numbers shown in Help/About)
> Client PC: WinNT4 SP4 Access97-SR1
> As for the Access97 installs on these three machines, I chose "Custom"
> and did a "Select all"...gave each machine a *complete* installation
> of Access97.
>
Instead of concentrating on how Windows/Access is installed, have you
actually opened up the references dialog and checked them out on the PC that
doesn't work?
RLN
you
actually opened up the references dialog and checked them out on the
PC that
doesn't work?<<
Yes. No references are reported as "missing" on the client's PC.
I opened up a code module, then did "tools/references".
There were 6 items checked at the top of the list. They are:
- Visual Basic for Applications
- Access 8.0 Object Library
- DAO 3.51 Object Library
- Excel 8.0 Object Library
- MS Calendar Control 8.0
- MS Rich TextBox Control 6.0
I'm very confused as to why these errors are ocurring. I'm stumped,
to be honest. Here are the error's I'm encountering on the user' PC.
(none of these occur on my machine or the test machine in our lab.)
In a query:
some of the errors I get on the client PC:
(A valid field is "KEY1ABA", it is 10 digits long.)
In a query design window, if I enter the expression:
left([key1aba],6)
left$([key1aba,6)
Mid([key1aba],2,3)
Mid$([key1aba],2,3) ...all return "#error" when I try to run the
query.
All I'm trying to do is pick out the first 6 positions of "Key1ABA".
In a report:
In report designer, my report already contains the field "KEY1ABA".
if I copy that field, change the control source of the new field to:
=left$([key1aba,6)
then run it "Left$ shows up as a "parameter value". (my report isn't
driven by any parms at all.)
In this report field list I have a field called 'QryTotal', which, if
dragged onto the report, displays fine. But I'd like to format it as
money, so to be more pleasing to the user. So, I do
=Format(Sum([QryTotal]),"$#,###.##") ...runs fine on my PC. On users
PC: this format statement yields: "The expression you entered
contains invalid syntax or you need to enclose your text data in
quotes."
In a form:
left([key1aba],6)
left$([key1aba,6)
on a field both yield:
"The expression you entered contains invalid syntax or you need to
enclose your text data in quotes."
I *can*, however use:
Mid([key1aba],1,6)
Mid$([key1aba],1,6) ...syntactically will be accepted; but when I run
the form, these two fields yield "#Name?", instead of displaying the
first 6 values of KEY1ABA. (Here again, KEY1ABA is in the field list
of the form's data source, and this field was displayed in its
entirety on the form just fine.
At this point, I'm not sure, as you mentioned earlier, that JET files
are the issue here. But for whatever its worth, here are the
date-timestamps of files I got off of the users machine:
MSJET35.DLL - Version: 3.51.2026.0
MSJET40.DLL - 4.002927.4
MSJETOLEDB.DLL - 4.002927.2
(following are some 'international' files; unsure if these are
applicable or not)
MSJTINT32.DLL - 3.000.2712 JetDB Engine Int'l
MSJTINT35.DLL - 3.51.0623.0 JetDB Engine Int'l
MSJTINT40.DLL - 4.00.2927.2 JetDB Engine Int'l
Jet Error files:
MSJTER35.dll - 3.51.0623.0 - Jet DB Engine Error DLL
MSJTER40.dll - 4.00.2927.2 - Jet DB Engine Error DLL
MSJTES40.dll - 4.00.2927.8 - Jet Expression Service
If all these files exist on the user's PC, which version of Jet is
Access97/SR2 actually using. and how can I check to be sure? How can
I check to see which version of Jet am I running on my PC?
I apologize for the length of this post, but this problem is driving
me and my user nuts.
Thanks for any solutions you can provide here. I really do appreciate
it sincerely.
Regards,
Ron
Clive Bolton
numeric field and not text on which Left() can be used.
Try forcing the data type with:
Left$(CStr([key1aba]),6)
^^^^
Instal the Jet35SP3.Exe which applies all the updates, I believe.
> MSJET35.DLL - Version: 3.51.2026.0
Mine is 3.51.3203.0
Don't check on which version anyone has - just run the service pack and then
you will know!
PS if your dev machine is behind the times, then you could well need to
/Decompile and recompile, then reissue the app to everyone.
^^^^
> ... "Left$ shows up as a "parameter value". (my report isn't
> driven by any parms at all.)
Oh, yes it is - you are being told it is. In fact it simply means that the
words cannot be found in any of the dictionaries available to the expression
handler.
Why "parameter"? Because the report generator takes the SQL part of the
recordsource and uses it to create its own query to obtain those fields and
all the other fields needed to evaluate expressions, grouping, etc on the
report.
^^^^
Besides that, you seem still to have a references problem. If it quacks and
walks like a bad reference, it is a bad reference. Try unclicking all but
the first three references, closing, re-opening. Check the references again.
Note that the referenced library may not be the one giving the problem - I
suspect that Access hits one reference problem and gives up on loading any
more. Close Access down and reload to ensure a clean start.
Clive
"RLN" <rlnnew...@hotmail.com> wrote in message
news:fc01c86f.02021...@posting.google.com...
RLN
info there...the field is a 10 position text field, because a valid
"key1aba" value can be "102010E68E".
>>Try forcing the data type with: Left$(CStr([key1aba]),6)<<
I did not think of this...I'll try it this way and see what happens.
>>Install the Jet35SP3.Exe which applies all the updates, I believe.<<
Good idea....is this file available on the MS web site? Only reason I
ask is, I had trouble finding jet35upd.exe (which I think is an older
update)
>>Don't check on which version anyone has (of msjet35.dll) - just run
the service pack and then you will know!<<
I agree here, but if I run Jet35SP3.Exe, which version of msjet35.dll
will it install? Reason I aks this is, I installed the same
msjet35.dll to the users' PC that was on my machine late last night
and the problems went away and the app ran just fine.
(Users' old version: of MSJET35.DLL - 3.51.2026.0
my version was something like 3.51.3325.....)
>>PS if your dev machine is behind the times, then you could well need
to
/Decompile and recompile, then reissue the app to everyone.<<
This is sort of another issue...I have never decompiled/recompiled
then reissued the app...not sure what you mean by this
process...guessI'll need to read up on this one. I *have* done
"compile all modules", done a save, then exited the app, compacted it,
*then* delivered it to the users' PC. Is this sort owf what you're
talking about here when you speak of "/Decompile and recompile"?
>>you seem still to have a references problem....Try unclicking all
but
the first three references, closing, re-opening. <<
There were 6 items (that appear in this order) checked at the top of
the list of "Tools/References". They are:
- Visual Basic for Applications
- Access 8.0 Object Library
- DAO 3.51 Object Library
- Excel 8.0 Object Library
- MS Calendar Control 8.0
- MS Rich TextBox Control 6.0
If I uncheck the Excel 8.0 Lib and reopen the app, my app won't be
able to export the data to Excel like it needs to; seems like this
unchecking process would open up another can of worms. (just my
observation here)
>>Check the references again.
Note that the referenced library may not be the one giving the problem
- I
suspect that Access hits one reference problem and gives up on loading
any
more. Close Access down and reload to ensure a clean start.<<
So am I understanding you correctly here to mean that Access could go
thru the list of loading the references: VBA, Access 8.0 Lib, DAO
3.51, then quit?
Thanks for your assistance here Clive.
I appreciate your taking the time to reply.
Ron
Clive Bolton
> Good idea....is this file available on the MS web site? Only reason I
> ask is, I had trouble finding jet35upd.exe (which I think is an older
> update)
Yes. I may try re-downloading it to make sure I have the latest release
> >>Don't check on which version anyone has (of msjet35.dll) - just run
> the service pack and then you will know!<<
> I agree here, but if I run Jet35SP3.Exe, which version of msjet35.dll
> will it install?
Does it matter, as long as all are the same?
> >>PS if your dev machine is behind the times, then you could well need
> to
> /Decompile and recompile, then reissue the app to everyone.<<
> This is sort of another issue...I have never decompiled/recompiled
> then reissued the app...not sure what you mean by this
> process...guessI'll need to read up on this one. I *have* done
> "compile all modules", done a save, then exited the app, compacted it,
> *then* delivered it to the users' PC. Is this sort owf what you're
> talking about here when you speak of "/Decompile and recompile"?
No. that is not what I mean. /Decompile strips out all the compiled code,
leaving just the text in place. (Backup first!)
When that is finished, I do not compile immediately. Instead I close and
Compact. The I re-open and compile all. This will ensure that all the
references are updated to those that are present on your machine now.
> >>you seem still to have a references problem....Try unclicking all
> but
> the first three references, closing, re-opening. <<
> There were 6 items (that appear in this order) checked at the top of
> the list of "Tools/References". They are:
> - Visual Basic for Applications
> - Access 8.0 Object Library
> - DAO 3.51 Object Library
> - Excel 8.0 Object Library
> - MS Calendar Control 8.0
> - MS Rich TextBox Control 6.0
> If I uncheck the Excel 8.0 Lib and reopen the app, my app won't be
> able to export the data to Excel like it needs to; seems like this
> unchecking process would open up another can of worms. (just my
> observation here)
Just do it. See what happens. Close. Reopen and Reselect them if necessary.
The point is to force Access to reset the references.
>
> >>Check the references again.
> Note that the referenced library may not be the one giving the problem
> - I
> suspect that Access hits one reference problem and gives up on loading
> any
> more. Close Access down and reload to ensure a clean start.<<
> So am I understanding you correctly here to mean that Access could go
> thru the list of loading the references: VBA, Access 8.0 Lib, DAO
> 3.51, then quit?
Whatever order Access chooses to use...
RLN
> leaving just the text in place. (Backup first!)
> When that is finished, I do not compile immediately. Instead I close and
> Compact. The I re-open and compile all. This will ensure that all the
> references are updated to those that are present on your machine now.
This was helpful, Clive, thank you.
Ron