SQL Mix up?

J

JIT

I am running a query with this in the criteria field:

IIf(DateDiff("d","Arrival Date",[Start Date])>[Rates]![FreeDays],[Departure
Date] Or IIf(DateDiff("d","ArrivalDate",[Start
Date])<[Rates]![FreeDays],((Max Charge Days Total-[Rates]![FreeDays]-DateDiff
("d", ArrivalDate, StartDate))))

It gives me an error warning that the expression has an invalid (dot) or !
Operator. Then it highlights the openning parenthesis right before the last
"d" in the expression. Does anyone know what is wrong?

Thank you
 
M

Maurice

Well in the first part of the select statement you've placed ArrivalDate
between " " and you did not in the last part of the statment. I don't know if
that's a typo but try changing it.

hth
 
J

JIT

IIf(DateDiff("d","Arrival Date",[Start Date])>CLng([Rates]![Free Days]),(Date
Range Detention Query_1]![Departure Date]) Or
IIf(DateDiff("d","ArrivalDate",[Start Date])<CLng([Rates]![Free Days]),(("Max
Charge Days Total")-CLng(([Rates]![Free Days])-(DateDiff("d","Arrival
Date",[Start Date])))))


I have this and am getting the error: Data type mismatch in the criteria
field. Do you know what that is?

Maurice said:
Well in the first part of the select statement you've placed ArrivalDate
between " " and you did not in the last part of the statment. I don't know if
that's a typo but try changing it.

hth
--
Maurice Ausum


JIT said:
I am running a query with this in the criteria field:

IIf(DateDiff("d","Arrival Date",[Start Date])>[Rates]![FreeDays],[Departure
Date] Or IIf(DateDiff("d","ArrivalDate",[Start
Date])<[Rates]![FreeDays],((Max Charge Days Total-[Rates]![FreeDays]-DateDiff
("d", ArrivalDate, StartDate))))

It gives me an error warning that the expression has an invalid (dot) or !
Operator. Then it highlights the openning parenthesis right before the last
"d" in the expression. Does anyone know what is wrong?

Thank you
 
M

Maurice

Why are you converting the values to long? Try the expression without the
clng and see what the query comes up with...
So: ([Rates]![Free Days]),([Date Range Detention Query_1]![Departure Date])
instead of ([Rates]![Free Days]),(Date Range Detention Query_1]![Departure
Date])

btw did you notice the lacking of a bracket right before the word Date?

hth
--
Maurice Ausum


JIT said:
IIf(DateDiff("d","Arrival Date",[Start Date])>CLng([Rates]![Free Days]),(Date
Range Detention Query_1]![Departure Date]) Or
IIf(DateDiff("d","ArrivalDate",[Start Date])<CLng([Rates]![Free Days]),(("Max
Charge Days Total")-CLng(([Rates]![Free Days])-(DateDiff("d","Arrival
Date",[Start Date])))))


I have this and am getting the error: Data type mismatch in the criteria
field. Do you know what that is?

Maurice said:
Well in the first part of the select statement you've placed ArrivalDate
between " " and you did not in the last part of the statment. I don't know if
that's a typo but try changing it.

hth
--
Maurice Ausum


JIT said:
I am running a query with this in the criteria field:

IIf(DateDiff("d","Arrival Date",[Start Date])>[Rates]![FreeDays],[Departure
Date] Or IIf(DateDiff("d","ArrivalDate",[Start
Date])<[Rates]![FreeDays],((Max Charge Days Total-[Rates]![FreeDays]-DateDiff
("d", ArrivalDate, StartDate))))

It gives me an error warning that the expression has an invalid (dot) or !
Operator. Then it highlights the openning parenthesis right before the last
"d" in the expression. Does anyone know what is wrong?

Thank you
 

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

Update SQL 1
Query Question 1 1
Update Error 1
Query Question 1
Parenthesis and comma status 2
Expression Error on Parameter Query with link to Cross Tab Query 8
Error 3075 4
Current Date in Query 12

Top