I know the criteria statement on my DLookUp is wrong, but I don’t know the
correct format. I have the following in Access 03.

“SubFrm Opportunities” taken from “Tbl Opportunities”. Contains field
[Start Time].
“SubSubFrm Actual” taken from “Tbl Actual”. Contains field [Actual Start
Time].
Both tables have the field [Event ID].

The DLookup attached to [Actual Start Time] in “SubSubFrm Actual” should
return the time listed in [Start Time] in “Tbl Opportunities” when the [Event
ID] is identical. I’ve tried the following, but it doesn’t work. Any ideas?

=DLookUp("[Start Time]","Tbl Vol Opportunities", “[EmployeeID] = ” &
[Employee ID])

Are you looking for [Event ID]. or [Employee ID]?
and in table Tbl Opportunities or Tbl Vol Opportunities?
Also, it's never a good idea to have table or column names with embedded
spaces since you have to surround all references with [ ] - which you have
not done.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"Flopbot" wrote:

> I know the criteria statement on my DLookUp is wrong, but I don’t know the
> correct format. I have the following in Access 03.
>
> “SubFrm Opportunities” taken from “Tbl Opportunities”. Contains field
> [Start Time].
> “SubSubFrm Actual” taken from “Tbl Actual”. Contains field [Actual Start
> Time].
> Both tables have the field [Event ID].
>
> The DLookup attached to [Actual Start Time] in “SubSubFrm Actual” should
> return the time listed in [Start Time] in “Tbl Opportunities” when the [Event
> ID] is identical. I’ve tried the following, but it doesn’t work. Any ideas?
>
> =DLookUp("[Start Time]","Tbl Vol Opportunities", “[EmployeeID] = ” &
> [Employee ID])
>

Flopbot -
You need to be exact in your naming of fields, tables, controls, etc. Any
field/table/control name that has any special characters (including spaces)
or are reserved words needs to be enclosed in square brackets. You were not
consistent in your posting, so I am using names that might not be correct.
This assumes the table name is "Tbl Opportunities", and that table has at
least two fields - "Start Time" and "Event ID". It also assumes that you
have a control (maybe a text box) called "Event ID" that has the event id you
want the start time for. If so, this is what you need:

=DLookUp("[Start Time]","[Tbl Opportunities]", "[Event ID] = " & Me.[Event
ID])

You also had one set of the double-quotes that were the right- and
left-sided ones which are no good in Access. You may have gotten those if
you were editing in Microsoft Word or something. You need the " rather than
“ or ” for your code to work.

--
Daryl S


"Flopbot" wrote:

> I know the criteria statement on my DLookUp is wrong, but I don’t know the
> correct format. I have the following in Access 03.
>
> “SubFrm Opportunities” taken from “Tbl Opportunities”. Contains field
> [Start Time].
> “SubSubFrm Actual” taken from “Tbl Actual”. Contains field [Actual Start
> Time].
> Both tables have the field [Event ID].
>
> The DLookup attached to [Actual Start Time] in “SubSubFrm Actual” should
> return the time listed in [Start Time] in “Tbl Opportunities” when the [Event
> ID] is identical. I’ve tried the following, but it doesn’t work. Any ideas?
>
> =DLookUp("[Start Time]","Tbl Vol Opportunities", “[EmployeeID] = ” &
> [Employee ID])
>

It looks like I did a really lousy job of describing my problem. I
apologize. Hopefully, this will help. It might be easiest to ignore my
previous post completely (and it’s many mistakes) and go solely by this
information.

The desired =DLookUp expression is located in the “default value” of a text
box on [SubFrm Actual] whose source is [Tbl Actual]. The main form is called
[Frm Opportunities] whose source is [Tbl Opportunities]. The control source
for my text box (the one with the expression) is [Actual Start Time].

[Start Time] is a date/time field in [Tbl Opportunities]
[Event ID] is an auto number field in [Tbl Opportunities]
[Actual Start Time] is a date/time field in [Tbl Actual]
[Event ID] is also found in [Tbl Actual]

Right now, my expression picks out the first time listed at the top of the
[Start Time] field in [Tbl Opportunities]. I want it to somehow find the
[Event ID] of the record that its currently “in” and place the corresponding
time in [Actual Start Time] field on [Tbl Actual].


"BruceM via AccessMonster.com" wrote:

> "Doesn't work" is too vague to be of any real use. I can say that since Tbl
> Vol Opportunities has spaces in the table name it needs to be enclosed in
> square brackets as you have done for the field name. Also, if you are
> looking for an equivalent [Event ID] field you need to use [Event ID], not
> [Employee ID] in the expression.
>
> It is unclear what you mean by the "DLookup attached to [Actual Start Time]".
>
> I will make the following assumptions: The expression (with the corrections
> suggested above) is used as the Control Source of a text box on a form;
> EventID is a number field; and EventID is both a field in [Tbl Vol
> Opportunities] and a field in the form's Record Source. Given those
> assumptions, the expression will look up [Start Time] in the first record in
> [Tbl Vol Opportunities] in which EventID matches the EventID field in the
> form's Record Source.
>
> You did not describe anything about [Tbl Vol Opportunities], but you provided
> some information about [Tbl Opportunities]. Are they the same?
>

Thank you everyone for your help and advise!

I think I somewhat understand your statement about VBA and control source. .
..possibly. My expression is located in the “default value” of a text box on
[SubFrm Actual] whose source is [Tbl Actual] so I’m not using VBA though I
tried three expressions below anyways.

This one returns the first time at the top of the field.
=DLookUp("[Start Time]","[Tbl Vol Opportunities]")

This one returns: #Name?
=DLookUp("[Start Time]","[Tbl Vol Opportunities]", "[Event ID] = "&
Me.[Event ID])

This one returns: #Error
=DLookUp("[Start Time]","[Tbl Vol Opportunities]", "[Event ID] = "& [Event
ID])


"BruceM via AccessMonster.com" wrote:

> The Me. prefix works only in VBA. The = sign suggests a Control Source
> expression. Given the inconsistencies in the original posting it is possible
> it is a fragment from VBA, but if it is a Control Source the Me. needs to be
> dropped.
>
> Daryl S wrote:
> >Flopbot -
> >You need to be exact in your naming of fields, tables, controls, etc. Any
> >field/table/control name that has any special characters (including spaces)
> >or are reserved words needs to be enclosed in square brackets. You were not
> >consistent in your posting, so I am using names that might not be correct.
> >This assumes the table name is "Tbl Opportunities", and that table has at
> >least two fields - "Start Time" and "Event ID". It also assumes that you
> >have a control (maybe a text box) called "Event ID" that has the event id you
> >want the start time for. If so, this is what you need:
> >
> >=DLookUp("[Start Time]","[Tbl Opportunities]", "[Event ID] = " & Me.[Event
> >ID])
> >
> >You also had one set of the double-quotes that were the right- and
> >left-sided ones which are no good in Access. You may have gotten those if
> >you were editing in Microsoft Word or something. You need the " rather than
> >“ or ” for your code to work.
> >