Access Report issue (1 Viewer)

gaia

New member
Local time
Today, 11:56
Joined
Jan 10, 2020
Messages
7
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.
 

June7

AWF VIP
Local time
Today, 10:56
Joined
Mar 9, 2014
Messages
5,423
You are using international dates. Access expects mm-dd-yyyy. Access sees 6-1-20 as June 1 2020. It see 31-1-20 as January 31 2020 because it knows there is no month 31.

Review http://allenbrowne.com/ser-36.html
 

gaia

New member
Local time
Today, 11:56
Joined
Jan 10, 2020
Messages
7
Thanks a ton. Although it is still not rectified - I have changed my settings to short date dd-MM-YYYY and also after restarting Access set the date field in the table and report to Short date - I am sure this is the problem. I'll explore till I get it right - maybe restart the laptop. I thought I had the date formats clear, but you went straight to the point hence my confidence.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:56
Joined
May 7, 2009
Messages
19,169
the better approach on dealing with Date datatype is to use DateSerial function when making comparison:

[TrdDt] >= DateSerial(2020, 01, 06)
 

isladogs

MVP / VIP
Local time
Today, 18:56
Joined
Jan 14, 2017
Messages
18,186
Access stores dates as double number datatype no matter whether they are displayed as dd/mm/yyyy, mm/dd/yyyy or any other international format.

You shouldn't change your date settings to the US format as your users will expect to enter and view dates in their local date format.

Queries will work fine using dd/mm/yyyy.
However, as already explained, vba requires the use of mm/dd/yyyy (or yyyy-mm-dd). So you need to handle the dates for comparisons using one of the methods already covered such as DateSerial, Format(Date, "mm/dd/yyyy") or a combination of Year, Month and Day functions.
 

gaia

New member
Local time
Today, 11:56
Joined
Jan 10, 2020
Messages
7
Thanks. Does this, as already programmed, have the same effect :


mThisWkSt = DateSerial(Year(mThisWkSt), Month(mThisWkSt), Day(mThisWkSt))
similarly for mThisWkEnd; also for This Month and other options.
Followed with : strSQL = strSQL & "[TrdDt] >= #" & mThisWkSt & "#"


All of the above in the code processing the options.


This is the value that is passed the DoCmd method.
 

gaia

New member
Local time
Today, 11:56
Joined
Jan 10, 2020
Messages
7
Further, I typed in the following in the immediate window :
mst=#05-01-20#
? DateSerial(Year(mst), Month(mst), Day(mst))
01-05-2020

Clearly if the short date format is set to dd-MM-YYYY then this will look for date in May which is not what was wanted.

Can I use the Format function with DateSerial to get the correct date ?


I cannot thank you and others who replied enough, for taking pains to go through my post.
 

isladogs

MVP / VIP
Local time
Today, 18:56
Joined
Jan 14, 2017
Messages
18,186
You need to ensure dates are in mm/dd/yyyy format before doing any date calculation. So for example, the number of days between 5 Jan and 13 Jan

Code:
?DateDiff("d",#05/01/2020#,#13/01/2020#)
 109 

?DateDiff("d",#01/05/2020#,#01/13/2020#)
 8

The first result is obviously wrong. The second result gives the correct answer
 

gaia

New member
Local time
Today, 11:56
Joined
Jan 10, 2020
Messages
7
Thanks Isladogs (is that right ?). Spot on. I changed the format of mThisWkSt to mm-dd-yyyy and it worked like magic. Saved me a roundabout solution of having custom queries for each option report. Cheers!
 

isladogs

MVP / VIP
Local time
Today, 18:56
Joined
Jan 14, 2017
Messages
18,186
Yup. Isla is the name of my dog in the picture.
Nothing to do with the Isle of Dogs in London

Anyway, glad to have helped.
Correctly handling dates is one of the trickiest things that us non-US residents have to deal with on a regular basis

So if I have two date fields StartDate and EndDate I might use code like

DateDiff("d", Format(StartDate, "mm/dd/yyyy"), Format(EndDate, "mm/dd/yyyy"))

Do bear in mind using Format converts dates to a text string
At times it may be necessary to revert to a date which you can do with code like CDate(Format(DateField, "mm/dd/yyyy"))
Or use one of the other date functions such as DatePart or DateSerial instead of formatting.
 
Last edited:

Users who are viewing this thread

Top Bottom