A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

DLookUp Format



 
 
Thread Tools Display Modes
  #1  
Old April 28th, 2010, 04:02 PM posted to microsoft.public.access.forms
Flopbot
external usenet poster
 
Posts: 60
Default DLookUp Format

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])

  #2  
Old April 28th, 2010, 04:32 PM posted to microsoft.public.access.forms
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default DLookUp Format

"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?

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])


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201004/1

  #3  
Old April 28th, 2010, 05:06 PM posted to microsoft.public.access.forms
Dorian
external usenet poster
 
Posts: 542
Default DLookUp Format

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])

  #4  
Old April 28th, 2010, 05:20 PM posted to microsoft.public.access.forms
Daryl S[_2_]
external usenet poster
 
Posts: 881
Default DLookUp Format

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])

  #5  
Old April 28th, 2010, 06:11 PM posted to microsoft.public.access.forms
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default DLookUp Format

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.

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.

[quoted text clipped - 11 lines]
=DLookUp("[Start Time]","Tbl Vol Opportunities", “[EmployeeID] = ” &
[Employee ID])


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201004/1

  #6  
Old April 30th, 2010, 12:30 AM posted to microsoft.public.access.forms
Flopbot
external usenet poster
 
Posts: 60
Default DLookUp Format

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?


  #7  
Old April 30th, 2010, 12:43 AM posted to microsoft.public.access.forms
Flopbot
external usenet poster
 
Posts: 60
Default DLookUp Format

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.


  #8  
Old April 30th, 2010, 12:51 PM posted to microsoft.public.access.forms
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default DLookUp Format

Since you are not using VBA, the Me. prefix will not work in the second
example.

In the third example, I suspect the error is because you are in the subform
based on the Actual table, and there is no EventID value (or, more
specifically, because EventID is null. Have you set the Link Child and Link
Master properties of the subform to EventID?

Assuming the expression is in a text box bound to a Date/Time field, and that
EventID is a LongInteger field in tblActual, and that the Link Child and Link
Master properties are set up correctly, I think it would work, but since you
are looking up a value from the parent record there is no need for the Domain
function DLookup. Rather, the Default Value could be:
=Forms!frmMain!StartTime

As an alternative, with VBA you could use the Current event of the subform:

Me.txtStartTime.DefaultValue = Me.Parent.StartTime

where txtStartTime is the text box bound to the date/time field in tblActual.

Is StartTime in tblActual subject to change? If not, there is no reason to
add it at all, as the information is already in the parent record.

As an aside, you will be doing yourself a favor if you limit names to letters,
numbers, and underscores (no spaces or special characters).

Flopbot wrote:
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])

The Me. prefix works only in VBA. The = sign suggests a Control Source
expression. Given the inconsistencies in the original posting it is possible

[quoted text clipped - 18 lines]
you were editing in Microsoft Word or something. You need the " rather than
“ or ” for your code to work.


--
Message posted via http://www.accessmonster.com

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:31 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.