Syntax error!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that says sntax error(comma) in query expression Expr1:
IIf(Not IsNull([datecompleted]) And
(weekends([date],[datecompleted])<=2,"Early") Or
(weekends([date],[datecompleted])>2,"Late"),True,"Y")

This is how my query looks like...

Expr1: IIf(Not IsNull([datecompleted]) And
(weekends([date],[datecompleted])<=2,"Early") Or
(weekends([date],[datecompleted])>2,"Late"),True,"Y")


My weekends is a function that looks like this:
Public Function Weekends(RqstDate As Date, DateCompleted As Date) As Integer
Weekends = _
DateDiff("d", RqstDate, DateCompleted) _
- DateDiff("ww", RqstDate, DateCompleted, 7) _
- DateDiff("ww", RqstDate, DateCompleted, 1)

End Function
 
Let's break this down into the 3 arguments (Expression, True part, False
part) accepted by IIf(). I've used curly braces to show where the 3
arguments are.

Expr1: IIf({Not IsNull([datecompleted]) And
(weekends([date],[datecompleted])<=2,"Early") Or
(weekends([date],[datecompleted])>2,"Late")},{True},{"Y"})

Now, to break down the Expression:

Not IsNull([datecompleted]) And
(weekends([date],[datecompleted])<=2,"Early") Or
(weekends([date],[datecompleted])>2,"Late")

You are checking to see that the DateCompleted is not Null AND
(weekends([date],[datecompleted])<=2,"Early"), if that part isn't True, then
the Or asks for (weekends([date],[datecompleted])>2,"Late"). To clarify,
I'll add parentheses to show the logic as currently written:

(Not IsNull([datecompleted]) And
(weekends([date],[datecompleted])<=2,"Early")) Or
(weekends([date],[datecompleted])>2,"Late")

Now, to break down the Weekends part:

WeekEnds([Date], [DateCompleted])>2, "Late"

1) You've used Date as a field name, this is a reserved word but should work
as long as you have the brackets around it. If you're trying to pass the
current date, change this to

WeekEnds(Date(), [DateCompleted])>2

VBA will drop the (), but you need it in a query or a calculated control.

2) WeekEnds([Date], [DateCompleted])>2 will return True or False, but what
is ', "Late"'?
 
if the datecompleted - date - weekends = less or equal 2 then that order is
early
if the datecompleted - date - weekends = greater than 2 then order is late...
what I posted was the column that was giving the error...

Wayne Morgan said:
Let's break this down into the 3 arguments (Expression, True part, False
part) accepted by IIf(). I've used curly braces to show where the 3
arguments are.

Expr1: IIf({Not IsNull([datecompleted]) And
(weekends([date],[datecompleted])<=2,"Early") Or
(weekends([date],[datecompleted])>2,"Late")},{True},{"Y"})

Now, to break down the Expression:

Not IsNull([datecompleted]) And
(weekends([date],[datecompleted])<=2,"Early") Or
(weekends([date],[datecompleted])>2,"Late")

You are checking to see that the DateCompleted is not Null AND
(weekends([date],[datecompleted])<=2,"Early"), if that part isn't True, then
the Or asks for (weekends([date],[datecompleted])>2,"Late"). To clarify,
I'll add parentheses to show the logic as currently written:

(Not IsNull([datecompleted]) And
(weekends([date],[datecompleted])<=2,"Early")) Or
(weekends([date],[datecompleted])>2,"Late")

Now, to break down the Weekends part:

WeekEnds([Date], [DateCompleted])>2, "Late"

1) You've used Date as a field name, this is a reserved word but should work
as long as you have the brackets around it. If you're trying to pass the
current date, change this to

WeekEnds(Date(), [DateCompleted])>2

VBA will drop the (), but you need it in a query or a calculated control.

2) WeekEnds([Date], [DateCompleted])>2 will return True or False, but what
is ', "Late"'?

--
Wayne Morgan
MS Access MVP


JOM said:
I have a query that says sntax error(comma) in query expression Expr1:
IIf(Not IsNull([datecompleted]) And
(weekends([date],[datecompleted])<=2,"Early") Or
(weekends([date],[datecompleted])>2,"Late"),True,"Y")

This is how my query looks like...

Expr1: IIf(Not IsNull([datecompleted]) And
(weekends([date],[datecompleted])<=2,"Early") Or
(weekends([date],[datecompleted])>2,"Late"),True,"Y")


