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: 72,442
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.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
zaxbat
post Mar 2 2018, 12:50 PM
Post#3



Posts: 952
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

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
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: 952
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

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
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: 952
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

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
LPurvis
post Mar 2 2018, 01:21 PM
Post#8


UtterAccess Editor
Posts: 16,271
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: 72,442
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Glad to hear you got it sorted out.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd June 2018 - 08:23 PM