DLookUp Format

  • Thread starter Thread starter Flopbot
  • Start date Start date
F

Flopbot

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 -
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.
 
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].
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
Back
Top