Month question (1 Viewer)

Hello,

I have users entering records into my table. They have two different date fields: the first is DateOfFileReview, which is a Date/Time data type, using the Short Date format, and is set to default to the current day and is unchangeable in the entry form. The second is MonthForReview - right now I am testing this field as Date/Time as well, with format of m/yy, and also defaults to =Date(), but IS changeable. This field is changeable because the users may be entering files from a previous month's batch (silly imo, but it is what it is). Example: a user may enter a record today, so DateOfFileReview is 9/6/2016, but they may want to change MonthForReview to 8/16 because it is from last month's batch of files.

So, the problem is that the query acts a little strange when users try to pull a report by MonthForReview. In a previous version of the database, the MonthForReview field was simply January, February, etc. because users would get a new copy every year, hence year was unimportant. We want this version to be more permanent, so I went back in the tables and changed all Januarys to 1/16, Februarys to 2/16, etc. (all records were entered this year and I substituted through June). However, I've noticed that new records (records entered since I made those substitutions) have different values when I click in the field in table view. For instance, when I click in a 6/16 cell, it remains 6/16, but when I click in a 7/16 cell, it may say 07/15/16 or 07/27/16 - it seems to match up with the date the file was entered (default). So when a user tries to run a report pulling July 2016 records by using the date range 7/16 to 7/16, the report shows nothing. However, entering 7/1/16 and 7/31/16 pulls the appropriate records. Is there a quick fix for this or a better way overall to approach this issue?

you need to understand that dates are actually numbers with special format properties so the can be viewed as a date in the way we are used to. Today (6th Sept) is 42619, yesterday would have been 42618.

The other thing to be aware of is regardless of where you are in the world, when providing a string value access will assume a US format mm/dd/yyyy but is clever enough to know that if the day >12 then treat as a format of dd/mm/yyyy. However when day <=12 then the US format will be assumed. So 16/08/16 (uk format) will be treated as 16th August, but 12/08/16 will be treated as 8th December.

So a date is a date is a date. it is not a month/year. So if users enter 7/16, that is not enough information to create a value without making an assumption about which day of the month. I've just tested it (uk format) and it is converted to a date of 16/07/2016 - stored numerically as 42567.

Also, from your post it sounds like you are allowing users to enter data directly into a table and not via a form. This is generally considered a bad idea and you are unable to control the input other than through basic field validation.

Back to the question in hand. I'm assuming from this statement so DateOfFileReview is 9/6/2016, but they may want to change MonthForReview to 8/16 that you are UK based - dates are 9th June and Aug - if date was 6th September you wouldn't be reviewing before that date in August.

You need to check that all month for review dates are actually stored correctly. Change the format to shortdate and make a visual comparison and/or write a short query to identify records where review date precedes the fixed review date.

Once you are happy the dates are correct for your report you need to change your criteria

when a user tries to run a report pulling July 2016 records by using the date range 7/16 to 7/16

your criteria needs to be something like

format(monthforreview,"m/yy") =forms!rptform!monthrequired

Having run into similar trouble as CJ_London is describing, I have taken to always displaying dates in a manner that will never be mistaken.
In your case, I would display Aug 2016 or Aug 16.
That way it is very easy to see if you have entered something wrong or it is behaving in an odd manner.
For full dates I would use something like 01 Aug 16.

I know it may look slightly foolish however there are no more mistakes about which month I am working with.
Internally, the date is stroed however the program prefers. This all has to do with the presentation.

I mostly use international date format....

Sent from my SM-G925F using Tapatalk

CJ, thanks for the explanation of how it works. Very poorly worded on my part - I should have said "I have users entering records into my table via a form."

"Back to the question in hand. I'm assuming from this statement so DateOfFileReview is 9/6/2016, but they may want to change MonthForReview to 8/16 that you are UK based - dates are 9th June and Aug - if date was 6th September you wouldn't be reviewing before that date in August."

Actually not UK based. What I mean is, they would be entering a record on Sep 6, 2016 (DateOfFileReview) but the file is from an August batch, so they would change the default MonthForReview from 9/16 to 8/16.

So if I understand correctly, you're saying store the MonthForReview in the table in the same format as DateOfFileReview? Then if they want MonthForReview: July 2016 records, have them enter the full dates 7/1/16 and 7/31/16? Sorry, not clear on this. Thanks for the responses.

you're saying store the MonthForReview in the table in the same format as DateOfFileReview

Click to expand...

Not quite - both are date datatypes - so both are numbers. Format is something else ie how the numbers are displayed. Do you mean entered in the same format by the user? If so, yes.

I may be being a bit pedantic but computers see things in black and white

Then if they want MonthForReview: July 2016 records, have them enter the full dates 7/1/16 and 7/31/16?

Click to expand...

in the criteria

format(monthforreview,"m/yy") will convert all dates to a text string so any date in august this year will be converted to 8/16

so you don't need from/to, you just need the user to enter 8/16
=forms!rptform!monthrequired

Yes, entered in the same format by the user. By all means, be a bit "pedantic," as I'd rather learn and use the correct terminology.

Still not quite clear on...

"so you don't need from/to, you just need the user to enter 8/16
=forms!rptform!monthrequired "

Right now on the report request form, I have two text boxes where users enter their months. For instance, 7/16 and 7/16 if they want July 2016. Also allows them to pull larger time periods, i.e. year to date: 1/16 to 9/16. Then the query pulls from those text boxes, i.e.:

WHERE
((Qry_NH.MonthForReview) Between Forms!FrmReportFilter!TextMonth1 And Forms!FrmReportFilter!TextMonth2)

Can you tell me what I need to change in the current setup to use your example?

Still not quite clear on...

"so you don't need from/to, you just need the user to enter 8/16
=forms!rptform!monthrequired "

Click to expand...

your original post implied looking for one month

If you want to look at a range of month/years be aware that text sorts differently to numbers (and date is a number)

so in numbers

1
2
10

is sorted numerically. In text the order would be

1
10
2

to get month/years (text) to sort the same as a number you need to use preceding zeros and put years before month if you go over a year end i.e.

format(monthforreview,"yymm") between format(forms!rptform!monthstart,"yymm") and format(forms!rptform!monthend,"yymm")