Report (SQL) Query

Hi,
Can you upload your project - the pdf only shows part of the query?
Derek.

That is normal, you cannot have a “FROM” within a “WHERE” clause without having a select in parenthesis, you need to use UNION to pull the information from other tables plus, you are calling a column which does not exist if we refer to the table and column names you describe: awards in the where clause.... we could better help you if you attached your project with a description of what result you are looking for. There are so many different results you can pull from a simple query depending on how you write it!

Hi,
I'm not sure why you can't use the 'report' function rather than 'reportsql'.
Anyway, please see the attached (report function for all rows, report function for highlighted row and reportsql function).
Hope this helps,
Derek.

If all the rows of data display correctly in a tablegrid, then I'm not sure why the 'report' function doesn't work correctly.
If your project is too large, is it possible to take a copy and chop out the unnecessary bits to leave just the part where you can see the reporting problem and then upload that.  Maybe then someone might spot something.
Derek.

Thanks Derek for the example. Does any know how to modify the search my comboBox? I found an example in the forum but I cant seem to get the data to pull through to the report.

SELECT                                   
  payments.date,
  payments.payment,
  contacts.bowler_name_ID,
  payments.membership,
  payments.secratary_fees,
  bowling_weeks.week_num
FROM
  payments
LEFT OUTER JOIN
  contacts on contacts.id = payments.id_contacts
LEFT OUTER JOIN
  bowling_weeks on bowling_weeks.id = payments.id_bowling_weeks
 
WHERE ("bowler_name_ID" LIKE '%{Edit_Search_Value}%')
        AND (  "contacts on contacts.id = payments.id_contacts" = '{ComboBox_Seacrh_ComboBox1}')
               
        ORDER BY "bowler_name_ID" ASC

Hi,
In the sqlreport code that you've pasted in your search, it refers to '{combobox_SEACRH_combobox1'} - could it be something as simple as a typo (seacrh instead of search?).
Failing that, can you upload your project otherwise it's a bit like shooting in the dark (or in your case, bowling in the dark - LOL!)?
Derek.

Hi,
Please remove frmpayments.button1 (save record) - it isn't needed and is clashing with frmpyaments.button3.. 
When you click on the report button (frmpayments.button3), it first checks that you have got the most up to date information.  Part of that check is if there is a 'save' button on the form -  and if there is,  it will action it first before running the report. 
But in this instance, what the 'save' button ends up doing is adding your search criteria as a new record - this will either be a blank record (because you run the report with no search criteria) or a record with whatever search criteria you entered. 
The report then runs using the record that has just been saved as its criteria, which is why you weren't seeing the results you were expecting.
If you do this, when you exit the report, you'll see the newly created blank record (or record with your selection criteria) has been added to frmpayments.tablegrid1.
So, delete frmpayments.button1 and you should be okay.  Your report and your relationships were never the problem.
One other thing to note is that you have duplicate rows of data in frmpayments.tablegrid1 (eg, week2 / 08.08.2020 / erik / team3 / 15.00). 
If you run the report with no search criteria, it displays 14 rows of data (ie without duplicates) whereas there are 17 rows of data in frmpayments.tablegrid1. 
This may be just because it's test data and would never happen.  But to safeguard against it, you could add the payments.id to the fields to be passed through to the report to ensure uniqueness (you don't need to actually display payments.id on the report itself).
I hope that's clarified things and not confused them!
I've also added your 'registration key' code back into the script so that it now compiles. A couple of things needed changing:
Line 81:  you had 16 keys defined (0-15) but only 15 defined in the array
Line 94:  you are using a longer key than in EHWagner's example (Hi EHW) so your key length should be 1,29 instead of 1,19)
Line 98:  the duration in days is held in position 30,3 instead of 20,3)..
Hope this moves you forward.
Derek.