SQL statement in form text control

[LightByrd]

Hello!
In a social services DB, one of the reports is all incidents involving a
particular client.
There are a few thousand records.
I am using an SQL statement which filters records by the client's full name
and Birthdate.
The SQL is sent to the rowsource property of a combo box.
The combination of name and DOB provides 1 unique client. (or none if the
DOB is mis-entered or is wrong)
The report (controlled by a query with parameters provided by the DOB text
control and the combo box.)

The combo box seems to be just one more thing to click through, so I thought
of changing it to a text control and aiming the SQL at it, using the
controlsource property. I can't get it to work. I printed out the help page
on controlsource, but it was vague and not much help.
The SQL statement is a straightforward SELECT DISTINCT [ClientName] FROM
[myqry] WHERE the name and DOB (from its control) match

And of course, I want the query to then fire and the report to display.
I feel kind of stupid, so I ask for help.
Thanks

 

[John W. Vinson]

Hello!
In a social services DB, one of the reports is all incidents involving a
particular client.
There are a few thousand records.
I am using an SQL statement which filters records by the client's full name
and Birthdate.
The SQL is sent to the rowsource property of a combo box.
The combination of name and DOB provides 1 unique client. (or none if the
DOB is mis-entered or is wrong)

Or two nonunique clients, if you happen to have two clients with the same name
and birthdate. Another MVP friend of mine had exactly that happen (in a small
clinic operation as I recall).

The report (controlled by a query with parameters provided by the DOB text
control and the combo box.)

Do you have a unique ClientID? If so why not use that as the criterion?

The combo box seems to be just one more thing to click through, so I thought
of changing it to a text control and aiming the SQL at it, using the
controlsource property. I can't get it to work. I printed out the help page
on controlsource, but it was vague and not much help.
The SQL statement is a straightforward SELECT DISTINCT [ClientName] FROM
[myqry] WHERE the name and DOB (from its control) match

And of course, I want the query to then fire and the report to display.
I feel kind of stupid, so I ask for help.
Thanks

To reference controls on a Form you need the full Form reference:

SELECT <whatever>
FROM tablename
WHERE [Client Name] = [Forms]![YourForm]![txtClientName]
AND [DOB] = [Forms]![YourForm]![txtDOB]

Of course this will fail if the user misspells the client name even by one
letter; using a combo box to select existing names from the table would seem
to be easier and much safer!

 

[LightByrd]

Or two nonunique clients, if you happen to have two clients with the same
name
and birthdate. Another MVP friend of mine had exactly that happen (in a
small
clinic operation as I recall).

That's why I am considering leaving the combo box.
If there were two uniques (oxymoron?) then the user could determine which
client was the desired one.
No excuse for no unique client ID -- that wont happen again!

Do you have a unique ClientID? If so why not use that as the criterion?

I was afraid you'd ask that! No. The DB was put together a little
piecemeal, since the govt. was always changing its mind.
The Incident table was/is considered the Holy Grail, and no neend for an
individual client's history, per se.
Then they wanted this new report

The combo box seems to be just one more thing to click through, so I
thought
of changing it to a text control and aiming the SQL at it, using the
controlsource property. I can't get it to work. I printed out the help
page
on controlsource, but it was vague and not much help.
The SQL statement is a straightforward SELECT DISTINCT [ClientName] FROM
[myqry] WHERE the name and DOB (from its control) match

And of course, I want the query to then fire and the report to display.
I feel kind of stupid, so I ask for help.
Thanks

To reference controls on a Form you need the full Form reference:

SELECT <whatever>
FROM tablename
WHERE [Client Name] = [Forms]![YourForm]![txtClientName]
AND [DOB] = [Forms]![YourForm]![txtDOB]

Thanks! I'll put that code on file. Too bad MS help can't stste it so
clearly

Of course this will fail if the user misspells the client name even by one
letter; using a combo box to select existing names from the table would
seem
to be easier and much safer!

I considered that. The first step is to enter only as much as you are sure
of the part of the client's Last Name.
The SQL statement has a LIKE clause. The combo box prients all names that
match both the partspec and the DOB

Thanks....see inserts above