Cannot see data after year change

[Ericsp]

I have a small database of 160 entries that has worked very well for the last 12 years. As we moved into 2022 I cannot see the data in Forms/Edit view.
Changing the system date back to 2021 and everything works as normal.


Has anyone had the same experience, got an answer?
Thanks
Eric

[NoellaG]

What is the recordsource of the form? It might contain a date/time criterion based on the current date.

[Ericsp]

Firstly I have very little knowledge of Access etc.
The Form has 3 date references (Date Joined, Date Lapsed & Date Paid), none relate to current date.
All seem to be formated the same, none have changed since last year.

[Welshgasman]

Upload your DB.
If it contains pertinent/private information, remove/amend it, but leave enough so we can see the problem.

[Micron]

There could be an expression anywhere - in a calculated control on a form or in code, for example. Suggest you copy the db and play with the forms in design view of the db copy. Look for a date type of control that (perhaps) has some sort of expression like = something. If not found, you can open the vb editor (ctrl+G) and look at code, but that code may not mean anything to you. Or you could compact/repair that copy, zip it and post it here. For instructions on that see "how to attach files" at the top of the forum window. If data is sensitive, you could probably remove most of it and leave only a few records with pseudo data for this problem. Just be sure that with whatever you have remaining, the problem still exists before posting.

[Ericsp]

Here is (I hope) a copy of the DB

[Welshgasman]

Cannot see any data? except for a Spencer, 1 record?
What is a Seried supposed to do?

[mike60smart]

Hi

Your Form for "Edit/View Current members is based on this query:-

SELECT Members.LastName, Members.PaymentMethodID, DateDiff("y",[DateJoined],[expr1c]) AS DaysElapsed, DrawYearConversion.DrawNumber, Members.FirstName, Members.MemberTypeID, EffectiveYearQuery.EffectiveYear, MemberTypes.MemberDues, MemberTypes.MemberDuesOld, Year(Now()) AS Expr1, [Expr1]+1 AS Expr1a, [Expr1]-1 AS Expr1b, DateValue("1/4/" & [Expr1]) AS Expr1c, 1 AS Expr1d, DatePart("yyyy",[DateJoined]) AS Expr2, DatePart("q",[DateJoined]) AS Expr3, IIf((([Expr1]-[Expr2])=1),(IIf([Expr3]>1,"New","Old")),"Very Old") AS Expr4, IIf(([Expr2]-[Expr1]=0),"Very New","Very Old") AS Expr5, IIf([Expr5]="Very New","Very New",IIf([Expr4]="New","New","Old")) AS Expr6, IIf([EffectiveYear]=[Expr1],([MemberTypes].[MemberDues]-([MemberTypes].[MemberDuesOld]*(1-[Expr7]))-[Credit]),[MemberTypes].[MemberDues]*[Expr7]-[Credit]) AS Expr8, IIf([EffectiveYear]=[Expr1],([MemberTypes].[MemberDues]-([MemberTypes].[MemberDuesOld]*(1-[Expr7a]))-[Credit]),[MemberTypes].[MemberDues]*[Expr7a]-[Credit]) AS Expr8a, IIf([DaysElapsed]<365,([DaysElapsed]/365),1) AS Expr7, IIf([Expr6]="New",1-((DatePart("m",[DateJoined])-3)/12),IIf([Expr6]="Old",1,0)) AS Expr7a, IIf([Expr8]<0,0-[Expr8],0) AS Expr9, Members.Credit, IIf([MembershipNumber]=0,[Members.MemberID]+444,[MembershipNumber]) AS MemNum, *
FROM MemberTypes RIGHT JOIN (DrawYearConversion INNER JOIN ((((((Series RIGHT JOIN (Members INNER JOIN MobileNetworks ON Members.MobileNetwork = MobileNetworks.ID) ON Series.ID = Members.Series1) LEFT JOIN Series AS Series_1 ON Members.Series2 = Series_1.ID) LEFT JOIN Series AS Series_2 ON Members.Series3 = Series_2.ID) LEFT JOIN Series AS Series_3 ON Members.Series4 = Series_3.ID) INNER JOIN EffectiveYearQuery ON Members.MemberID = EffectiveYearQuery.MemberID) INNER JOIN [MemberDraw (Query)1] ON Members.MemberID = [MemberDraw (Query)1].MemberID) ON DrawYearConversion.DrawYears = [MemberDraw (Query)1].MemberDraw) ON MemberTypes.MemberTypeID = Members.MemberTypeID
WHERE (((Members.MemberTypeID)<>7 And (Members.MemberTypeID)<>6 And (Members.MemberTypeID)<>8))
ORDER BY Members.LastName, Members.FirstName;

It produces No records

[mike60smart]

Hi

I just took a look at your relationships and you have not set any relationships between tables with Referential Integrity set.

This means you would be able to add Child Records to related tables where there is noi Parent record in the Main table?

In your Members table you have the following issues:-
Lookup fields in tables - these are a No-No
Characters in field names
Repeating Group - This repeating group relates to the fields AircraftReg1, AircraftReg2,AircraftReg3 & AircraftReg4
These repeating groups should be records in a related table and NOT fields in the Members table.

