Using multiple form fields to populate the WHERE clause in a query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jason - 28 Nov 2007 22:30 GMT

I am trying to use a form to allow users to generate query results in my
database. The more fields the user enters into the form the more narrow the
query results. My problem is this: I want the user to have the option the
fill in as many or as few of the form fields as they want and still have the
query function properly.

I tried: WHERE
(((tbl_DB_Table.[1st_Feild_Name])=[Forms]![frm_Form_Name]![1st_Control_Name])
AND
((tbl_DB_Table.[2nd_Field_Name])=[Forms]![frm_Form_Name]![2nd_Control_Name]));

This works great as long as the user fills in ALL of the form fields (all 2
of them for this example). The problem is that if the user wants to query on
just one of those fields they are SOL.

I tried the correct the issue using the IIf function but to no avail. Here
is the way I "tried" to get it to work:

...AND
((tbl_DB_Table.[2nd_Field_Name])=IIf([Forms]![frm_Form_Name]![2nd_Control_Name]) Is Null, Like "*",([Forms]![frm_Form_Name]![2nd_Control_Name])));

(I've also tried a few other syntax variations based on that same theme, but
no luck.)

I'm not sure weather or not I'm on the right track and just having syntax
problems or if I'm way off base and need to approach from another direction.
I know that what I want to do is possible, and I'm sure I will feel stupid
once I see the proper way to do it, but as it stands now... I'm just not
wrapping my head around it.

Thank you in advance for your help,

Jason

Reply to this Message

Jason - 28 Nov 2007 23:00 GMT

Never mind everybody! I just read Angie's Post that was made earlier today
and what do you know??? there was my answer... I have to use the [Fied_Name]
instead of Like "*".

Reply to this Message

Dale Fye - 29 Nov 2007 21:56 GMT

Jason,

Actually, I think you will need:

WHERE (ISNULL([Forms]![frm_Form_Name]![1st_Control_Name]) OR

tbl_DB_Table.[1st_Feild_Name]=[Forms]![frm_Form_Name]![1st_Control_Name])
AND (ISNULL([Forms]![frm_Form_Name]![2nd_Control_Name]) OR

tbl_DB_Table.[2nd_Field_Name]=[Forms]![frm_Form_Name]![2nd_Control_Name])

Personally, I prefer to build these types of Where clauses in the code
behind a cmd_Filter button. Something like:

Private Sub cmd_Filter_Click

Dim varFilter as Variant
varFilter = NULL

if LEN(me.1st_Control_Name & "") > 0 then
varFilter = "[1st_Field_Name] = " & me.1st_Control_Name
End If

if len(me.2nd_Control_Name & "") > 0 then
varFilter = (varFilter + " AND ") _
& "[2nd_Field_Name] = " & me.2nd_Control_Name
End If
...

me.filter = cstr(NZ(varFilter, ""))
me.filterOn = True

End Sub

Note: if the field values are text rather than numerice, you will have to
imbed quotes around the values of the control, similar to:

varFilter = "[1st_Field_Name] = '" & me.1st_Control_Name & "'"

HTH
Dale

Signature

Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.

> I am trying to use a form to allow users to generate query results in my
> database. The more fields the user enters into the form the more narrow the
[quoted text clipped - 29 lines]
>
> Jason

Reply to this Message