My weekends is a function that looks like this:
Public Function Weekends(RqstDate As Date, DateCompleted As Date) As
Integer
Weekends = _
DateDiff("d", RqstDate, DateCompleted) _
- DateDiff("ww", RqstDate, DateCompleted, 7) _
- DateDiff("ww", RqstDate, DateCompleted, 1)

End Function
Click to expand...
Click to expand...
 
Just to add to my previous post, this was an inherited db, and Date is a
field in one of my tables

JOM said:
if the datecompleted - date - weekends = less or equal 2 then that order is
early
if the datecompleted - date - weekends = greater than 2 then order is late...
what I posted was the column that was giving the error...

Wayne Morgan said:
Let's break this down into the 3 arguments (Expression, True part, False
part) accepted by IIf(). I've used curly braces to show where the 3
arguments are.

Expr1: IIf({Not IsNull([datecompleted]) And
(weekends([date],[datecompleted])<=2,"Early") Or
(weekends([date],[datecompleted])>2,"Late")},{True},{"Y"})

Now, to break down the Expression:

Not IsNull([datecompleted]) And
(weekends([date],[datecompleted])<=2,"Early") Or
(weekends([date],[datecompleted])>2,"Late")

You are checking to see that the DateCompleted is not Null AND
(weekends([date],[datecompleted])<=2,"Early"), if that part isn't True, then
the Or asks for (weekends([date],[datecompleted])>2,"Late"). To clarify,
I'll add parentheses to show the logic as currently written:

(Not IsNull([datecompleted]) And
(weekends([date],[datecompleted])<=2,"Early")) Or
(weekends([date],[datecompleted])>2,"Late")

Now, to break down the Weekends part:

WeekEnds([Date], [DateCompleted])>2, "Late"

1) You've used Date as a field name, this is a reserved word but should work
as long as you have the brackets around it. If you're trying to pass the
current date, change this to

WeekEnds(Date(), [DateCompleted])>2

VBA will drop the (), but you need it in a query or a calculated control.

2) WeekEnds([Date], [DateCompleted])>2 will return True or False, but what
is ', "Late"'?

--
Wayne Morgan
MS Access MVP


JOM said:
I have a query that says sntax error(comma) in query expression Expr1:
IIf(Not IsNull([datecompleted]) And
(weekends([date],[datecompleted])<=2,"Early") Or
(weekends([date],[datecompleted])>2,"Late"),True,"Y")

This is how my query looks like...

Expr1: IIf(Not IsNull([datecompleted]) And
(weekends([date],[datecompleted])<=2,"Early") Or
(weekends([date],[datecompleted])>2,"Late"),True,"Y")


My weekends is a function that looks like this:
Public Function Weekends(RqstDate As Date, DateCompleted As Date) As
Integer
Weekends = _
DateDiff("d", RqstDate, DateCompleted) _
- DateDiff("ww", RqstDate, DateCompleted, 7) _
- DateDiff("ww", RqstDate, DateCompleted, 1)

End Function
Click to expand...
Click to expand...
Click to expand...
 
I understand that if an order is done in less than or equal to 2 days it is
"early". However, there is nothing in your syntax that would do this. The
IIf statement will return True or "Y". The Weekends() functions will return
a number. There is nothing telling the statement what to do with the "Early"
or "Late". What should the True and "Y" represent? What do you expect to be
the final output from the entire IIf statement?
 
How do you think I should write my statement... My final output is as follows:
I would like to display "Early" if the order is done in less than or equal
to 2 days
"Late" if greater than 2 days
"Y" if it does not fall in any of those days...
 
Ok, now I think we can figure this out. By your description, "Y" would only
be there if the order wasn't complete at all (i.e. DateCompleted is null).
All other possibilities are covered with <=2 and >2.

Expr1: IIf(Not IsNull([datecompleted]),
IIf(Weekends([date],[datecompleted])<=2,"Early", "Late"), "Y")

Check first to see if DateCompleted is Null, if not, then computer Early or
Late. If it is, you get "Y". Since this is in a query, you should be ok with
the possible error in the second IIf if DateCompleted is Null. This wouldn't
work in VBA because VBA evaluates the True and False parts of an IIf whether
the condition is True or False. So, in VBA, this would error if
DateCompleted was Null, because it would evaluate the True part of the first
IIf even though is doesn't need to. Jet doesn't do that. It only worries
about the part (true or false) that it needs.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Storing and Retrieving 'DateLastStored' 6
using form's OpenReport to open report with multiple subreports 1
Syntax problem! 4
How do use the Dcount? 13
Count Values 13
Date Question! 3
Help with my function vba code 1
Infamous Business Day Count 7

Back
Top