[Micron]

@Ericsp - a couple of pointers to help those who would help you
- when you trim things and objects out of a db and post it, make sure that the problem can be replicated with what's left
- provide at least some direction/instruction on what form/report/etc. should be opened and how to replicate the problem.

It's possible that the reason things don't work is simply that the only record left has member type 13 and at least one member query has criteria for type 1 OR 2, so that's at least one issue.

[Welshgasman]

Just change the surnames to DataProtected and upload that.
We should be able to get by with first names and the like.

[Welshgasman]

Hi

I just took a look at your relationships and you have not set any relationships between tables with Referential Integrity set.

This means you would be able to add Child Records to related tables where there is noi Parent record in the Main table?

In your Members table you have the following issues:-
Lookup fields in tables - these are a No-No
Characters in field names
Repeating Group - This repeating group relates to the fields AircraftReg1, AircraftReg2,AircraftReg3 & AircraftReg4
These repeating groups should be records in a related table and NOT fields in the Members table.

Mike,
Let's get the DB working first. It has been working fine since 2004, so TBH I see little point in spending time amending it now?

[mike60smart]

Hi Eric

The Enter /Edit Members form now works as it is based on the Members table

[Welshgasman]

Here is the query for the form in amore readable format?
Perhaps you can see what is causing it?

SELECT members.lastname,
       members.paymentmethodid,
       Datediff("y", [datejoined], [expr1c])
       AS DaysElapsed,
       drawyearconversion.drawnumber,
       members.firstname,
       members.membertypeid,
       effectiveyearquery.effectiveyear,
       membertypes.memberdues,
       membertypes.memberduesold,
       Year(Now())
       AS Expr1,
       [expr1] + 1
       AS Expr1a,
       [expr1] - 1
       AS Expr1b,
       Datevalue("1/4/" & [expr1])
       AS Expr1c,
       1
       AS Expr1d,
       Datepart("yyyy", [datejoined])
       AS Expr2,
       Datepart("q", [datejoined])
       AS Expr3,
       Iif(( ( [expr1] - [expr2] ) = 1 ), ( Iif([expr3] > 1, "new", "old") ),
       "very old")
       AS Expr4,
       Iif(( [expr2] - [expr1] = 0 ), "very new", "very old")
       AS Expr5,
       Iif([expr5] = "very new", "very new", Iif([expr4] = "new", "new", "old"))
       AS
       Expr6,
       Iif([effectiveyear] = [expr1], (
       [membertypes].[memberdues] - (
       [membertypes].[memberduesold] * ( 1 - [expr7] ) ) - [credit] ),
       [membertypes].[memberdues] * [expr7] - [credit])
       AS Expr8,
       Iif([effectiveyear] = [expr1], ( [membertypes].[memberdues] -
       (
                                      [membertypes].[memberduesold] * ( 1
                                      - [expr7a] ) ) - [credit] ),
       [membertypes].[memberdues] * [expr7a] - [credit]) AS Expr8a,
       Iif([dayselapsed] < 365, ( [dayselapsed] / 365 ), 1)
       AS Expr7,
       Iif([expr6] = "new", 1 - ( ( Datepart("m", [datejoined]) - 3 ) / 12 ),
       Iif(
       [expr6] = "old", 1, 0))
       AS Expr7a,
       Iif([expr8] < 0, 0 - [expr8], 0)
       AS Expr9,
       members.credit,
       Iif([membershipnumber] = 0, [members.memberid] + 444, [membershipnumber])
       AS
       MemNum,
       *
FROM   drawyearconversion
       INNER JOIN ((((((series
                        RIGHT JOIN ((membertypes
                                     RIGHT JOIN members
                                             ON membertypes.membertypeid =
                                                members.membertypeid)
                                    INNER JOIN mobilenetworks
                                            ON members.mobilenetwork =
                                               mobilenetworks.id)
                                ON series.id = members.series1)
                       LEFT JOIN series AS Series_1
                              ON members.series2 = Series_1.id)
                      LEFT JOIN series AS Series_2
                             ON members.series3 = Series_2.id)
                     LEFT JOIN series AS Series_3
                            ON members.series4 = Series_3.id)
                    INNER JOIN effectiveyearquery
                            ON members.memberid = effectiveyearquery.memberid)
                   INNER JOIN [memberdraw (query)1]
                           ON members.memberid = [memberdraw (query)1].memberid)
               ON drawyearconversion.drawyears =
                  [memberdraw (query)1].memberdraw
WHERE  (( ( members.membertypeid ) <> 7
          AND ( members.membertypeid ) <> 6
          AND ( members.membertypeid ) <> 8 ))
ORDER  BY members.lastname,
          members.firstname;

[moke123]

If you need to randomize your data, such as names, addresses, email addresses, etc., You can import the one form in the attached database into a copy of your database.
Open the form and select the table and fields in the lists that you need to randomize. Do not choose primary or foreign key fields.
This will scramble the letters of the selected fields.

DO THIS IN A COPY OF YOUR DATABASE!