Form variable passed to query

[TimE]

Not sure if I am asking the right question, but....

I would like to pass a string variable to a query but it does not seem to work.

In the query, my criteria for the date field is (and works):

>=[forms]![frmDisposition]![FromDate] And <=[forms]![frmDisposition]![ToDate]

But, this is not working for the ID field criteria:

[forms]![frmDisposition]![ID]

In the forms code, I have a string based on the result of 3 check box. I tried using an unbound (ID) control to display the string so I know that the value of the string is correct. EX: "FW" Or "MA" Or "PD"

Is it possible to pass the value of the string to the query or do I need to try and pass the value of the unbound control to the query?

 

[boblarson]

You aren't talking about a "variable" but an unbound form control. You should be able to display the correct value ("FW," "MA," or "PD) without the form's code, by using an immediate if statement (IIF).

The criteria for using a control on a form in the query is:

=[Forms]![YourFormNameHere].[YourControlNameHere]

For controls, especially unbound, you should use the dot operator (.) instead of the bang operator (!).

Also, to reference any form controls, make sure that if they are bound that the control names are different from the fields they are bound to, which doesn't happen automatically because Microsoft didn't set it up that way.

 

[TimE]

Thank you for your help Bob.

Not sure if I am missing something, besides the obvious....knowledge.

The criteria of the query, I have:

[forms]![frmDisposition].[SiteValue]

The unbound control on the form is named SiteValue

The current value is "FW" Or "MA" Or "PA", but it appears it is not working.

My goal is to have the above value in the criteria of the Site in the query.

I hope this makes sense to you because the more I look at it, the more I want to change jobs.

 

[boblarson]

Can you post your database? I'm thinking that there's something that we're just not communicating and that happens frequently - I might say to do one thing, but to you that one thing is obviously done, when it really isn't because we are really talking about different things. It happens quite often really and we just have to get over that hump.

To post the database, be sure to do Tools > Database Tools > Compact and Repair and then after that to zip it up using WinZip or something like it. It needs to be 393Kb or less after zipping.

 

[TimE]

Thank you again.

I removed everything except the table, query and form that I am needing help with.

When looking at it, please be gentle regarding my code. I have alot of redundant code that I need help with.

 

[boblarson]

Okay, I am attaching your form fixed, but there are other issues you should address.

1. You should use an option group instead of individual checkboxes (which I did and just disabled your existing ones so you can see the difference between what you had and what I did).

2. The date field you were dealing with also included time and you can't pull it based on the text boxes the way you had it. It wasn't going to work. So, what you do is create a separate field in your query (unchecked so it doesn't show up) and format to date only and then base the criteria off of that field. See what I did.

3. You should not be storign the site text in the table. It should be a foreign key number as what you are doing now is not normalized. You should have the name, or abbreviation only ONCE in your database (hence a site's table) which has a long integer (autonumber in the site table) as the foreign key in the other table(s) so that if the name, or abbreviation, changes you can just make the change once as the number won't change.

 

[TimE]

Thank you Bob. I hope you did not pull all of your hair out looking at the attachment.

The individual checkboxes was so I could choose any combination of the 3 sites. Should I add enough checkboxes to cover the combinations and alter the IIf statement?

I am linking to an existing table that is using the text for Site ID vs a foreign key. I will not be able to modify the table.