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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|