Query Criteria Access Project/SQL Server

[mvdkooij]

Hi,

I have a problem with giving a criteria for a query.
I have experience with BETWEEN and FORMS!FrmName!Control.value in ACCESS but this does not work with Access Project/SQL Server.

I want to give a criteria in a form and with that criteria I want to open a report.

But when I enter a criteria in a query it says: YOU ENTRY CANNOT BE CONVERTED TO A VALID DATE TIME VALUE.

So pls help me.

 

[cmmrfrds]

When you say query are you talking about a View? Views are stored in sql server just like stored procedures. Maybe elaborate a little and show syntax and control flow in your example.

 

[mvdkooij]

Indeed a view.

I have a table MCO with MCOnumber en Date
In a textbox on a form I want to give a date and then a report must print on that date.

 

[cmmrfrds]

Views in sql server do not accept parameters. Stored Procedures (SP's) and User Defined Functions (UDF) will accept parameters and return resultsets. Is there some reason you need to use a View instead of an SP, which has an interface in Microsoft Access? Look into using an SP since the parameters can be passed a variety of ways in Microsoft Access.

 

[mvdkooij]

Thnks.
The reason I am using a View is that my Access does give errors when I want to make a Stored Procedure. It says it can't find the procedures....
Very strange, but everything else is working....

 

[cmmrfrds]

If you are not the database owner then all the objects need to be prefixed with dbo. Try putting dbo in front of your object name.

dbo.yourSP
dbo.yourtable

 

[mvdkooij]

I still have this problem.
There are some SYSTEM-SP in de SQL-server but when I try to open them it results in an error.
Same as I want to create a new one (in ACCESS). It says "can't find the object".

Pls help....maybe the installation from Access is incomplete??

 

[cmmrfrds]

Show an example of the code you are using. What reference ADO libraries do you have set. ActiveX Objects 2.7? What version of sql server? Do you have the correct permissions on sql server?

 

[mvdkooij]

SQL Servers is (I Think) 2000

Code is ok, as I have tried a stored procedure from Northwind and that one is also not working.

Ref. to permissions, I am the database owner and have permission to execute SP. Which more permissions do I need?

I am a rookie: where can I see about the ADO libraries?

 

[cmmrfrds]

To see what ADO library is referenced. While in some vba code go up to Tools on the menubar and select References. Check that you have version 2.5 or later of the ADO library.

 

[mvdkooij]

In references I have:

* Visual Basic for appl.
* MS Acces 9.0 Object Library
* MS ActiveX Data Objects 2.7 Library
* MS ADO Ext.2.7 for DDL and security
* OLE Automations

Is this okay?

 

[uludude1]

My guess is you are using a crosstab query. If you change it to a select it will work. I have a similar (or perhaps the same) problem. I try to pull criteria from a simple form into a crosstab to create a report. It says that MS Jet does not recognize Frm![..] as a valid field name or expression. It has something to do with the TRANSFORM and PIVOT

TRANSFORM Count(UserTable01.FirmFile) AS CountOfFirmFile
SELECT UserTable01.USR4VAL, UserTable01.ORDDATE, UserTable01.USR1VAL, Count(UserTable01.FirmFile) AS [Total Of FirmFile]
FROM UserTable01
WHERE (((UserTable01.ORDDATE) Between [frmtbl1]![Beg Date] And [frmtbl1]![End Date]) AND ((UserTable01.USR1VAL)=[frmtbl1]![Salesperson name]))
GROUP BY UserTable01.USR4VAL, UserTable01.ORDDATE, UserTable01.USR1VAL
PIVOT UserTable01.PURCREFI;

wierd stuff

 

[cmmrfrds]

Your reference libraries look up to date.

How old is the install copy of Access 2000. There have been fixes/patches since the initial release. Check the Microsoft site for these.

At what point do you get the error on creating a stored procedure. If you click New stored procedure, can you enter any code or don't you get that far.

 

[mvdkooij]

In Access, if I want to create a new one it gives an error.

If I create it in SQL it works, but as soon as I want to start it in Access it says again it can't find the procedure (but it does mention the the SP I created in SQL).

And the SP I made asks for a beginning and en ending date and Access asks these things, but as soon as these items are entered the errors occurs.

It's is really strange!!!!

 

[tlbroadbent]

For future reference, there is now a separate forum for Microsoft: Access Project (ADP). It may be more appropriate to post your Access Project related articles in the new forum. See forum958. If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm