Invalid syntax in dlookup

I am trying to lookup information from a table using the dlookup function.
Using a subquery worked beautifully but it won't allow me to use a subquery
in a query for a report. The following is what I have so far, but I keep
getting a error "Invalid Syntax". Could someone please take a look at my code
and tell me where I'm going wrong.
Rates:
IIf(IsNull(DLookUp("[Rate]","[tblRates]","[qry_ServMiscInv]![ServDate] & "
Between [StartDate]
and [EndDate] And [ServiceType] = '" & [ServiceType] & "' And [RateID] = " &
[tblCustomer_Details]![RateID] &
"")),IIf([Sleepover]=True,tblContracts.SORate,tblContracts.Rate,DLookU p("[Rate]","[tblRates]",[qry_ServMiscInv]![ServDate] & " Between [StartDate]
and [EndDate] And [ServiceType] = '" & [qry_ServMiscInv]![ServiceType] &
"'And [RateID] = '" & [tblCustomer_Details]![RateID] & "'"))

thanks in advance

You're missing a space in front of "And [RateID]":


Rates:
IIf(IsNull(DLookUp("[Rate]","[tblRates]","[qry_ServMiscInv]![ServDate] & "
Between [StartDate]
and [EndDate] And [ServiceType] = '" & [ServiceType] & "' And [RateID] = " &
[tblCustomer_Details]![RateID] &
"")),IIf([Sleepover]=True,tblContracts.SORate,tblContracts.Rate,DLookU p("[Rate]","[tblRates]",[qry_ServMiscInv]![ServDate]
& " Between [StartDate]
and [EndDate] And [ServiceType] = '" & [qry_ServMiscInv]![ServiceType] &
"' And [RateID] = '" & [tblCustomer_Details]![RateID] & "'"))

--

Ken Snell
http://www.accessmvp.com/KDSnell/



"Maresdd" wrote in message
...
I am trying to lookup information from a table using the dlookup function.
Using a subquery worked beautifully but it won't allow me to use a
subquery
in a query for a report. The following is what I have so far, but I keep
getting a error "Invalid Syntax". Could someone please take a look at my
code
and tell me where I'm going wrong.
Rates:
IIf(IsNull(DLookUp("[Rate]","[tblRates]","[qry_ServMiscInv]![ServDate] & "
Between [StartDate]
and [EndDate] And [ServiceType] = '" & [ServiceType] & "' And [RateID] = "
&
[tblCustomer_Details]![RateID] &
"")),IIf([Sleepover]=True,tblContracts.SORate,tblContracts.Rate,DLookU p("[Rate]","[tblRates]",[qry_ServMiscInv]![ServDate]
& " Between [StartDate]
and [EndDate] And [ServiceType] = '" & [qry_ServMiscInv]![ServiceType] &
"'And [RateID] = '" & [tblCustomer_Details]![RateID] & "'"))

thanks in advance

I've put the space in but it still comes up with Invalid Syntax entered an
operand without operator. It comes back to the 2nd [Rate] in the 2nd DLookup.

Rates:
IIf(IsNull(DLookUp("[Rate]","[tblRates]","[qry_ServMiscInv]![ServDate] & "
Between [StartDate]
and [EndDate] And [ServiceType] = '" & [ServiceType] & "' And [RateID] = " &
[tblCustomer_Details]![RateID] &
"")),IIf([Sleepover]=True,tblContracts.SORate,tblContracts.Rate,DLookU p("[Rate]","[tblRates]",[qry_ServMiscInv]![ServDate]
& " Between [StartDate]
and [EndDate] And [ServiceType] = '" & [qry_ServMiscInv]![ServiceType] &
"' And [RateID] = '" & [tblCustomer_Details]![RateID] & "'"))


"Ken Snell" wrote:

You're missing a space in front of "And [RateID]":


Rates:
IIf(IsNull(DLookUp("[Rate]","[tblRates]","[qry_ServMiscInv]![ServDate] & "
Between [StartDate]
and [EndDate] And [ServiceType] = '" & [ServiceType] & "' And [RateID] = " &
[tblCustomer_Details]![RateID] &
"")),IIf([Sleepover]=True,tblContracts.SORate,tblContracts.Rate,DLookU p("[Rate]","[tblRates]",[qry_ServMiscInv]![ServDate]
& " Between [StartDate]
and [EndDate] And [ServiceType] = '" & [qry_ServMiscInv]![ServiceType] &
"' And [RateID] = '" & [tblCustomer_Details]![RateID] & "'"))

--

Ken Snell
http://www.accessmvp.com/KDSnell/



"Maresdd" wrote in message
...
I am trying to lookup information from a table using the dlookup function.
Using a subquery worked beautifully but it won't allow me to use a
subquery
in a query for a report. The following is what I have so far, but I keep
getting a error "Invalid Syntax". Could someone please take a look at my
code
and tell me where I'm going wrong.
Rates:
IIf(IsNull(DLookUp("[Rate]","[tblRates]","[qry_ServMiscInv]![ServDate] & "
Between [StartDate]
and [EndDate] And [ServiceType] = '" & [ServiceType] & "' And [RateID] = "
&
[tblCustomer_Details]![RateID] &
"")),IIf([Sleepover]=True,tblContracts.SORate,tblContracts.Rate,DLookU p("[Rate]","[tblRates]",[qry_ServMiscInv]![ServDate]
& " Between [StartDate]
and [EndDate] And [ServiceType] = '" & [qry_ServMiscInv]![ServiceType] &
"'And [RateID] = '" & [tblCustomer_Details]![RateID] & "'"))

thanks in advance


.

Just looking at this much
DLookUp("[Rate]","[tblRates]","[qry_ServMiscInv]![ServDate] & " Between
[StartDate] and [EndDate] And [ServiceType] = '" & [ServiceType] & "' And
[RateID] = " & [tblCustomer_Details]![RateID] & "")
it is wrong in several ways.
First, I think StartDate and EndDate are fields in tblRates, so they should
be inside of quotes.
Then, I would add # around all date values. I'm not sure where
qry_ServMiscInv and tblCustomer_Details are from.

I think this might work for the DLookup() syntax:
DLookUp("[Rate]","[tblRates]","[StartDate]=#" &
[qry_ServMiscInv]![ServDate] & " and [EndDate] =#" &
[qry_ServMiscInv]![ServDate] & "# And [ServiceType] = '" & [ServiceType] &
"' And [RateID] = " & [tblCustomer_Details]![RateID])

--
Duane Hookom
MS Access MVP


"Maresdd" wrote in message
...
I've put the space in but it still comes up with Invalid Syntax entered an
operand without operator. It comes back to the 2nd [Rate] in the 2nd
DLookup.

Rates:
IIf(IsNull(DLookUp("[Rate]","[tblRates]","[qry_ServMiscInv]![ServDate] & "
Between [StartDate]
and [EndDate] And [ServiceType] = '" & [ServiceType] & "' And [RateID] = "
&
[tblCustomer_Details]![RateID] &
"")),IIf([Sleepover]=True,tblContracts.SORate,tblContracts.Rate,DLookU p("[Rate]","[tblRates]",[qry_ServMiscInv]![ServDate]
& " Between [StartDate]
and [EndDate] And [ServiceType] = '" & [qry_ServMiscInv]![ServiceType] &
"' And [RateID] = '" & [tblCustomer_Details]![RateID] & "'"))


"Ken Snell" wrote:

You're missing a space in front of "And [RateID]":


Rates:
IIf(IsNull(DLookUp("[Rate]","[tblRates]","[qry_ServMiscInv]![ServDate] &
"
Between [StartDate]
and [EndDate] And [ServiceType] = '" & [ServiceType] & "' And [RateID] =
" &
[tblCustomer_Details]![RateID] &
"")),IIf([Sleepover]=True,tblContracts.SORate,tblContracts.Rate,DLookU p("[Rate]","[tblRates]",[qry_ServMiscInv]![ServDate]
& " Between [StartDate]
and [EndDate] And [ServiceType] = '" & [qry_ServMiscInv]![ServiceType] &
"' And [RateID] = '" & [tblCustomer_Details]![RateID] & "'"))

--

Ken Snell
http://www.accessmvp.com/KDSnell/



"Maresdd" wrote in message
...
I am trying to lookup information from a table using the dlookup
function.
Using a subquery worked beautifully but it won't allow me to use a
subquery
in a query for a report. The following is what I have so far, but I
keep
getting a error "Invalid Syntax". Could someone please take a look at
my
code
and tell me where I'm going wrong.
Rates:
IIf(IsNull(DLookUp("[Rate]","[tblRates]","[qry_ServMiscInv]![ServDate]
& "
Between [StartDate]
and [EndDate] And [ServiceType] = '" & [ServiceType] & "' And [RateID]
= "
&
[tblCustomer_Details]![RateID] &
"")),IIf([Sleepover]=True,tblContracts.SORate,tblContracts.Rate,DLookU p("[Rate]","[tblRates]",[qry_ServMiscInv]![ServDate]
& " Between [StartDate]
and [EndDate] And [ServiceType] = '" & [qry_ServMiscInv]![ServiceType]
&
"'And [RateID] = '" & [tblCustomer_Details]![RateID] & "'"))

thanks in advance


.

On Wed, 24 Feb 2010 15:46:04 -0800, Maresdd
wrote:

I am trying to lookup information from a table using the dlookup function.
Using a subquery worked beautifully but it won't allow me to use a subquery
in a query for a report. The following is what I have so far, but I keep
getting a error "Invalid Syntax". Could someone please take a look at my code
and tell me where I'm going wrong.

You've got a lot of misplaced quotes and many other problems. What you want is
to create a valid SQL WHERE clause (without the word WHERE) as the third
argument, by concatenating string constants for the fieldnames and operators
with string variables or form references for the variable portion. You also
need appropriate delimiters - ' or " for Text fields, # for dates, no
delimiter for numbers.

Also, ! is a delimiter for Form references, not for table fields.

Finally you are using qry_ServMiscInv!ServDate as part of your third
operand... but your DLookUp doesn't reference that query, it references
tblRates.

Could you explain in words what you're trying to accomplish???? What is this
query, what is the table, and how do you want to specify which record contains
the desired rate?
--

John W. Vinson [MVP]