Check boxes excludes needed information

[JPG4]

Hello,

I am creating a form in Access that uses the days of the week (I am looking at flight patterns for an airline). I have created a check box for each day; therefore I have 7 total. The checkboxes relate back to a query (that are in the yes "-1" and no "0" format) so that when I check the box for Monday, the query comes back with flights that ONLY operate on Monday ("yes").

This is where my problem lies: what seems to be happening is when I check "Monday" and leave the rest unchecked, they are being recognized as "no" and therefore my query is returning only the results that have the pattern:
Mon: Yes
Tues: No
Wed: No
Thurs: No
Fri: No
Sat: No
Sun: No

Is there a way to make it so that when I check Monday, that it returns ANY data that has yes in Monday regardless of what the other days are? In other words, so it follows a pattern similar to this:
Mon: Yes
Tues: Yes OR No
Wed: Yes OR No
Thurs: Yes OR No
Fri: Yes OR No
Sat: Yes OR No
Sun: Yes OR No

Basically, I want to say if the box if checked then the value is -1 or yes, but if it is unchecked then the value is -1 (yes) OR 0 (no).

I am very new to Access, so I apologize if none of this makes any sense. I appreciate any help anyone can offer. I'm willing to try anything at this point!

JPG4

[Scott Price]

Hello JPG4 and welcome to TSDN!

Could you post your current SQL code for us? Go to the query design view, right-click on the top of the design view window, choose SQL view, then copy and paste it over here... Once you have pasted it, wrap it with the code tags, by selecting it all, then clicking the # button on the top of this forums' reply window. Manually edit the first [code] tag to look exactly like this: [code=sql]... Thanks!

Regards,
Scott

[JPG4]

Hi Scott!

Thank you for a quick reply! I have copied and pasted the code below as you requested. (By the way, thank you for explaining how to do that... I would have been lost!) I have 4 queries total, but this it the final one (the one that relates back to the check boxes on the form). Let me know if you need anything else and again, thank you for your help!

SELECT [FREQ YesNo].MONTH, [FREQ YesNo].CXR, [FREQ YesNo].DEPT, [FREQ YesNo].ARR, [FREQ YesNo].STAGE, [FREQ YesNo].FLT, [FREQ YesNo].EQUIP, [FREQ YesNo].CAP, [FREQ YesNo].[D-TIME], [FREQ YesNo].[A-TIME], [FREQ YesNo].BLOCK, [FREQ YesNo].FREQ, [FREQ YesNo].Mon, [FREQ YesNo].Tues, [FREQ YesNo].Wed, [FREQ YesNo].Thurs, [FREQ YesNo].Fri, [FREQ YesNo].Sat, [FREQ YesNo].Sun
FROM [FREQ YesNo]
WHERE ((([FREQ YesNo].Mon)=[Forms]![OAG]![Mon1]) AND (([FREQ YesNo].Tues)=[Forms]![OAG]![tues1]) AND (([FREQ YesNo].Wed)=[Forms]![OAG]![wed1]) AND (([FREQ YesNo].Thurs)=[Forms]![OAG]![thurs1]) AND (([FREQ YesNo].Fri)=[Forms]![OAG]![fri1]) AND (([FREQ YesNo].Sat)=[Forms]![OAG]![sat1]) AND (([FREQ YesNo].Sun)=[Forms]![OAG]![sun1]));

[Scott Price]

Thanks for posting the SQL! You're welcome for the explanation! It's not always easy to tell if someone knows how already, so I err on the side of caution :-)

First thought, why not change your 7 fields in the FREQ YesNo table to one field named something like DayOfWeek. If I'm not mistaken, most of the time you will enter data for which flight runs when and where in this way: Mon - Thurs, Mon, Weds, Fri, etc? The SQL then for choosing ANY flight that operates on Monday will be like this:

SELECT [FREQ YesNo].FreqID, [FREQ YesNo].CXR, [FREQ YesNo].DayOfWeek
FROM [FREQ YesNo]
WHERE ((([FREQ YesNo].DayOfWeek) Like ("*Mon*")));

I tested this in my test db and it works perfectly, finding the four test records I entered with this form: Mon - Thurs ; Mon, Weds, Fri ; Mon ; Mon - Fri

Let me know what you think on this suggestion!

Regards,
Scott

[JPG4]

Scott,

I like your idea, however, I am not quite sure how to implement it. See, the days of the week (frequency) come from a table that is downloaded from a website into Excel and the uploaded into Access. The way the table comes is in numbers, where 1=Mon, 2=Tues, etc. The table comes with the frequencies all listed in one column in this format: 1..4.67 (Mon, Thurs, Sat, Sun), or 1...... (Only Monday), etc... So, what I did was set up a query to separate them out into 7 different columns, and then did another query that contained an IIF statement that changed them into Yes/No as opposed to numbers and periods.

If I go back to how the data originally comes (12..5.7), and I type in "Like "*1*" in the criteria, you are correct, it works perfectly! So the next step is how do I link this back to the form's check boxes so that when I check Monday it means "Like "*1*" as opposed to "Yes" for Monday?

Thanks again for all of your guidance!

[JPG4]

Hi Scott,

I have decided to try to take a new approach (unless you can help me with the above issue). I have posted a new discussion under the title "Linking a Text Box in a Form to the Criteria in a Query using "Like" "

Since you have an idea of what I am looking for, any help or suggestions would be wonderful! :-)

Thanks!

[Scott Price]

Sorry to not get back to you earlier than this, I had to leave the computer for a few hours this afternoon...

What you are trying to do is quite simple actually.

in the criteria section of your query design view, you simply type in the name of the control your are referring to: i.e. Forms![YourFormName].[YourTextBoxName]. Using the Like operator will result in something like this: Like("*" & Forms![YourFormName].[YourTextBoxName] & "*")

That is actually the approach that i was going to steer you towards, using your check boxes to populate a text box with the value you wish to display on the form.

Typing the value in manually is another option that I'm guessing is what you are going to try now. It's equally as valid...

If you instead wish to use your check boxes to populate the text box, we can take a look at the simple vb code that will be needed to do it.

Good luck on your app, and go ahead and post in this thread again if you have more questions regarding the check box portion. I haven't seen your other post, but likely someone else has already started on an answer there...

Regards,
Scott