The issue :-
As a lark I ‘wrote’ a program in MS Access to display my daily trades – brokerage and taxes, gross profit and net profit. The main points and issue are :-
tblTrdMain has 6 records of trades for 6th and 7th Jan 2020. The table has an indexed field named ‘TrdDt’.
To view reports a form opens with (radio button) options of ‘Last week’, ‘This week’, ‘Last Month’, ‘This month’ and ‘Financial Year’. On clicking ‘View Report’ command button on this form the following code is executed :-
DoCmd.OpenReport "repoTrdMain", acViewPreview, , strSQL
strSQL is to be passed the “WHERE” part of the SQL clause without the ‘WHERE’
the value of strSQL is processed when one of the abovementioned options is taken. I have captured the value of strSQL, both in the options processing and just a line before the Docmd line shown above. The values of strSQL are :-
For option ‘This week’ : [TrdDt] >= #06-01-20#
For option ‘This month : [TrdDt] >= #01-01-20# And [TrdDt] <= #31-01-20#
The curious problem is that for the option ‘This month’ the records are correctly displayed always (so too when option ‘Financial Year’ is taken) but option ‘This week’ always fails showing a blank report (or if the ‘On No Data’ event procedure is activated, then the report is cancelled as having no data). The essential part is that the value transferred to the SQL part of the DoCmd method is correct. I have even copied the [TrdDt] >= #06-01-20# part from the immediate window and pasted it in the criteria of a query based on tblTrdMain, under the indexed field TrdDt (it of course removes the [TrdDt] part), and it displays the records correctly!
Am I missing something ? Is a refresh of tblTrdMain required and if so how and where should the code be entered. I would be grateful for a solution without having to debug by stepping through code.
As a lark I ‘wrote’ a program in MS Access to display my daily trades – brokerage and taxes, gross profit and net profit. The main points and issue are :-
tblTrdMain has 6 records of trades for 6th and 7th Jan 2020. The table has an indexed field named ‘TrdDt’.
To view reports a form opens with (radio button) options of ‘Last week’, ‘This week’, ‘Last Month’, ‘This month’ and ‘Financial Year’. On clicking ‘View Report’ command button on this form the following code is executed :-
DoCmd.OpenReport "repoTrdMain", acViewPreview, , strSQL
strSQL is to be passed the “WHERE” part of the SQL clause without the ‘WHERE’
the value of strSQL is processed when one of the abovementioned options is taken. I have captured the value of strSQL, both in the options processing and just a line before the Docmd line shown above. The values of strSQL are :-
For option ‘This week’ : [TrdDt] >= #06-01-20#
For option ‘This month : [TrdDt] >= #01-01-20# And [TrdDt] <= #31-01-20#
The curious problem is that for the option ‘This month’ the records are correctly displayed always (so too when option ‘Financial Year’ is taken) but option ‘This week’ always fails showing a blank report (or if the ‘On No Data’ event procedure is activated, then the report is cancelled as having no data). The essential part is that the value transferred to the SQL part of the DoCmd method is correct. I have even copied the [TrdDt] >= #06-01-20# part from the immediate window and pasted it in the criteria of a query based on tblTrdMain, under the indexed field TrdDt (it of course removes the [TrdDt] part), and it displays the records correctly!
Am I missing something ? Is a refresh of tblTrdMain required and if so how and where should the code be entered. I would be grateful for a solution without having to debug by stepping through code.