Functions stopped working

[Guest]

I have started having problems with Access2003(XP pro).
First I had a table with a date field and a delault value of "Date()". Its
form had a text box showing the date and its default was also "Date()". The
form was happy looking at existing records but in a new record it wouldn't
run; the date textbox would show #name#. When I replaced Date() with "Now()"
every thing was happy.
The silly part was that the problem only occurs on some computers(we have
five computers running the program to a split data BackEnd.
it is as though I have dimensioned the word Date as some variable, but I
have searched through the vba and found nothing.
I fixed this (temporarily?) by removing the defauld "Date()" values from the
table and form.
Then another form runs foul when it uses Len and Val functions. it won't
handle the following recordset
Set DelDoc = CurrentDb.OpenRecordset("SELECT top 1
ProductionRawMaterials.DeliveryDocket,
Val(Right([DeliveryDocket],Len([DeliveryDocket])-2)) AS DDDoc " _
& "FROM ProductionRawMaterials " _
& "Where (((ProductionRawMaterials.DeliveryDocket) Like
""rm*"")) " _
& "ORDER BY Val(Right([DeliveryDocket],Len([DeliveryDocket])-2))
DESC;", dbOpenSnapshot)

When I run this code it give a RunTime Error No 3075 and says "Function Not
available in Expressions in query expression Val(Right(....etc)).
Again it is happy on one of the computers. I have checked the vba References
and they are the same on all the computers, none appear to be missing.
I hope the above makes sense.
Thanks for any help anyone can give.
dennis howe

 

[Allen Browne]

This sounds like a problem with references.

For details on what they are and which ones you need, see:
http://allenbrowne.com/ser-38.html

 

[Guest]

Thanks Allen
It is a reference problem. None of my references are missing but I fixed the
problem by selecting "Autodesk Inventor Object Library". I tried this because
I installed AutoDesk Inventor 10 on one of the computers and that is when my
problems started.
The trouble is I don't know how to fix the problem on the computers that
don't have Inventor Installed.
Any suggestions?
Thanks
dennis

This sounds like a problem with references.

For details on what they are and which ones you need, see:
http://allenbrowne.com/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have started having problems with Access2003(XP pro).
First I had a table with a date field and a delault value of "Date()". Its
form had a text box showing the date and its default was also "Date()".
The
form was happy looking at existing records but in a new record it wouldn't
run; the date textbox would show #name#. When I replaced Date() with
"Now()"
every thing was happy.
The silly part was that the problem only occurs on some computers(we have
five computers running the program to a split data BackEnd.
it is as though I have dimensioned the word Date as some variable, but I
have searched through the vba and found nothing.
I fixed this (temporarily?) by removing the defauld "Date()" values from
the
table and form.
Then another form runs foul when it uses Len and Val functions. it won't
handle the following recordset
Set DelDoc = CurrentDb.OpenRecordset("SELECT top 1
ProductionRawMaterials.DeliveryDocket,
Val(Right([DeliveryDocket],Len([DeliveryDocket])-2)) AS DDDoc " _
& "FROM ProductionRawMaterials " _
& "Where (((ProductionRawMaterials.DeliveryDocket) Like
""rm*"")) " _
& "ORDER BY
Val(Right([DeliveryDocket],Len([DeliveryDocket])-2))
DESC;", dbOpenSnapshot)

When I run this code it give a RunTime Error No 3075 and says "Function
Not
available in Expressions in query expression Val(Right(....etc)).
Again it is happy on one of the computers. I have checked the vba
References
and they are the same on all the computers, none appear to be missing.
I hope the above makes sense.
Thanks for any help anyone can give.
dennis howe

 

[Guest]

I fixed the offending computers my removing the reference to DAO 3.6 Object
Library, closing VBA and then reselecting it.