Query not combining and filtering values based on a form

[GinaWhipp]

Hmm, uploading my sample and you can take a look at the Row Source for the Combo Box.

As for the Reports, you have to first create the report using the same Record Source that is on the Form. Once you do that upload a new sample file.

 

[mayestom]

Okay, yeah I see the Row Source on my combo box was empty. But I thought that filter was working??? If I were to sort the drop down in ascending order would this be the correct code?:

SELECT Format([Purchase_Date],"m-yyyy") AS MyMonthYear FROM Equipment GROUP BY Format([Purchase_Date],"m-yyyy") HAVING (((Format([Purchase_Date],"m-yyyy"))<>" ")) ORDER BY [Purchase_Date];

 

[mayestom]

Okay I created a report (rptMultiSelectqry) it seems to have the same Record Source as the form. Remember, this is from the form frmMultiSelectqry. frmSupplierDescriptionCodeqry does not seem to have this issue.

 

[GinaWhipp]

That one didn't work for me when I tested. Which one was the one that gave you problems?

 

[mayestom]

The report I created is called rptMultiSelectqry the Supplier name is a number. But if you create any filtered Report off of frmMultiSelectqry, only the Supplier name comes through, not the name.

 

[GinaWhipp]

Okay, here you go...

 

[mayestom]

It's still the same. Whenever I create a new report only the number comes through not the Supplier name. Would you mind trying to create a new report on a filtered query in frmMultiSelectqry? Filter on anything you like.

 

[mayestom]

Oh my gosh! I didn’t even notice you added a create a report button. That’s great! Thank you!

 

[GinaWhipp]

Oh, okay. let me fix the name and upload a new sample.

Glad you found the button!

 

[GinaWhipp]

Hmm, I see you are using a combo box at the table level, that is why it's showing like that. I added the table Suppliers to the Record Source and added the appropriate field. This is another reason adding those look-ups at the table level is a bad idea, you do not get the desired results and you don't know why. Instead use the appropriate tables in the query and pull the fields you want.

 

[mayestom]

I understand. I created this db before we started talking so I didn't know as much as I know now.
I tried filtering on Supplier name and I get an error message. "Run-Time error '3464' data type mismatch in criteria expression".
The filtering on Project name works and when I create a report off of it the Supplier name comes through!!!

 

[mayestom]

I'm trying to follow what you did so I understand it. You added Supplier table to qryMultiSelect and linked Supplier_Name with Supplier_ID. Correct?

 

[GinaWhipp]

My laziness I should have tested first

Okay, will fix that and re-upload...

 

[mayestom]

err wait Supplier table was already in the query? You linked the fields? Err, I'm not really sure what you did to fix it?

 

[GinaWhipp]

Okay, we're up to version 5!

 

[mayestom]

I'm sorry, I'm affraid I still get the same error message when I filter on Supplier. "Run-Time error '3464' data type mismatch in criteria expression"

 

[GinaWhipp]

Look at the one I sent, I just added the Suppliers Table, the name was in there via the Equipment table. However, because you are using a Combo Box at the Table level you mask the fact that the Equipment table is actually storing the ID not the name. To see the name you have to add the Supplier table.

 

[mayestom]

Ah I see. I understand now.

 

[mayestom]

Could that be what's triggering the run time error when filtering on Supplier name?

 

[GinaWhipp]

I don't get any error. Did you download my version 5?