Does this DLookup syntax Look correct?

[rdjohnso]

Here is the code:

=DLookUp("[OrgCosts]","rdqry_AllocatedOrgHoursRatesPerPeriod","[Organization] = 'Daou' AND Month([dtmStart]) = '1' AND Year([dtmStart]) = " & Year(Date()))

The Query is pulling data from another query.... but the names of the fields are exactly as shown....
The query itselfruns fine and pulls back legit data so it leads me to believe my 'DLookup' is incorrect.

It gives me an '#Error' for the field when I try to run the report.

Any assistance appreciated.

Thanks

Ron

 

[Zion7]

This looks wrong

...Month([dtmStart]) = '1' AND...

should be (no single quotes)

...Month([dtmStart]) = 1 AND...

 

[rdjohnso]

thanks Zion... I tried that but it didnt fix it....

R

 

[Zion7]

D'you go' me Ron?

You said the query itself, runs fine.
Did you Try to run with the same criteria?

 

[rdjohnso]

you got me Zion....

the 'Daou' part works but
guess the other 2 parts dont work....

the values of 'dtmStart' example: 1/1/2006
I need to get the record where the month of dtmStart
is January or 1 AND year of dtmStart is currentYear.(2006)

R

any other ide

 

[Zion7]

Just for the sake of it, try this. (I know it worked for me)

....And dtmStart Like '1/*/2006'")

and if that works, try

....And dtmStart Like '1/*/" & Year(Date()) & "'")

 

[rdjohnso]

tried both... what am I missing?

=DLookUp([OrgCosts],"rdqry_AllocatedOrgHoursRatesPerPeriod","[Organization] = 'Daou' And dtmStart Like '1/*/" & Year(Date()) & "'")

 

[Zion7]

Try obvious things Ron,

I'm suspicious of dtmStart.

try just giving it a straight date

...And dtmStart = #1/4/2006#"

or something, you know will work,
to lessen the variables...

 

[rdjohnso]

well this doesnt even work...

=DLookUp("[OrgCosts]","rdqry_AllocatedOrgHoursRatesPerPeriod","[Organization] = 'Daou'")

it would bring back about 5 records so thats probably why this doesnt work....

 

[rdjohnso]

ok so I thought I would change it up.... something more literal using the strMonth from another field.

Anything obvious I am missing?

=DLookUp("[OrgCosts]","rdqry_AllocatedOrgHoursRatesPerPeriod","[Organization] = 'Daou' AND [strMonth] = 'Jan'")