Passing a Value to SQL from a table

[Ross]

Hi to All,

For: myName = “Jimâ€

I am storing this SQL String:

Type A
STRSQL = "SELECT tblEmployees.Employee_ID, tblEmployees.First,
tblEmployees.Last, tblEmployees.Email "
STRSQL = STRSQL & "FROM tblEmployees "
STRSQL = STRSQL & "WHERE (((tblEmployees.First) = """ & MyName & """)) "
STRSQL = STRSQL & "ORDER BY tblEmployees.Last;"

In a Memo Field of a table as follows:
Type B
SELECT tblEmployees.Employee_ID, tblEmployees.First, tblEmployees.Last,
tblEmployees.Email
FROM tblEmployees
WHERE (((tblEmployees.First) = """ & MyName & """))
ORDER BY tblEmployees.Last;

If I create a qdf from Type A (and evaluate via (debug.print)) I get

WHERE (((tblEmployees.First) = "Jim"))

However:
If I retrieve the string from the Memo Field of the table (Type B) and
create a qdf from Type B (and evaluate via (debug.print)), I get

WHERE (((tblEmployees.First) = """ & MyName & """))

How can I set a variable, strsql, equal to the SQL string in the memo field
of a table,
And then create a querydef from the strsql with “Jim†inserted into the sql
instead of “â€â€ & myName & “â€â€ ?

Thank You

Ross

 

[Michel Walsh]

You may get problem due to the memo field, in a query. Otherwise, you can
use DLookup or a JOIN to reach data from another table.


SELECT * FROM tableName WHERE fieldName = DLookup("otherField" ,
"otherTable", "someField=" & someParameter)


or


SELECT * FROM tableName INNER JOIN otherTable ON tableName.fieldName =
otherTable.otherField WHERE otherTable.someField = someParameter


are equivalent statements *IF* there is just one row where:

otherTable.someField = someParameter

is true.



Vanderghast, Access MVP

 

[Ross]

Thanks Michel,

I will ponder these things!

You may get problem due to the memo field, in a query. Otherwise, you can
use DLookup or a JOIN to reach data from another table.


SELECT * FROM tableName WHERE fieldName = DLookup("otherField" ,
"otherTable", "someField=" & someParameter)


or


SELECT * FROM tableName INNER JOIN otherTable ON tableName.fieldName =
otherTable.otherField WHERE otherTable.someField = someParameter


are equivalent statements *IF* there is just one row where:

otherTable.someField = someParameter

is true.



Vanderghast, Access MVP