adding criteria to selectquery form Combobox

[marchello51nl]

SELECT connection_in_process.process_nr, custommers.client_nr, custommers.organization_name, member_status.status_description, connections.port_status_nr, connections.connection_nr, slots.slot_nr, port.port_nr, port_types.port_description, vlans.vlan_type, connection_order.ipv4_number, connection_order.ipv6_number, connection_order.fqdn, connection_order.as_number, connection_status.port_status_description, switch_names.switch_name, switch_types.switch_description, switch_port.location
FROM vlans INNER JOIN (switch_types INNER JOIN (switch_status INNER JOIN (switch_port INNER JOIN (switch_names INNER JOIN (slots INNER JOIN (port INNER JOIN (port_types INNER JOIN (member_status INNER JOIN (custommers INNER JOIN ((connection_status INNER JOIN connections ON connection_status.port_status_nr = connections.port_status_nr) INNER JOIN (connection_in_process INNER JOIN connection_order ON connection_in_process.process_nr = connection_order.process_nr) ON connections.connection_nr = connection_order.connection_nr) ON custommers.client_nr = connection_in_process.client_nr) ON member_status.member_status = custommers.member_status) ON port_types.port_type = connections.port_type) ON port.port_nr = connections.port_nr) ON slots.slot_nr = connections.slot_nr) ON switch_names.switch_name_nr = connections.switch_name) ON switch_port.switchport_nr = switch_names.switchport_nr) ON switch_status.switch_status = switch_names.swich_status) ON switch_types.switch_type = switch_names.switch_type) ON vlans.vlan_nr = connection_order.vlan_nr
WHERE :?

Well i have this complex select query and five comboboxes
witch i want to use to filter data on this select query
the problem is that if i use 4 comboboxes i can filter fine
but when i come to 5 ms access conciders the query to complex
i have used [Forms]![FRM_QueryForm]! [Listbox_name] or [Forms]![FRM_QueryForm]! [Listbox_name] is null in the criteria field

But i think it would be better in vba code

Any sugestions

Marcel

 

[DougP]

Are you trying to do something if it is Null or not

IIf(isnull([Forms]![FRM_QueryForm]![Listbox_name]), true result here , false result here)

DougP, MCP

 

[marchello51nl]

IS Null does the job never the less if one makes this select query access changes the sql to a big unreadable file
and thats why i am looking for an vba answer to this question
This query (under) changes to a five page query after saving in access

PARAMETERS [Forms]![FRM_Tabblad_Manager]![Custommer_nr_overview] Long, [Forms]![FRM_Tabblad_Manager]![Custommer_name_overview] Text ( 255 ), [Forms]![FRM_Tabblad_Manager]![status_description_overview] Text ( 255 ), [Forms]![FRM_Tabblad_Manager]![port_status_overview] Text ( 255 );
SELECT connection_in_process.process_nr, custommers.client_nr, custommers.organization_name, member_status.status_description, connections.port_status_nr, connections.connection_nr, slots.slot_nr, port.port_nr, port_types.port_description, vlans.vlan_type, connection_order.ipv4_number, connection_order.ipv6_number, connection_order.fqdn, connection_order.as_number, connection_status.port_status_description, switch_names.switch_name, switch_types.switch_description, switch_port.location
FROM vlans INNER JOIN (switch_types INNER JOIN (switch_status INNER JOIN (switch_port INNER JOIN (switch_names INNER JOIN (slots INNER JOIN (port INNER JOIN (port_types INNER JOIN (member_status INNER JOIN (custommers INNER JOIN ((connection_status INNER JOIN connections ON connection_status.port_status_nr = connections.port_status_nr) INNER JOIN (connection_in_process INNER JOIN connection_order ON connection_in_process.process_nr = connection_order.process_nr) ON connections.connection_nr = connection_order.connection_nr) ON custommers.client_nr = connection_in_process.client_nr) ON member_status.member_status = custommers.member_status) ON port_types.port_type = connections.port_type) ON port.port_nr = connections.port_nr) ON slots.slot_nr = connections.slot_nr) ON switch_names.switch_name_nr = connections.switch_name) ON switch_port.switchport_nr = switch_names.switchport_nr) ON switch_status.switch_status = switch_names.swich_status) ON switch_types.switch_type = switch_names.switch_type) ON vlans.vlan_nr = connection_order.vlan_nr
WHERE (((custommers.client_nr)=[Forms]![FRM_Tabblad_Manager]![Custommer_nr_overview] Or [Forms]![FRM_Tabblad_Manager]![Custommer_nr_overview] Is Null) AND ((custommers.organization_name)=[Forms]![FRM_Tabblad_Manager]![Custommer_name_overview] Or [Forms]![FRM_Tabblad_Manager]![Custommer_name_overview]=Null) AND ((member_status.status_description)=[Forms]![FRM_Tabblad_Manager]![status_description_overview] Or [Forms]![FRM_Tabblad_Manager]![status_description_overview] Is Null));

 

[melaniews]

Me too -- me too.

Same problem -- looking for same solution -- VB code

Melanie

 

[melaniews]

I found an answer that worked for me. It's fantastic and quite simple to use.

Perhaps it will work for you.

faq702-3619

Melanie