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
> Datevalue Causing Data Type Mismatch, Access 2016    
post Sep 25 2019, 08:07 AM

Posts: 3
Joined: 17-January 18

I am running a simple query that should pull records with a date <=date(), on a field that is using a datevalue function (field contains date/time, but just need date). Everything works fine unless there are no records for the current date then I get a Data Type Mismatch error. My uneducated guess is datevalue is returning a string not an actual date, but I don't have any ideas on how to take both into consideration. Any suggestions on how I can prevent this from happening?

SELECT Engagement_Protocol_DueDates.PATID, Engagement_Protocol_DueDates.Protocol, Engagement_Protocol_DueDates.[Cell Phone], Engagement_Protocol_DueDates.Email, Engagement_Protocol_DueDates.accept_tests, Engagement_Protocol_DueDates.rec_elec_updates, Engagement_Protocol_DueDates.[Date Screened], Engagement_Protocol_DueDates.[Visit A Date], Engagement_Protocol_DueDates.InfoStatus, Engagement_Protocol_DueDates.Engage_Step, Engagement_Protocol_DueDates.Engage_Time, DateValue([Engagement_Protocol_DueDates]![DueDate]) AS Due_Date, IIf([Engagement_Protocol_DueDates]![InfoStatus]="Waiting For Info Session","No","Yes") AS Scheduled, Engagement_Protocol_DueDates.ID, Engagement_Protocol_DueDates.Title
FROM Engagement_Protocol_DueDates LEFT JOIN dbo_Text_Sent ON (Engagement_Protocol_DueDates.[Cell Phone] = dbo_Text_Sent.TO_Phone_Number) AND (Engagement_Protocol_DueDates.Engage_Step = dbo_Text_Sent.Engage_Step) AND (Engagement_Protocol_DueDates.Protocol = dbo_Text_Sent.Protocol)
WHERE (((DateValue([Engagement_Protocol_DueDates]![DueDate]))<=Date()) AND ((Left$([Engagement_Protocol_DueDates]![Engage_Step],3))="RCU") AND ((dbo_Text_Sent.TO_Phone_Number) Is Null));
Go to the top of the page
post Sep 25 2019, 08:28 AM

UA Admin
Posts: 36,528
Joined: 20-June 02
From: Newcastle, WA

DateValue returns a Variant. If the date argument passed into the function includes time information, DateValue doesn't return it.

The problem is that your SQL is being asked to convert a Null to a date value. That's the source of the error about a Mismatched Data Type. That would occur, as you say, when there are no records for the current date. Why that's the case, of course, is not so easy to see from just this SQL statement. BTW, there is a really good website, Instant SQL Formatter which formats SQL in a more readable layout. And here, if you use the [ SQL] and [/SQL ] html tags, UA respects that layout (for the most part).

SELECT engagement_protocol_duedates.patid,
engagement_protocol_duedates.[cell phone],
engagement_protocol_duedates.[date screened],
engagement_protocol_duedates.[visit a date],
Datevalue([engagement_protocol_duedates] ! [duedate]) AS Due_Date,
Iif([engagement_protocol_duedates] ! [infostatus] = "waiting for info session", "no", "yes") AS Scheduled,
FROM engagement_protocol_duedates LEFT JOIN dbo_text_sent ON ( engagement_protocol_duedates.[cell phone] = dbo_text_sent.to_phone_number ) AND ( engagement_protocol_duedates.engage_step = dbo_text_sent.engage_step ) AND ( engagement_protocol_duedates.protocol = dbo_text_sent.protocol )
WHERE ( ( ( Datevalue([engagement_protocol_duedates] ! [duedate]) ) <= DATE() ) AND ( ( Left$([engagement_protocol_duedates] ! [engage_step], 3) ) = "rcu" ) AND ( ( dbo_text_sent.to_phone_number ) IS NULL ) );

The solution will be to check for a value in that field and only try to format it if it is non-null. You may be able to use Iif() to do that.

My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    22nd January 2020 - 03:06 AM