Solved

MS ACCESS text date field compared in CRITERIA as NOT this Year

Posted on 2013-11-14
5
414 Views
Last Modified: 2013-11-14
PREVIOUS QUESTION

As a follow on to the above question I need to also check that something is NOT THIS YEAR, I've tried taking the lead from the above question where criteria MONTH(DATE()) allowed for a comparison of this month against the data and looking at something like NOT YEAR(DATE()) but this didn't work.

This bit does provide the year:
SVC_YEAR: Year(Format(Mid([SVC_DATE],5,2) & "/" & Mid([SVC_DATE],7,2) & "/" & Left([SVC_DATE],4),"mm/dd/yyyy"))
0
Comment
Question by:ghettocounselor
5 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 39648643
in the SVC_YEAR column, place in the criteria row


<>Year(date())
0
 

Author Comment

by:ghettocounselor
ID: 39648653
with this I get "DATA TYPE MISMATCH IN CRITERIA EXPRESSION"

WHERE (((Year(Format(Mid([SVC_DATE],5,2) & "/" & Mid([SVC_DATE],7,2) & "/" & Left([SVC_DATE],4),"mm/dd/yyyy")))<>Year(Date())));
0
 
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 250 total points
ID: 39648662
You coudl simplify the calculated column (field) to:

SVC_YEAR: Val(Left([SVC_DATE],4)) 

Open in new window

then use the criteria

<>Year(date())

or edit the where clause to include:

Where  Val(Left([SVC_DATE],4)) <>  YEAR(DATE()) ...

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 39648665
Try  simplifying it like this (adding the xxxx to the end handles nulls):

SVC_YEAR: Left(SVC_DATE & "xxxx",4)  

And use as criteria (as cap1 suggested):  <> Year(Date())
0
 

Author Closing Comment

by:ghettocounselor
ID: 39648725
Thanks for all the help
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Some sers suddenly getting error popup msg 28 89
Access on Mouse move 5 35
Microsoft Access Write errors seem to be caused by bit fields 4 36
Switch 5 17
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question