Access Project 2002 Passing Parameters to a Query

[CLM]

Hello,

I have sent textbox parameters to feed sql statements via vba code in Access
97, 2000 and even 2002 but with Access Project that doesn't seem to work.

Does anyone know how I can retrieve input from a form textbox and send it to
a query's SQL via Code?

Thx,

CLM

 

[Van T. Dinh]

The difficulty is that you need to integrate the *server-base* query with
Access object.

See if the following Microsoft Knowledge Base helps:

http://support.microsoft.com/?id=278400

 

[CLM]

Ok that I get if I am basing my form on an existing table.

My form basically has 3 fields right now: txtAccountNum, txtStartDate,
txtEndDate
I want to take a bit of code similar to the following that I would normally
use:
Dim strSQL as string

strSQL = "SELECT Data.* " _
& "FROM data " _
& "Where AcctNum = " & txtAccountNum _
& "AND TransDate between " txtStartDate & " AND " & txtEndDate
& ";"

"qryGetData".sql = strSQL

I would take it that I create a function of fn_GetData
SELECT Data.*
FROM Data
WHERE (AcctNum = @AcctNum)
AND (TransDate between @StartDate AND @EndDate)

Then within the form code would I then set @AcctNum = txtAccountNum,
@StartDate = txtStartDate and @EndDate = txtEndDate??

Please advise,

Thx,

CLM

 

[Van T. Dinh]

Actually, you Query is _not_ parametrised since you constructed the SQL
String by code and the end result (SQL String) is explicit without any
parameter.

However, since the query / view is processed by the SQL Server, you will
need to use the correct MS SQL Server syntax for the 2 date values. There
are a number of different explicit date formats you can use but you will
need to check the B.O.L. (SQL Server's Books-On-Line) for the correct
syntax.

Also, get rid of the semi-colon at the end. SQL does use this and including
it _may_ create problems (?).

 

[CLM]

OK so I am on the write track, but last night I attempted to use the
AFterChange event to assign the txtAcct to the @AcctNum and it didn't like it
very much. Is there a reference I should be setting or is my syntax way off
the mark?

Thx,

CLM

 

[Van T. Dinh]

Don't worry about those @ things!

I wrote that your Query / View / SQL String is *not* parametrised as you
constructed the SQL String (by code) using explicit value so the @ thing is
not applicable.

Since you use ADP, the SQL String is handled by the SQL Server engine, not
Access, your SQL String must follow the syntax of T-SQL, espe cially on
delimiting Text and Data *explicit* values. For example, in JET SQL, you
can use either single-quote or double-quote as String delimiter but in
T-SQL, you can only use sigle-quote. Delimiting explit date values in T-SQL
is rather different than JET SQL (#-delimited). You will need to check
B.O.L. for the correct T-SQL data syntax.