format problem

[anil]

hi all
i am facing weird problem.I have a simple query as

Select * from tbllog where [tbllog].[Adate]=
Format([Forms]!frmSamplingSchedule!SelectADate, "mm/dd/yyyy")

using in vba module.Our computers are connected on network on windows
2000 with windows XP as base.
This query works fine in my pc and 2 other pc,but do not work on some
pc's giving message.Cannot use the 'Format' thing,Not thw right way.
Can someone please tell me the reason.Is anything wrong in query or
should I change the way.ACtually I need the date in format
12-Jun-2006,which is set in my reigonal settings.
thanks
anil

 

[[MVP] S.Clark]

Unless Adate is a text string, then there's no need to compare a date to a
formatted date. It's just an extra step to confuse Access.

You may need to convert the textbox to a date type:
Select * from tbllog where [tbllog].[Adate]=
cdate([Forms]!frmSamplingSchedule!SelectADate)

--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

 

[John Spencer]

To do its job, Access (like most modern programs) makes use of various
external program and object libraries that provide functionality that may be
shared among applications. For example, Access always uses the Visual Basic
for Applications library, the version-appropriate Access Object Library, and
the OLE Automation library. References to the specific library files,
including their locations, are stored with your database. But these library
modules may not be in the same location on different machines, especially if
they have different versions of Microsoft Office. If you move a database
from one machine to another, these references may be "broken"; that is, one
or more of the library files may not be where the stored reference says it
is.

When this happens, you need to take steps to let Access repair the broken
reference(s) ON THE COMPUTER WHERE THE FAILURE IS OCCURING.

Here are MVP Doug Steele's instructions for how to do it:

*** Quote ***

Any time functions that previously worked suddenly don't, the first thing to
suspect is a references problem.

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

For far more than you could ever want to know about this problem, check out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

Just so you know: the problem will occur even if the library that contains
the specific function that's failing doesn't have a problem.

**** End Quote ****

So, follow those instructions and see if your problem goes away.

Check out the following reference
http://www.trigeminal.com/usenet/usenet026.asp?1036

hi all
i am facing weird problem.I have a simple query as

Select * from tbllog where [tbllog].[Adate]=
Format([Forms]!frmSamplingSchedule!SelectADate, "mm/dd/yyyy")

using in vba module.Our computers are connected on network on windows
2000 with windows XP as base.
This query works fine in my pc and 2 other pc,but do not work on some
pc's giving message.Cannot use the 'Format' thing,Not thw right way.
Can someone please tell me the reason.Is anything wrong in query or
should I change the way.ACtually I need the date in format
12-Jun-2006,which is set in my reigonal settings.
thanks
anil

 

[anil]

thanks John
I searched the problem.
It is actually in access in reports.
The message is 'Function is not available in query expression
"[tbllog].[ADate] =
Format(Forms.FrmSamplimgScheduleReports!SelectADate, "Short Date"))'.It
actually miss the format (forms........) thing in creteria .even when
I add in query in their pc,Save it and open it again ,it misses
again.But in my pc it is ok.

When I changed the query in Report as 'select * from tbllog where
tbllog.ADate=Forms!frmSamplingScheduleReports!SelectADate)' it worked
in query but when in report preview,it gives message that 'There is no
data to print.'.

I tried by using CDate thing also but that is also not working.
They are using the Same Access 2000 version and same windows and Same O
Drive where the Database is.

Please help me in this.
thanks
anil

 

[anil]

hi john
one more thing.Why the libraries are missing when I save them at one
point and next day when I open They are missing again.In other pc it is
is ok there is reason but in my pc I work daily.
Is it because I changed the program ( a bit ) on other pc or some other
reason.
thanks

regards
anil

 

[anil]

hi john
sorry one more query
We have other database also installed on our system which work on
access.They also have the modules written in vba.e.g HANSEN .
we don't have any problem ever with that.
is it to do some thing with locked database or front end or back end
case.
thanks
anil

 

[John Spencer]

Anil,

Each database has its own collection of references. The references are not
set at the Access application level, but at the level of each database. So
if you update the database and move it to another computer the references
can be broken again.

One thing that might help is to remove unneeded references.
Make a backup of your database

--Compile your vba code
--Fix any errors (Keep doing this until you have no errors in the vba code)
--Remove a reference (Access has some required references you cannot remove)
You can do this by unchecking one reference (Note its name exactly or do
this on a copy, so you can restore the reference it is needed.
--Compile your code. If the code compiles without errors then that
reference was not needed. (Repeat until you cannot remove references)

 

[anil]

Thanks john
That helped to solve the problem.
So it means that extra libraries are the missing one's on other pc or
on different pc on same server.Required Libraries will always work
fine.
thanks
anil

Also one more advice needed.I have to prepare one report on basis of
results that I have got.Tables needed are
tblResults(ResultID,ParameterID,ResultValue,ResiltIsPass,---------)
tblLocation(locationID,LocationName---------)tblSample(SampleID,SampleNO,----)

So output I need as
(LocationID,Year,Month,ParameterID,NoofSamples,Monthlyresult,YearlyResult)

Here user is getting year and month from form.In report i need only 9
parameters out of 50.So I am using query to select all 9 and then work
forward.It also involved mean, max,stddev and Count.
The problem is Each parameter has different calculation for monhly and
yearly result.
e.g parameter A has monthly result in % ,Parameter B has mean
value,Parameter C has Small Calculation involved like(mean + Stddev) in
yearly result and so on.
Also 3 parameter have results in 3 decimal places,3 in 2 decimal
places,2 in 1 decimal places,1 in %.
I have written all of them in queries and that comes to 30
queries,because 1 of monthly,1 yearly,1 combination of both.which looks
too much.it took 25 secs to run from form.
But writting in program is not working and giving regular problems as
we have to select date from form(parameter query).I want to fix them in
program so that they will not be seen.
Which way is better - in queries or Functions/Sub.in terms of
functionality and performance and safety.
Thanks
anil