Can this be done.

[Woodies_46]

Can someone tell me if this is close and if it can be done

IF ctl.control Source in CurrentDb.Querydefs("Maps and Plans Query")
then
Wherestr = Wherestr & "(((" & "[Table_name].[colum name]" & ")" & "="
& "" " & ctl.Control Source & "" " & ")"


What i'm trying to do is to get it to check to see if the text writin
in ctl.control Source is in the Maps and Plans Query somewhere then if
it is then i would like it to write it to the string called wherestr.


Thanks Nathan

 

[Maurice W. Darr]

Nathan,

Yes, but of course not in a query per se. This is a coding question.
I am not quite sure of what the ctl.control Source represents but presumably
its a textbox on a form somewhere. Likewise I am not sure where
[Table_name].[column name] comes from.
With those limitations here's what you can do.

dim i as integer
dim qdef as dao.querydef
dim Wherestr
set qdef=CurrentDb.Querydefs("Maps and Plans Query")
i=instr(qdef.sql,ctl.control Source)
if i>0 then
Wherestr = Wherestr & "(((" & "[Table_name].[colum name]" & ")" & "=" >
& "" " & ctl.Control Source & "" " & ")"
end if
qdef.close
set qdef=nothing

 

[Woodies_46]

k thanks for the replay.

I'll simplify it a bit. Say i have a table called [plans] and i have a
form called [plan search] which has a text box on it,
I would like to know how to take the value out of the box and search
the table with it and if it there then write it to a str along with the
controls name that it came out of in SQL format.

Cheers

 

[Maurice W. Darr]

Now I understand. I'm slow.
I will still have to fill in a little but here goes.

If table tblPlans has fields
Id
The_plan

and the form is PlanSeach with a field called SearchCriteria then you can
Write a query like

SELECT tblPLans.The_plan
FROM tblPLans
WHERE (((tblPLans.The_plan) Like "*" &
[Forms]![frmPlanSearch]![SearchCriteria] & "*"));

There are some subtle gottca's in this. The Like clause is not case
sensitive in Access so if you search on "Test" you will match both "A test
plan" and "A TEST plan".
Also the Like clause will is not search for delmited text so
"Test" will match "This is a testosterone day"

Maurice