Checkboxes 2

[SirLaugha1ot]

I have several checkboxes that I use in my form. When I run a report, the checkboxes are visible yet when I try and cross the info over into Excel, they disappear! The only information that crosses over is anything that is a text or number field. How do I get the checkboxes to show in Excel...would I need to change the checkboxes? Help?

 

[jgoodman00]

I just tried this, & the data went through, but the checkboxes are formatted into bool fields, whereby they state whether they are true or false. I think that check boxes in excel are inserted as objects (& lots of them slows it down (& increases file size) terribly).

Therefore I don't think you can actually export bool fields as checkboxes. You might be able to create a macro in excel which converts the bool value into a checkbox result though. However, if possible I think it will take a very long time to execute.... James Goodman
j.goodman00@btinternet.com

 

[SirLaugha1ot]

Is there something else I could use in an Access form besides checkboxes that would carry over into Excel?

 

[BoxHead]

Sir,

You could use a query of your table. On the QBE, select all of the fields and drag them into the grid.

In the field name cells, use expressions to change your checkbox fields to display text that you want.

IIf([YourField] = "-1", "True", "False")

Play around with it until you like the results and export the query to Excel instead of the table.

Just a thought.

HTH

John

 

[djmousie]

I kind of have the same problem, i have check boxes on my form that i want to display an alpha-numeric if the box is check . For example if if a check box is checked its value is -1, i want -1 to have the value of "A", how would i go about that as an expression?

 

[BoxHead]

djmousie,

IIf([YourField] = "-1", "A", "")

The IIF statement is similar to the If function in Excel. You have three parts inside the parentheses divided by the commas.

The first part is your criteria: "YourField = "-1"".

After the first comma is the string or value that is displayed if your criteria is met/true: "A".

After the second comma is the string or value that is displayed if your criteria is not met/false: "" (an empty string in this case).

You can also use the query as the source for reports and take better control of the output.

If i recall your situation correctly, you could add another column to your query that would concatenate the values of the first four: "ACD" "BD" "ABD" etc...


HTH

John

 

[djmousie]

Hmm, still can't get it to work. In one table i have 5 fields, Trip_A, Trip_B, Trip_C and Trip_D, plus the ID field. Under each trip is a check box. Its either on or off, yes or no. Now, those check boxes if ON need to stand for the value of each trip. So if ON in trip_A, then i need the value of "A" to appear in another table, and "B" and so on. How do i link another table so i can get all 4 field values in one TRIP_TYPE field in another. So the other table, under trip_type could read "A,B,D" for example? For some reason the above expression doesnt seem to work for what im doing. I do appreciate your time and hope for the reply, thanks again

kevin

 

[BoxHead]

Sir & Kevin,

I apologize. There should be no quotation marks around the -1 in the IIf statement.


Kevin, I built a table with the five fields you mentioned and then used the form wizard to build a form. I entered a variety of values for ten records with autonumber IDs 1 - 10.

Then I went to the database window, selected the queries tab; 'New'; 'Design'. I added the table, closed the add window, selected the five fields and dragged them into the grid.

On the top line of the grid where it read Trip_A, I replaced it with

 A: IIF([Trip_A] = -1, " A ", "")

I did the same thing in the other columns for B, C, and D.

Note: I used a space before and after the 'A' (et al) because using a comma as a separater would involve checking if each letter was first or last in the string.

In the next open column of the query, I typed the following in the field line:

 Trips: [A] & [B] & [C] & [D]

.

When I look at the data view, I see the strings as they should be.

I'm not sure how you have your relationships set up or how to link the string to another table, but I built another query based on this query with the two fields: Trip_ID and Trips. On the query properties, I changed the Unique records property to "Yes" to eliminate some redundancy.

I apologize if my explanations have been unclear and I hope this is helpful.

John


PS: Be sure to put the expressions in the top "Field" line of the QBE grid, not the criteria line.

 

[djmousie]

Thanks Boxhead, i got the query to give the values, but a camper can choose any combination of trips. For example, just Trip A or Trip A and Trip D. When i open up the Trip_Query, it asks me to Enter A Parameter Value for A, B,C and D. The query needs to sort those values and place them in a field, i shouldnt be typing anything in. So if under Trip_A field there is an "A" and under Trip_D field there is a "D", it needs to place just "A" and "D" into a seperate field. The way its working now you have to type in each value that is going to go in the Field. I hope this is clear, and once again appreciate your help.

Kevin

 

[djmousie]

*bump*

 

[BoxHead]

Kevin,

Do have the line

     Trips: [A] & [B] & [C] & [D].

in the top line for the sixth column or is it in the Criteria cell?

It should be in the top, 'Field' cell.

John

 

[djmousie]

Yes i do it in the top line called "FIELD". i Have it just like u put it. Its because of that line, i get a box that pops up and asks me to put a Parameter value in for A throug D. I basically choose the letters rather than it putting the letters in the order they are suppopsed to.

The query right now though is NOT linked to another query or relationship, even when i do link it, that parameter box still pops up asking me to put in values.

If u give me your email address i might be able to send you some screen shots of what im dong...thanks again...

Kevin

 

[djmousie]

ok, i know why that box was popping up, but now it lists the values all in one FIELD but as 0 and -1s ...for example under the TRIPS field it will list: 00-1-1 meaning check boxes C and D where checked off. How do i make these values alpha-numeric in respect to their trip letters?

 

[BoxHead]

Kevin,

If you can send me a print screen of the SQL Builder, I'd like to compare it to the one I did.


John
finneganjohn@hotmail.com

 

[djmousie]

this is what i did BoxHead that seemed to work:

Trips: IIf([Trip_A]=-1," A ","&quot & IIf([Trip_B]=-1," B ","&quot & IIf([Trip_C]=-1," C ","&quot & IIf([Trip_D]=-1," D ","&quot

I put that all in one field line and it produced pretty much the same results that i need but all in one field....now hopefully i can link the query to the camper_id_table and it will work.thanks for your patience with me .

kevin

 

[BoxHead]

Kevin,

I guess we were posting at the same time. Glad to hear it's working.

John

 

[djmousie]

i have also sent you an email with some screen shots of my goals..thanks