date problem in parameter query

[anil]

hi all
i have written a query as follows:

SELECT LabData.ParameterName, LabData.SAMPLE_NO, Month([SAMPLE DATE])
AS AMonth, Year([SAMPLE DATE]) AS Ayear, LabData.RESULT
FROM LabData
WHERE (((LabData.ParameterName)=[Forms]![Form2]![SelectParameter]) AND
((Month([SAMPLE DATE]))=[Forms]![Form2]![Select_Month]) AND
((Year([SAMPLE DATE]))=[Forms]![Form2]![Select_Year]));

This query is to get monthly result of the samples.
in the form i have used the Date-Time picker where i have set the date
as month and year selecting the custom property(3).
This query works when i run from query , but i enter month and year in
form , no results are recieved.
can u please help me like where is the mistake
thanks
anil

 

[Michel Walsh]

Hi,


It seems that Select_Month and Select_Year hold something else that what you
think they do. DO NOT RELAY on what they DISPLAY in the form. Have the form
open, have the control filled with a value, but instead of executing the
query, open the Debug Immediate Window (generally Ctrl G, will do), and
there, type:


? Forms!Form2!Select_Month



? Forms!Form2!Select_Year



I suspect they both contain the full year_month_date value, but DISPLAY only
the year part, or the month part, because of a FORMAT applied to their
value. In other word, the full date #2/2/2006# will display 2006, if
formatted to display just the year, but in your statement, it is the real
value, not its formatted displayed text, that is considered and there,
asking if

2006 = #2/2/2006#


will fail.



Hoping it may help,
Vanderghast, Access MVP

 

[anil]

hi michel
thanks. you are right.
i understand the mistake.so can u help me how to improve this
mistake.What should i do in query or form so as to get desired result
of getting month and year in query result from input of form.
thanks
anil

 

[anil]

hi michel
i solved the problem by using Month([Forms]!form2!Select_month) giving
me Amonth= month (from Form)
Thanks for explaining in such a beautiful manner
anil

 

[anil]

hi michel
i ahve now different problem.
now when i select parameter,month and date from form it woks fine,it
even works fine when form is open and i run the query in the query
window.but if the form is closed i am not able to run the query.I have
checked creteria conditions but still not working.when i run immediate
window it gives message 'form2 not found'.
can u help me on this .
thanks
anil

 

[Michel Walsh]

Hi,


That is because you use the form in your query, when you use

Forms![Form2]![Select_Month]

as example. The form Form2 must be open, can be invisible, but must be open.

You must also use such expression with DoCmd, the User Interface, a
RowSource, a RecordSource, as example, but not using it with CurrentDb which
does not understand that syntax (FORMS!formName!ControlName).


Hoping it may help,
Vanderghast, Access MVP

 

[anil]

thanks michel