Query not combining and filtering values based on a form

[mayestom]

Hi Gina, I know I've asked a lot of you but I was wondering if I may ask of you to look at a query I'm trying to make work? Or should I start a new thread? It's very similar to the two queries you helped me create on this thread. In fact, I'm modeling it right off of those queries. I think I'm very close to working it out but just can't figure it out.

 

[GinaWhipp]

Here is fine... Only problem is you post the SQL of the query.

 

[mayestom]

Thank you Gina.
I know the naming convention is totally off. I'm doing this with someone else so I don't really have any control over that.
This is the sql of the query:

SELECT [Packaging information charts - Copy Of].OEM, [Packaging information charts - Copy Of].Program, [Packaging information charts - Copy Of].Model_Year, [Packaging information charts - Copy Of].Part_Description, [Packaging information charts - Copy Of].[P/N], [Packaging information charts - Copy Of].Take_Rate, [Packaging information charts - Copy Of].Circuit_Count, [Packaging information charts - Copy Of].Affiliate, [Packaging information charts - Copy Of].DC1, [Packaging information charts - Copy Of].Customer, [Packaging information charts - Copy Of].IC_Type, [Packaging information charts - Copy Of].IC_Container_Dim, [Packaging information charts - Copy Of].IC_Container_Code, [Packaging information charts - Copy Of].[IC_Cnt/UL], [Packaging information charts - Copy Of].IC_Std_Pack, [Packaging information charts - Copy Of].OG_Type, [Packaging information charts - Copy Of].OG_Dim, [Packaging information charts - Copy Of].OG_Cont_Code, [Packaging information charts - Copy Of].[OG_Cnt/UL], [Packaging information charts - Copy Of].OG_Std_Pack, Year([Model_Year]) AS MyYear
FROM [Packaging information charts - Copy Of];

 

[GinaWhipp]

Nothing jumping out at me, what's not working?

 

[mayestom]

I'm sorry I wasn't more specific. The query is the basis of a form that uses combo's and filters results. Like the frmMultiSelect form that you helped me create. Like I said I think I'm very close to making it work.

 

[GinaWhipp]

Hmm, okay but you still haven't told me what the problem is. Error message? Results not filtering? I don't know what I'm looking for...

 

[mayestom]

In the form frmMYCustomerProgram there are three combo (Model Year, Customer, Program). Program and Customer seem to be working. I say seem because I'm not sure if it's just that the data just happens to line up or if it's really linked properly. Model Year is the one that's not working that I know of. First you get a parameter prompt and then when you do set a value it returns nothing. No matter what you choose. Now for some reason I also don't think I have the combo boxes getting the values from the proper place. The form is supposed to be working off of table "Packaging information charts - Copy Of" but the values that you can choose from for the combo's are from other tables.

I've attached the db. If you could advise me as to what I should do I would really appreciate it.

 

[GinaWhipp]

Okay, fixed...

You had Data Type issues and a Field Name ModelYear to Model_Year should be fine now.

 

[mayestom]

I can't believe it Gina! I spent most of the day today trying to fix it (and failing) and it took you all of about 2 minutes to find and fix the problems! Thank you.

Would you mind telling me where I should look for the problems I had in it? Were they in the code or the query or mis-labeled or?? Just so I know for future

 

[GinaWhipp]

Hmm, next time I'll take longer

Issue one
MonthYear is numeric not text so removed extra quotation marks that made Access think it was text.

Issue two
MonthYear was Month_Year in the table but in the code you typed Month_Year

So, when you do run into situations like this, first check Data Types to make sure you have the right number of quotes and then confirm the name of the field in the table to make sure you spelled/entered it correctly.

 

[mayestom]

For some reason I kept looking at the spelling of ModelYear and thinking something is not right. I must have changed the spelling back and forth ten times with and without the underscore :banghead:. But couldn't figure out what was wrong with it. And I need to pay more attention to the syntax, not just thinking that it looks okay.
This just stresses the point of what you and others have told me about adopting a proper and uniform naming convention. I will when I start making my own.
Lots of newbie mistakes, right? But I'll get there with great and patient help from people like you.
Thank you.

 

[GinaWhipp]

We ALL made those mistakes in the beginning! Oh, that was not to take away from what you are experiencing... just to say you are not alone. We learned and we now pass on to others in the hopes of saving you the same stress. You should have seen my first database...well, maybe not!

 

[mayestom]

Hi Gina,
May I ask you something about a Control Form (“Assign Permissions based on Users Level…”) I found on your website?

 

[GinaWhipp]

Yep, ask away!

 

[mayestom]

I was trying to implement a security function on my db. What I was hoping to have was three levels of users. An admin group that have full access to do anything, a user’s group that can input/edit/delete data and a guest group that can only view data. Would applying your Control Form give me that ability? If so, I’m a little fuzzy on how to implement it. I think I'm fine up until step 4 and I don't need to add a temporary access button. But then I don't understand what the examples of use are? And do I create my own form to enter information or did I miss that somewhere?

 

[GinaWhipp]

Yes, that scenario, with changes, could give you exactly what you want. However, the one caveat would be... are Users being recorded anywhere? Because it can't be implemented without the Users information so they could be identified.

 

[mayestom]

are Users being recorded anywhere?

Is that what mean by:

To capture this information I use a Log On form once. The User enters their First Name, Last Name and eMail Address and I *capture* the balance.

If so, then I don’t. Is that what you mean at the end of the page where you say to create a Form to manage the information?

 

[GinaWhipp]

Yes, exactly. So, the first step to creating any kind of access permissions is to have a table that stores the Users.

 

[mayestom]

So I need to create a Log On table with a form of it's own? Is that different from the tblUsers?

 

[GinaWhipp]

The Log On Form will use tblUsers so no need for a separate table.