Posts by W_Scott_Taylor

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Re: Using checkboxes to generate a list

    Assuming you are using a table(subform) in your form that contains the checkbox and label data since we are talking about 100 records the UDF would look something like the code below where field zero is the checkbox and field one is the value you are wishing to concatenate from a table called Table1.

    If you have individual checkbox controls on your form then you would need to spin through the checkbox controls and concatenate them in a similar fashion.

    Re: Average The Times Properly

    I would convert everything to hours. The result you described 02:03:17 could be converted by a formula like =(LEFT(A1,2)*24)+MID(A1,4,2)+(RIGHT(A1,2)/60) where cell A1 contains the text 02:03:17. Then average all 4,000 formula results.

    Re: Getting an Excel Output in Access

    It is hard to comprehend what you are describing. It sounds like you may want to link into Access the raw data you used for the VLookups in creating the "form" and write a query to return the data you are interested in (the form data). You could then use a 'Make Table' query to produce a version of the records that are editable.

    Re: Opening Excel Files from Access

    How about this...

    Re: Query by form

    Yes it is possible, but I don't know without play around with it how to structure the filter string. Maybe someone else can help on this one. I think I would start experimenting with containing the first fields criteria in parenthesis and using an AND and then the second fields criteria within parenthesis. That might work. :confused:

    Re: Create Form/subform combining list of items that matches one record

    What about something like this... (See Attached Sample)

    I used a lookup instead of your option boxes and by clicking into the '[AUDIT RESULTS]' field on the form you get a list of 'Correct', 'Incorrect', and 'N/A' to select. I also created a small table for '[Case Manager]' names so that they can be assigned quickly. You can use the combo box beside of the policy number to type in or look up your policy number and jump to that record on the form. I also created a couple of queries that will add the 9 default checklist detail records to a new policy header record. I don't usually go this far in helping folks, I usually just try to point them in the right direction to help them learn. Hope this helps...

    Re: Query by form

    Okay, now I understand what you were trying to do. I had to tweak the code a little to get it to work in my example. Check out the attached sample.

    Re: Query by form

    If you have the combo/list box on the form, and that piece sounds like it is working to me, you can add more combo/list boxes for the different fields you need to query. You should be able to drag a subform object from your toolbar onto the form and follow the wizard to establish the parent/child relationships. The subform will filter based on selections made in the parent form automatically if you set it up correctly. Is this what you are asking?

    Re: Modifiy Define name range formula


    Not to intentionally hijack this thread, but, can one use a technique like that with a bubble chart quadrant analysis graph? I have to automate a template where the user can paste data into a sheet and then I will give them the option of picking different columns to switch between X, Y and Bubble size depending on the number of columns they paste in. If I can use an offset technique I will not have to code adding each series for the rows of data pasted in. I can then move on to the rest of the functionality.


    Re: Modifiy Define name range formula

    I have never seen it used the way you are using it that's why I asked, I was thinking


    =OFFSET(ReferenceCell (Where From),Go Down this many,Go over this many, Return this many rows, Return this many columns)

    Also your counta functions are returning numbers larger that one would expect when visually examining the worksheet. There is garbage in some of the 'apparently blank' cells the functions are counting.