SQL Mix up?

[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

 

[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

 

[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?

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

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

 

[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

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?

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

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