UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Data Type Mismatch In Criteria, Access 2016    
 
   
mike60smart
post Nov 7 2019, 07:46 AM
Post#1


UtterAccess VIP
Posts: 13,470
Joined: 6-June 05
From: Dunbar,Scotland


Hi Everyone

I have the following expression in a query to obtain the Renewal Dates which works as expected:-

RDate: DateAdd("yyyy",[jtblPositionTraining]![RenewalPeriod],[jtblPositionTraining]![DateOfCourse])

However, When I add the criteria "Is Null" I get the error "Data type mismatch in criteria expression" ??

Any help appreciated

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
Larry Larsen
post Nov 7 2019, 07:57 AM
Post#2


UA Editor + Utterly Certified
Posts: 24,381
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi Mike

Not aware that DateADD(..) has the option of including criteria..

Where in that statement are you trying it..??

DateAdd ( interval, number, date )

Either the number or date parameters..

Larry
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
BruceM
post Nov 7 2019, 08:08 AM
Post#3


UtterAccess VIP
Posts: 7,985
Joined: 24-May 10
From: Downeast Maine


Can you apply the criterium to the field [DateOfCourse] rather than to the expression?
Go to the top of the page
 
mike60smart
post Nov 7 2019, 08:27 AM
Post#4


UtterAccess VIP
Posts: 13,470
Joined: 6-June 05
From: Dunbar,Scotland


Hi Larry

I am trying it in the criteria row of the query

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
mike60smart
post Nov 7 2019, 08:34 AM
Post#5


UtterAccess VIP
Posts: 13,470
Joined: 6-June 05
From: Dunbar,Scotland


Hi Larry

Can the following SQL View of the query be modified to only show records which do not contain an RDate ???

CODE
SELECT tblEmployees.EmployeeID, [LastName] & ", " & [Firstname] AS [Employee Name], tbluPositions.Position, tblTraining.TrainingNr, jtblPositionTraining.DateOfCourse, jtblPositionTraining.PassFail, jtblPositionTraining.RenewalPeriod, DateAdd("yyyy",[jtblPositionTraining]![RenewalPeriod],[jtblPositionTraining]![DateOfCourse]) AS RDate, tblTraining.TrainingDetails
FROM ((tblTraining RIGHT JOIN jtblPositionTraining ON tblTraining.TrainingID = jtblPositionTraining.TrainingID) RIGHT JOIN tblEmployees ON jtblPositionTraining.EmployeeID = tblEmployees.EmployeeID) LEFT JOIN tbluPositions ON jtblPositionTraining.PositionID = tbluPositions.PositionID;

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
Larry Larsen
post Nov 7 2019, 08:42 AM
Post#6


UA Editor + Utterly Certified
Posts: 24,381
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi Mike

Try:

CODE
SELECT tblEmployees.EmployeeID, [LastName] & ", " & [Firstname] AS [Employee Name], tbluPositions.Position, tblTraining.TrainingNr, IsDate([DateofCourse]) AS x, jtblPositionTraining.PassFail, jtblPositionTraining.RenewalPeriod, DateAdd("yyyy",[jtblPositionTraining]![RenewalPeriod],[jtblPositionTraining]![DateOfCourse]) AS RDate, tblTraining.TrainingDetails
FROM tblTraining RIGHT JOIN ((jtblPositionTraining RIGHT JOIN tblEmployees ON jtblPositionTraining.EmployeeID = tblEmployees.EmployeeID) LEFT JOIN tbluPositions ON jtblPositionTraining.PositionID = tbluPositions.PositionID) ON tblTraining.TrainingID = jtblPositionTraining.TrainingID
WHERE (((IsDate([DateofCourse]))=False));


Using IsDate([DateofCourse]) to return True / False

Criteria:
WHERE (((IsDate([DateofCourse]))=False));

HTH's
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
mike60smart
post Nov 7 2019, 08:56 AM
Post#7


UtterAccess VIP
Posts: 13,470
Joined: 6-June 05
From: Dunbar,Scotland


Hi Larry & Bruce

How stupid am I. I was so focused on the RDate field

Bruce's suggestion works just great.

Many thanks
cheers.gif


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
BruceM
post Nov 7 2019, 10:18 AM
Post#8


UtterAccess VIP
Posts: 7,985
Joined: 24-May 10
From: Downeast Maine


It was temporary myopia, that's all, Mike. Happens to all of us sometimes. Glad you got it sorted out. Good luck with the project.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th November 2019 - 07:47 PM