Dear mb60,
Glad to see you're still appreciating my old solution :-)
Nic;o)
nico5038
Last Activity: Sep 20 '21, 10:11 PM
Joined: Nov 15 '06
Location: The Netherlands
-
-
Hi Ade,
Indeed a very long time :-)
Got triggered by an email from this old thread and took me a while to find out that there has been added a generated report to do the same as my function with preparated report.
I've been retired two years ago and spend the majority of my time now on the "VeleHanden .nl" platform (something like TheCrowd). Especially on projects that are transcripting and tagging handwritten...Leave a comment:
-
Thank you for your response.
I did learn the alternative dynamic report listed here, as I created and used only the "fixed" col1, etc. approach myself.
Just one final warning, MS Access does have limits. Microsoft states a max of 250 fields in a table, but having long fieldnames I've seen tables with some 90 fields crash. So just for having a growing number of fields from the crosstable query, you're application will eventually...Last edited by NeoPa; Feb 13 '21, 04:12 PM. Reason: Attempted to trigger subscription of new thread for you.Leave a comment:
-
Have tried the autoreport. It's a replacement for the code with lblCol1, etc. and delivers a report with the columns based on the column names of the query "qryDummy".
Why didn't you create a crosstable query in the editor and named it "qryDummy" to pass in the function call as described ?Leave a comment:
-
The create autoreport function is new for me. Guess that's causing the confusion.
Guess the best is to:
1: Create the autoreport
2: Change the autoreport to hold the fieldnames as specified in my previous comment.
3: Add the code for renaming the fields dynamically.
As I guess that the auto report uses the fields of the qrDummy to generate the fields, this should hold the needed number of fields.Leave a comment:
-
The "lblCol1", "lblCol2", "lblCol3", etc. need to be placed in the detail section of the report. The code in the On_Open event of the report, thus when you activate the report it will run.Leave a comment:
-
Check the description:
To start, doing this you need to place the fields "coded" in the report.
The column headings should be called "lblCol1", "lblCol2", "lblCol3", etc.
The "detail" fields should be called "Col1", "Col2", "Col3", etc.
So for 17 columns you'll need these coded fields/controls from e.g. lblCol1 till lblCol17, etc.
...Leave a comment:
-
Two possible problems I can think of:
1: the number of columns and fields on the report aren't enough (guess you need to have 17 fields
2: the fieldnames "RT Plate" and "RT Pipe" contain spaces, please use "RT_Plate" and "RT_Pipe" to avoid "space-trouble".
Nic;o)Leave a comment:
-
I would use a UNION query with:
Next I would create a Group By query on this UNION to get the MAX() of the Price1 and Price2 field per part.Code:Select part, price as price1, 0 as price2 from tblOne UNION select part, 0 as price1, price as Price2
This will give all info and allows you to exclude the rows with Price1 = Price2, thus having one table with the culprit.
Getting the idea?
Nic;...Leave a comment:
-
I usually try to prevent prompting for report parameters, as people use to make typo's, causing an eroneous report or an empty report.
My approach is to have the parameters on the form and test in my form code or it's resulting in a filled report. When that's the case I activate the [Print] button and show the report in preview mode, still allowing the user to cancel the printing when (s)he has second toughts about the selection.
...Leave a comment:
-
The only way I see to solve this is to code the value's (captions) in VBA based on data in a table. When the number of options is fixed, it's not too dfficult, however when the number can increase, then the coding will have to reshape the optiongroep and number of options...
Getting the idea ?
Nic;0)Leave a comment:
-
Did yu try the UNION solution?
Nic;o)Leave a comment:
-
place tblstudent and tblstudentlang in the query editor (you can start a new view to get this editor) and drag and drop studentid from one table in the other. That's all.
Nic;o)Leave a comment:
-
Use the function in your query like:
select clean(fldXL) from tblLOG
Nic;o)Leave a comment:
-
Sounds like your subform has the "add newrecord" property set.
ust check your data tab settings in the property window of the subform...
Nic;o)Leave a comment:
-
Just check the individual datefield to match 2016. A NULL value will return 0 (zero), that won't match 2016. Better to use NZ(year(datefie ld),2016) to force a TRUE value.
Nic;o)Leave a comment:
-
Just study the OpenReport() function. Part of the syntax is a parameter field that can be used to refer to the "me.<fieldname> " of the form to filter the report.
Nic;o)Leave a comment:
-
Just like you wrote:
Year(datefield)-1
Nic;o)Leave a comment:
-
The clients account balance is sum of invoices minus sum of payments. As these will be in separate tables, you'll need to combine them with a UNION, by making one field negative to let the sum get the outstanding amount or using a dummy column for "the opposite value" to get two fields you can subtract like:
...Code:select custid, invoice, 0 as payment from tblInvoice union select custid, 0 as invoice, payment from tblPaymen
Leave a comment:
-
What does Roll stands for, enrollment for a course ?
The there needs to be an enrollment table, as a student can apply for multiple courses.
This table will need an enrollment id and the student id also the properties of the enrollment will have to be added, like startdate, number of hrs/days/weeks, etc..
Nic;o)Leave a comment:
No activity results to display
Leave a comment: