Pass-Through qry 1

[SiJP]

I have created a pass through query in an Access 2k database. The SQL syntax appears to be incorrect and I think I know why (but would like to confirm my thoughts and get some help as to how to correctly write the query).

Firstly, the query is using information from 5 ODBC tables which requires the user to log into the sql server to access the data. Under the Pass-Through queries properties I have entered the appropriate ODBC;DSN=***;SERVER=***;UID=***;PWD=***.

The query contains what I believe to be two errors..

1) I have an IIf statement: IIf([TableName].[Field]<>0,&quot;Job Done&quot;,&quot;Call is Outstanding&quot AS [Call Complete?]

As you can see this is a custom defined field.. (as Call Complete) and I'm not sure if this is one of my problems.. It is asking if there is a value greater than 0 in the table's field (which is numeric) then to return &quot;Job Done&quot; to the query field, otherwise return &quot;Call is Outstanding&quot;.

2) The query is called from a form. On my form I have two unbound date fields. The query basically asked for the values in these dates fields to use as criteria for the query:

WHERE ((TableName.FieldDate) Between [Forms]![frmAccountReports]![txtDateFrom] And [Forms]![frmAccountReports]![txtDateUntil]));

If I remove both of these lines of the syntax the query will run, but for obvious reasons I would like to include them! Can anyone offer some help on this?

TIA

 

[SarahG]

When you create a pass-through query, you do not use Access SQL syntax, and you cannot refer to the Forms collection. The SQL you type in this query is the SQL you would type in SQL Server, it is sent directly to SQL Server and not processed by Access or JET e.g.

select t1.*, t2.* from table t1, table2 t2 where t1.field1=t2.field1

That is why it works when you take out those two statements, they are Access-specific.

The easiest thing to do is remove these, and then build a query on top of this pass-through query which manipulates the data as you want to, to get things working. (Incidentally, if you don't need these 5 tables for anything else, you don't have to have them linked as this pass-through query does not refer to the Access linked tables, it's going straight to SQL Server.)

When you have time to mess around with it, you could replace the IIF statement with a CASE statement (see your SQL Server help for more info).
You could also edit the query through VBA code:

dim db as database, q as querydef

set db=currentdb()
set q=db.querydefs("passthroughqueryname")

q.connect="ODBC;DSN= ...etc"
q.sql="select from t1 where fielddate betweeen '" & forms!formname!FieldDate1 & "' and '" & forms!formname!fielddate2 & "'"

 

[SiJP]

Ahhh, gottcha - I wondered why it suddenly worked when I had it as a normal Select Query!

I've taken out the troublsome statements and put them in another query that runs over the top of the pass through..

Thanks for your helpful response.

 

[sonso]

Also note that, due to the kindness of HumongoHard's heart, the SQL syntax for SQL Server is different than Access. Be careful with quotation marks and variables.

 

[kvr99]

Aha, my question exactly!

I am trying to query a linked table (linked to a SQL*Server table) and I'd like to filter by a date literal.

The Access syntax (#) doesn't work:

SELECT [fields]
FROM [linked table]
WHERE myDate > #2003-01-01#

(understandable since query is sent directly to SQL*Server)

but then again the SQL*Server syntax doesn't work either:

SELECT [fields]
FROM [linked table]
WHERE myDate > '2003-01-01'

because Access thinks I am comparing a date field to a string literal!

Any ideas???