Adding a Date (1 Viewer)

Aimn_4U

Registered User.
Local time
Tomorrow, 07:55
Joined
May 14, 2019
Messages
33
Hi All,

I have a form that has a search function and I would like to ensure the search does not show any results where the ‘assessed date’ is over 2 years old.

I thought I put in the correct code, as it took of the results that were over 2 years old, however I have noticed now that anything new I have added is no longer showing up in the search either.

I used the code: >DateAdd("yyyy",-2,Date())
In the ‘Date assessed’ section of the Query.

When I remove this code from the query it brings up my new entries, but also brings up entries that are older than 2 years.

Please see screen shots attached of the query and the search results WITH and WITHOUT the date code.

Could someone please assist me with the correct code for this.

Thank you in advance.
 

Attachments

  • Query WITH date.jpg
    Query WITH date.jpg
    94.7 KB · Views: 67
  • Results WITH date.JPG
    Results WITH date.JPG
    59.3 KB · Views: 63
  • Query WITHOUT date.jpg
    Query WITHOUT date.jpg
    90.6 KB · Views: 61
  • Results WITHOUT date (2).JPG
    Results WITHOUT date (2).JPG
    69.1 KB · Views: 65

theDBguy

I’m here to help
Staff member
Local time
Today, 16:55
Joined
Oct 29, 2018
Messages
21,358
Hi. To make sure it's just a matter of the date criteria, try taking out all the other criteria and only leave the date criteria in. Then, see if the new entries show up. If they don't, then double check the dates used in those entries. If they do show up, then double check if those other criteria were excluding them out.
 

namliam

The Mailman - AWF VIP
Local time
Tomorrow, 00:55
Joined
Aug 11, 2003
Messages
11,696
Probable cause is that the Date assesed for the new records is empty (aka NULL)
Any comparison with a NULL value will fail always other than actually checking and trapping the NULL value

Try creating a field: IIF(IsNull([Date assessed]); date; [date assessed])

Feel free to ignore below statements, but I hope they can help you.

FYI:
1) I wont go into the troubles you will eventually run into using space (and likely any special characters) in your database.

2) Using codes as primary keys in your database is in general a less optimal solution, while codes are candidates for primary keys... they tend to mean something to us humans and as a result though everyone will deny it will at some point change.
Values changing is a bad thing for primary keys

3) consider the split between an internal and external unit.... since you are inner joining both you require both to be in tblUnitEquivelance. I dont know your design but it seems unlikely for any unit to be both internal and external

4) consider the Like constructs on all of these lines, considering NULL values
Also you have likely somehting like "Like * & form & *"
Which kindoff limits the use of like since you cant search for "Like * & Form" or Like Form *"
Building your query in VBA to suite your needs is much more robust though more time consuming.
 

Users who are viewing this thread

Top Bottom