using a variable to specify field name in dlookup function 
Author Message
 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  
 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  
 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  
 using a variable to specify field name in dlookup function
Sorry--Try 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  
 using a variable to specify field name in dlookup function
Thank you, Judy,

You are truly a lifesaver.

Larry


Quote:
> Sorry--Try 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  
 
 [ 5 post ] 

 Relevant Pages 

1. Using Variables for Field Names using .AddNew and .Update

2. #Name error showing up on dlookup fields in my reports

3. use of variables in DLookup functions

4. Using variables for field names in recordset

5. Using variables instead of field names

6. referencing field names using a variable

7. Call Function using Func Name stored in variable

8. Using variables for field names in VBA

9. Field Names using variables

10. Repost Code Example - using variables in field name references

11. Repost Code Example - using variables in field name references

12. Adding a record using the field names as a variable

 

 
Powered by phpBB® Forum Software