DLookup in Query

[Guest]

I am using this code in a query field in design view:

expr1: DLookup("TimeItemNo","lkupTime","TimeDesc=" &
DateDiff("n",[StartTime],[FinishTime]))

to pull up a code number (timeItemNo) that matches a certain period of
elapsed time in minutes but i am getting the error message:

syntax error (missing operator) in query expression 'TimeDesc = '.

followed by:

unknown

the timeItemNo and TimeDesc fields are numbers as is, I imagine, the result
of the DateDiff calculation. The expression worked exactly as typed when used
on a form.

Any help much appreciated!

 

[Allen Browne]

Try:
expr1: DLookup("TimeItemNo","lkupTime","TimeDesc=" &
Nz(DateDiff("n",[StartTime],[FinishTime])),0)

If StartTime or FinishTime were null, that might fix the problem.

 

[Guest]

Thanks Allen. I tried what you suggested and got another error message
telling me i had wrong arguments in the expression (i pasted it directly from
your post). Any other suggestions.

Cheers
Chris

Try:
expr1: DLookup("TimeItemNo","lkupTime","TimeDesc=" &
Nz(DateDiff("n",[StartTime],[FinishTime])),0)

If StartTime or FinishTime were null, that might fix the problem.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am using this code in a query field in design view:

expr1: DLookup("TimeItemNo","lkupTime","TimeDesc=" &
DateDiff("n",[StartTime],[FinishTime]))

to pull up a code number (timeItemNo) that matches a certain period of
elapsed time in minutes but i am getting the error message:

syntax error (missing operator) in query expression 'TimeDesc = '.

followed by:

unknown

the timeItemNo and TimeDesc fields are numbers as is, I imagine, the
result
of the DateDiff calculation. The expression worked exactly as typed when
used
on a form.

Any help much appreciated!

 

[Allen Browne]

It should work if:
- TimeItemNo is the name of the field to retrieve the value from;
- lkupTime is the name of the table to look in;
- TimeDesc is a Number field in lkupTime;
- [StartTime] and [FinishTime] are date/time fields in the query.

For more help with DLookup(), see:
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen. I tried what you suggested and got another error message
telling me i had wrong arguments in the expression (i pasted it directly
from
your post). Any other suggestions.

Cheers
Chris

Try:
expr1: DLookup("TimeItemNo","lkupTime","TimeDesc=" &
Nz(DateDiff("n",[StartTime],[FinishTime])),0)

If StartTime or FinishTime were null, that might fix the problem.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am using this code in a query field in design view:

expr1: DLookup("TimeItemNo","lkupTime","TimeDesc=" &
DateDiff("n",[StartTime],[FinishTime]))

to pull up a code number (timeItemNo) that matches a certain period of
elapsed time in minutes but i am getting the error message:

syntax error (missing operator) in query expression 'TimeDesc = '.

followed by:

unknown

the timeItemNo and TimeDesc fields are numbers as is, I imagine, the
result
of the DateDiff calculation. The expression worked exactly as typed
when
used
on a form.

Any help much appreciated!

 

[Guest]

Thanks Allen....all those "ifs" were true, and i checked out the link you
gave me. To no avail! So i played around a bit. I found if i just substituted
a number for the datediff calculation, it worked, as in

expr1: DLookup("TimeItemNo","lkupTime","TimeDesc=" & 120)

which return the item code for 120 minutes to every record, unsurprisingly!.

Then i decided i would change the TimeDesc field in lkupTime to a text field
and made the code thus:

expr1: DLookup("TimeItemNo","lkupTime","TimeDesc='" &
DateDiff("n",[StartTime],[FinishTime]) & "' ")

AND IT WORKED!!

But I am uneasy about having essentially a minutes field defined as text.
What do you think is going on here??

It should work if:
- TimeItemNo is the name of the field to retrieve the value from;
- lkupTime is the name of the table to look in;
- TimeDesc is a Number field in lkupTime;
- [StartTime] and [FinishTime] are date/time fields in the query.

For more help with DLookup(), see:
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen. I tried what you suggested and got another error message
telling me i had wrong arguments in the expression (i pasted it directly
from
your post). Any other suggestions.

Cheers
Chris

Try:
expr1: DLookup("TimeItemNo","lkupTime","TimeDesc=" &
Nz(DateDiff("n",[StartTime],[FinishTime])),0)

If StartTime or FinishTime were null, that might fix the problem.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am using this code in a query field in design view:

expr1: DLookup("TimeItemNo","lkupTime","TimeDesc=" &
DateDiff("n",[StartTime],[FinishTime]))

to pull up a code number (timeItemNo) that matches a certain period of
elapsed time in minutes but i am getting the error message:

syntax error (missing operator) in query expression 'TimeDesc = '.

followed by:

unknown

the timeItemNo and TimeDesc fields are numbers as is, I imagine, the
result
of the DateDiff calculation. The expression worked exactly as typed
when
used
on a form.

Any help much appreciated!

 

[Allen Browne]

You have extra quotes in there now (because of the Text field presumably.)
Not a good solution.

TimeDesc should be a Number field (size Long Integer) to match the
expression.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen....all those "ifs" were true, and i checked out the link you
gave me. To no avail! So i played around a bit. I found if i just
substituted
a number for the datediff calculation, it worked, as in

expr1: DLookup("TimeItemNo","lkupTime","TimeDesc=" & 120)

which return the item code for 120 minutes to every record,
unsurprisingly!.

Then i decided i would change the TimeDesc field in lkupTime to a text
field
and made the code thus:

expr1: DLookup("TimeItemNo","lkupTime","TimeDesc='" &
DateDiff("n",[StartTime],[FinishTime]) & "' ")

AND IT WORKED!!

But I am uneasy about having essentially a minutes field defined as text.
What do you think is going on here??

It should work if:
- TimeItemNo is the name of the field to retrieve the value from;
- lkupTime is the name of the table to look in;
- TimeDesc is a Number field in lkupTime;
- [StartTime] and [FinishTime] are date/time fields in the query.

For more help with DLookup(), see:
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen. I tried what you suggested and got another error message
telling me i had wrong arguments in the expression (i pasted it
directly
from
your post). Any other suggestions.

Cheers
Chris

:

Try:
expr1: DLookup("TimeItemNo","lkupTime","TimeDesc=" &
Nz(DateDiff("n",[StartTime],[FinishTime])),0)

If StartTime or FinishTime were null, that might fix the problem.

I am using this code in a query field in design view:

expr1: DLookup("TimeItemNo","lkupTime","TimeDesc=" &
DateDiff("n",[StartTime],[FinishTime]))

to pull up a code number (timeItemNo) that matches a certain period
of
elapsed time in minutes but i am getting the error message:

syntax error (missing operator) in query expression 'TimeDesc = '.

followed by:

unknown

the timeItemNo and TimeDesc fields are numbers as is, I imagine, the
result
of the DateDiff calculation. The expression worked exactly as typed
when
used
on a form.

 

[Guest]

Allen, please, don't give up on me yet!!
I know it should work, i agree with you that the text field thing is not
good, but strangely it works...i have gone through and doubled check all the
formatting, they are all number fields with long integer...i am tearing my
hair out!!

Anyway, it seems to work , and i can use the dlookup results in further
calculations in the query by setting the query design view field format to
'fixed'. But now i have another bizarre problem...

when i refer to a calculated field in another calculation, i am asked to
'enter the parameter' that is the name of the calculated field, i hit ok on
that dialog box, and it still does the calculation corrrectly, but i don't
want these annoying parameter dialog boxes coming up...so here is an example:

this is my first calculated field...works well:
x: [mainitemuv]+[consultuv]

here's the next one:
Expr4: [x]+[timeuv]

i get a parameter dialog box asking me to enter 'x'...then i click ok, and
the query runs even though i don't enter anything in that dialog box

and btw, i have gone into the query --> parameters menu and stated the
format for x but all i get is the same dialog box, but this time i get no
calculation performed in the query

so sorry to throw all this at you...hope you can help

Chris

You have extra quotes in there now (because of the Text field presumably.)
Not a good solution.

TimeDesc should be a Number field (size Long Integer) to match the
expression.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen....all those "ifs" were true, and i checked out the link you
gave me. To no avail! So i played around a bit. I found if i just
substituted
a number for the datediff calculation, it worked, as in

expr1: DLookup("TimeItemNo","lkupTime","TimeDesc=" & 120)

which return the item code for 120 minutes to every record,
unsurprisingly!.

Then i decided i would change the TimeDesc field in lkupTime to a text
field
and made the code thus:

expr1: DLookup("TimeItemNo","lkupTime","TimeDesc='" &
DateDiff("n",[StartTime],[FinishTime]) & "' ")

AND IT WORKED!!

But I am uneasy about having essentially a minutes field defined as text.
What do you think is going on here??

It should work if:
- TimeItemNo is the name of the field to retrieve the value from;
- lkupTime is the name of the table to look in;
- TimeDesc is a Number field in lkupTime;
- [StartTime] and [FinishTime] are date/time fields in the query.

For more help with DLookup(), see:
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen. I tried what you suggested and got another error message
telling me i had wrong arguments in the expression (i pasted it
directly
from
your post). Any other suggestions.

Cheers
Chris

:

Try:
expr1: DLookup("TimeItemNo","lkupTime","TimeDesc=" &
Nz(DateDiff("n",[StartTime],[FinishTime])),0)

If StartTime or FinishTime were null, that might fix the problem.

I am using this code in a query field in design view:

expr1: DLookup("TimeItemNo","lkupTime","TimeDesc=" &
DateDiff("n",[StartTime],[FinishTime]))

to pull up a code number (timeItemNo) that matches a certain period
of
elapsed time in minutes but i am getting the error message:

syntax error (missing operator) in query expression 'TimeDesc = '.

followed by:

unknown

the timeItemNo and TimeDesc fields are numbers as is, I imagine, the
result
of the DateDiff calculation. The expression worked exactly as typed
when
used
on a form.

 

[Guest]

it's driving me crazy...why won't my query recognise it's own calculated
fields?? and ask me to enter the parameter??!!

 

[Guest]

I'm sorry Allen

I am a dummy...mind you i spent all day on this ...aaargh

but the answer was simple...i had to take the field out of the 'group by'
statement, and now it works very well.

But i'm still unsettled as to why..the other thing doesn't work.....oh well!

chris

it's driving me crazy...why won't my query recognise it's own calculated
fields?? and ask me to enter the parameter??!!

I am using this code in a query field in design view:

expr1: DLookup("TimeItemNo","lkupTime","TimeDesc=" &
DateDiff("n",[StartTime],[FinishTime]))

to pull up a code number (timeItemNo) that matches a certain period of
elapsed time in minutes but i am getting the error message:

syntax error (missing operator) in query expression 'TimeDesc = '.

followed by:

unknown

the timeItemNo and TimeDesc fields are numbers as is, I imagine, the result
of the DateDiff calculation. The expression worked exactly as typed when used
on a form.

Any help much appreciated!