Extract Form

[SBBmaster09]

I have an extract form where there are 4 combo boxes. Each combo box when selected will set as filter for the query to be extracted. If the combo box is empty it just means that there is no filter, how can I do this to be able to extract the data depending on the combo boxes.

I am figuring it how will the combo boxes set as filter.

Thank you.

 

[arnelgp]

select * from table where [field1] = iif(Trim(forms!form1.combo1 & "")="",[field1], forms!form1combo1) and [field2] = iif(Trim(forms!form1.combo2 & "")="",[field2], forms!form1combo2) and [field3] = iif(Trim(forms!form1.combo3 & "")="",[field3], forms!form1combo3) and [field4] = iif(Trim(forms!form1.combo4 & "")="",[field4], forms!form1combo4);

 

[SBBmaster09]

where should i put that line, should it be in a Queries table?

 

[arnelgp]

you create a query with that template.

 

[SBBmaster09]

i dont get it sir, I already have a query but how will I use the code you gave above

 

[arnelgp]

create a query or modify your query to make it look like the above, only put the correct form and comboboxes control names.

then you set the form's recordsource to the query.

 

[SBBmaster09]

i am trying to understand what you want me to do, but here is what I have done,

I created a query named Master, I have an existing Extract form with 4 combo boxes.

under each criteria of the column I need to filter is i input the following code:

iif(Trim([Forms]![frm_Extract]![ComboboxName] & "")="",[ColumnName], [Forms]![frm_Extract]![ComboboxName])

ColumnName is the header name of that specific column,

In my codes in extract button, i have these macro ExportWithFormatting

DoCmd.RunMacro "MacroExtract"

Now I CAN'T still able to extract the correct data. Kindly explain further of your code, Thanks.

 

[arnelgp]

here is a sample db for you to study, fairly simple.
see the query1 (same one that i previously posted).
see the click event on form1.

 

[SBBmaster09]

It seems I alaready run it putting a code in the Query Table:

[Forms]![frm_Extract]![ComboBoxes] Or [Forms]![frm_Extract]![ComboBoxes] is Null

 

[SBBmaster09]

Hi, I'm trying to use your code, it seems that my data is not pulling any data. How can i extract it in an excel.

 

[SBBmaster09]

I am using the same format, what is missing? I cant extract the data I need, I am using MS Access 2013.

 

[arnelgp]

can you post your db?

 

[SBBmaster09]

Sample of Extracting when I test:

When I select Profile, Completed, it will get Completed tags only, (this is working) and when selected Incomplete, will pull all all tag as incomplete (this item is not working)

So how can I assure that it should be pulling correct data.

 

[arnelgp]

of course it will not get what you want?

MainData table have different data than tbl_Project (where you get your combos).

pay attention on your combos: ExTeam(Team) and ExGroup(SubGroup).

 

[SBBmaster09]

So it is not possible to display the data that is not from another table? As i need to display all the options needed.

 

[SBBmaster09]

Can you fix my db, im getting confused on my codes..