using a variable to specify field name in dlookup function
Author 
Message 
Larr #1 / 5

using a variable to specify field name in dlookup function
Ok, here is the problem I have a table that will always have ona, and only one record two of the field names are: 1998 1999 what I want to do is lookup the value of field 1998 or 1998, based on a variable. below is a sample of the code I am trying to use: dim x, y as integer x=1998 do while x < 2000 y = dlookup(x, "Ships") docmd.openquery "AppendYearTotals" x = x + 1 loop I've tried different syntaxes for x with no success if I use y=dlookup(x,"Ships"), Y is given the value of x (y=1998) same thing if I use [x] if I use "x" or "[x]", I get errors If I type the filed name in directly, ie y=dlookup("[1998]","Ships") it works perfectly, (y=3), which is the value of the field. Can anyone tell me what syntax to use to make this work right? You will be a lifesaver. Thanks, Larry

Wed, 10 Jul 2002 03:00:00 GMT 


Judith Seyfer #2 / 5

using a variable to specify field name in dlookup function
Larry, Dim x as String, y as Integer x="1998" do while x<"2000" The rest of your code should then work. J Seyfert
Quote: > Ok, here is the problem > I have a table that will always have ona, and only one record > two of the field names are: > 1998 > 1999 > what I want to do is lookup the value of field 1998 or 1998, based on a > variable. > below is a sample of the code I am trying to use: > dim x, y as integer > x=1998 > do while x < 2000 > y = dlookup(x, "Ships") > docmd.openquery "AppendYearTotals" > x = x + 1 > loop > I've tried different syntaxes for x with no success > if I use y=dlookup(x,"Ships"), Y is given the value of x (y=1998) > same thing if I use [x] > if I use "x" or "[x]", I get errors > If I type the filed name in directly, ie y=dlookup("[1998]","Ships") it > works perfectly, (y=3), which is the value of the field. > Can anyone tell me what syntax to use to make this work right? > You will be a lifesaver. > Thanks, > Larry

Wed, 10 Jul 2002 03:00:00 GMT 


Larr #3 / 5

using a variable to specify field name in dlookup function
Judy, I tried your suggestion and met with the same result. Any other ideas?
Quote: > Larry, > Dim x as String, y as Integer > x="1998" > do while x<"2000" > The rest of your code should then work. > J Seyfert
> > Ok, here is the problem > > I have a table that will always have ona, and only one record > > two of the field names are: > > 1998 > > 1999 > > what I want to do is lookup the value of field 1998 or 1998, based on a > > variable. > > below is a sample of the code I am trying to use: > > dim x, y as integer > > x=1998 > > do while x < 2000 > > y = dlookup(x, "Ships") > > docmd.openquery "AppendYearTotals" > > x = x + 1 > > loop > > I've tried different syntaxes for x with no success > > if I use y=dlookup(x,"Ships"), Y is given the value of x (y=1998) > > same thing if I use [x] > > if I use "x" or "[x]", I get errors > > If I type the filed name in directly, ie y=dlookup("[1998]","Ships") it > > works perfectly, (y=3), which is the value of the field. > > Can anyone tell me what syntax to use to make this work right? > > You will be a lifesaver. > > Thanks, > > Larry

Wed, 10 Jul 2002 03:00:00 GMT 


Judith Seyfer #4 / 5

using a variable to specify field name in dlookup function
SorryTry this (I tested this one) dim x As String, y As Integer, z As String z = "1997" Do While z < "2000" x = "[" & z & "]" y = DLookup(x, "tblSM_References") z = z + 1 Loop etc. Judy
Quote: > Judy, > I tried your suggestion and met with the same result. > Any other ideas?
> > Larry, > > Dim x as String, y as Integer > > x="1998" > > do while x<"2000" > > The rest of your code should then work. > > J Seyfert
> > > Ok, here is the problem > > > I have a table that will always have ona, and only one record > > > two of the field names are: > > > 1998 > > > 1999 > > > what I want to do is lookup the value of field 1998 or 1998, based on a > > > variable. > > > below is a sample of the code I am trying to use: > > > dim x, y as integer > > > x=1998 > > > do while x < 2000 > > > y = dlookup(x, "Ships") > > > docmd.openquery "AppendYearTotals" > > > x = x + 1 > > > loop > > > I've tried different syntaxes for x with no success > > > if I use y=dlookup(x,"Ships"), Y is given the value of x (y=1998) > > > same thing if I use [x] > > > if I use "x" or "[x]", I get errors > > > If I type the filed name in directly, ie y=dlookup("[1998]","Ships") it > > > works perfectly, (y=3), which is the value of the field. > > > Can anyone tell me what syntax to use to make this work right? > > > You will be a lifesaver. > > > Thanks, > > > Larry

Wed, 10 Jul 2002 03:00:00 GMT 


Larr #5 / 5

using a variable to specify field name in dlookup function
Thank you, Judy, You are truly a lifesaver. Larry
Quote: > SorryTry this (I tested this one) > dim x As String, y As Integer, z As String > z = "1997" > Do While z < "2000" > x = "[" & z & "]" > y = DLookup(x, "tblSM_References") > z = z + 1 > Loop > etc. > Judy
> > Judy, > > I tried your suggestion and met with the same result. > > Any other ideas?
> > > Larry, > > > Dim x as String, y as Integer > > > x="1998" > > > do while x<"2000" > > > The rest of your code should then work. > > > J Seyfert
> > > > Ok, here is the problem > > > > I have a table that will always have ona, and only one record > > > > two of the field names are: > > > > 1998 > > > > 1999 > > > > what I want to do is lookup the value of field 1998 or 1998, based on > a > > > > variable. > > > > below is a sample of the code I am trying to use: > > > > dim x, y as integer > > > > x=1998 > > > > do while x < 2000 > > > > y = dlookup(x, "Ships") > > > > docmd.openquery "AppendYearTotals" > > > > x = x + 1 > > > > loop > > > > I've tried different syntaxes for x with no success > > > > if I use y=dlookup(x,"Ships"), Y is given the value of x (y=1998) > > > > same thing if I use [x] > > > > if I use "x" or "[x]", I get errors > > > > If I type the filed name in directly, ie y=dlookup("[1998]","Ships") > it > > > > works perfectly, (y=3), which is the value of the field. > > > > Can anyone tell me what syntax to use to make this work right? > > > > You will be a lifesaver. > > > > Thanks, > > > > Larry

Wed, 10 Jul 2002 03:00:00 GMT 


