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
> Can't Figure Out Why I'm Getting The "invalid Syntax" Error, Access 2016    
 
   
Mvaldesi
post Mar 2 2018, 12:44 PM
Post#1



Posts: 118
Joined: 19-August 10



Good morning, UA!

Below is a field I’m trying to calculate in an Access query; I’m simply comparing two date fields to determine the status of “Early”, “On Time”, “Late” or “Late More than 3 Days”, but I’m receiving an error message (“The expression you entered contains invalid syntax,”) it then highlights the TRUE conditions that contain spaces. I’m pretty sure I’ve done similar fields like this dozens of times before; I can’t figure out why it’s taking issue with this one all of the sudden. Feels like I’m missing something pretty obvious; can anyone assist? Thanks in advance!

MOVING_STATUS: IIf(([ETA_ACTUAL_DT]-[ETA_DATE])<0,”Early”,IIf(([ETA_ACTUAL_DT]-[ETA_DATE])=0,”On Time”,IIf(([ETA_ACTUAL_DT]-[ETA_DATE])>0 And ([ETA_ACTUAL_DT]-[ETA_DATE])<4,”Late”,IIf(([ETA_ACTUAL_DT]-[ETA_DATE])>3,”Late More Than 3 Days”,””))))

Go to the top of the page
 
theDBguy
post Mar 2 2018, 12:48 PM
Post#2


Access Wiki and Forums Moderator
Posts: 73,924
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Just curious, does removing the spaces gets rid of the error? If not, then we know it's not the spaces. Are you copying and pasting this expression into the query grid from somewhere else? I just noticed it had smart quotes rather than the regular ones.
Go to the top of the page
 
zaxbat
post Mar 2 2018, 12:50 PM
Post#3



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Before I start trying to pair up all of the parenthesis....my first question is.... are the fields holding the dates all of type DATETIME or DATE ???? Not strings, right?
This post has been edited by zaxbat: Mar 2 2018, 12:52 PM
Go to the top of the page
 
Mvaldesi
post Mar 2 2018, 12:53 PM
Post#4



Posts: 118
Joined: 19-August 10



Thank you both for your responses!

theDBguy, when I remove the spaces, it then puts brackets around all of the TRUE conditions, then asks for them as inputs when I run the query. And yes, I did type the expression up in an Outlook email as I prefer it to trying to read the smaller font in the “Zoom” box.

Zaxbat, the fields are just dates (no time stamp.)
Go to the top of the page
 
zaxbat
post Mar 2 2018, 01:00 PM
Post#5



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


The parenthesis are paired up ok.

One thought though about the boolean AND in there.....
I always use parenthesis around both expressions in Boolean comparisons (just to be sure).

Even though SQL generally cares NOT AT ALL about spaces.....i would remove the one after the colon
Go to the top of the page
 
Mvaldesi
post Mar 2 2018, 01:16 PM
Post#6



Posts: 118
Joined: 19-August 10



Thanks again, both of you! It appears it WAS the quotes I copied out of Outlook; I retyped them within Access and it’s now working.
Go to the top of the page
 
zaxbat
post Mar 2 2018, 01:19 PM
Post#7



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Yep...you beat me to it....it was unhappy with your strings.....go figure....

Notice though..... the big green guy hit it first try
This post has been edited by zaxbat: Mar 2 2018, 01:20 PM
Go to the top of the page
 
LPurvis
post Mar 2 2018, 01:21 PM
Post#8


UtterAccess Editor
Posts: 16,295
Joined: 27-June 06
From: England (North East / South Yorks)


Just to explain why, when you use a text editor like Word (or by default then Outlook), your autocorrect will often change the standard double quote to a similar but distinct Unicode character. (Which isn't SQL compatible.)

Best to stick to a Notepad type editor if not the query UI ;-)

Cheers
Go to the top of the page
 
theDBguy
post Mar 2 2018, 01:28 PM
Post#9


Access Wiki and Forums Moderator
Posts: 73,924
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Glad to hear you got it sorted out.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    10th December 2018 - 04:53 PM