dlookup question

[VicBaker]

I'm trying to use dlookup to compare a value from a field in my data table with several values stored in a field in my lookup table and when they match, populate an empty field in my data table with the value from the corresponding field in the lookup table.

"Socvb"=DLookUp("[SOC code]","[SOC- Position Code Link Table]","[Unduplicated Employee Records].Position = """ & [SOC- Position Code Link Table].[Position] & """")

where socvb is my empty field in data table Unduplicated employee records
soc code in the soc position code link table is the field that contains the value I want to grab
Position is the field that is common between soc position code link table and unduplicated employee records.

The preceding code wants me to manually enter a value for "SOC- Position Code Link Table.Position" -- I want the dlookup to find that information and automatically enter it.

What am I doing wrong? How do I fix it?

Thanks much!!

V

[RuralGuy]

"Socvb" is not usable syntax. I would suggest you store the value in a variable 1st and then to your table.

[VicBaker]

I'm using the dlookup in the criteria of the field definition.

I get the same result if I omit the "socvb=" piece.

How do I assign the function result to a variable? Do I need to do that in the SQL?

Thanks again,

V

[RuralGuy]

Try:

Dim MyVariable
MyVariable = DLookUp("[SOC code]","[SOC- Position Code Link Table]","[Unduplicated Employee Records].Position = """ & [SOC- Position Code Link Table].[Position] & """")

[VicBaker]

Hi RG--
Thanks much for your prompt help! I cut and pasted your code into the SQL view so it now looks like this:

SELECT [Unduplicated Employee Records].EMPLID, [Unduplicated Employee Records].[EMPL RCD], [Unduplicated Employee Records].[BANNER ID], [Unduplicated Employee Records].[FIRST NAME], [Unduplicated Employee Records].MIDDLE, [Unduplicated Employee Records].[LAST NAME], [Unduplicated Employee Records].socvb, [Unduplicated Employee Records].Position, [Unduplicated Employee Records].Position_Descrip
FROM [Unduplicated Employee Records];
Dim MyVariable
MyVariable = DLookUp("[SOC code]","[SOC- Position Code Link Table]","[Unduplicated Employee Records].Position = """ & [SOC- Position Code Link Table].[Position] & """")

and I get a "characters found after end of sql statement error"

Where should I put this code? and, next step, how do I move the data from myvariable to [unduplicated employee records].socvb in the right place?

No hurry on this--I'll be back to my desk in about 90 minutes.

Thanking you much!

V

[RuralGuy]

It does not go in your SQL at all. Maybe you need to give me a bigger picture of how you are going about this task.

[VicBaker]

Hi RG--
First THANK YOU! I have fairly strong BASIC Access skills, but I haven't used it in 10 years. I have to use the databases I was given because this is only a temporary position for me.

anyway.

I removed the "" from the socvb in my first example of the dlookup function, (based on your "not usable syntax") which I typed into the "criteria" portion of my socvb field definition. I ran the query, and got the request to enter "SOC- Position Code Link Table.Position" when I clicked on OK, I get a message "the expression you entered as a query parameter produced this error: 'Microsoft access cannot find the name 'Unduplicated Employee Records.Position' you entered in the expression.
And here's the curious things about this: when I hit ok, it puts me in an empty table (before, it put me back into design view of my query) AND the dlookup function in the criteria box now has single quotes around it. I tried this twice, removing those quotes, and got the same sequence both times...

Now, for the more specifics you requested.

I have a (data) table full of employee data that contains a field "position" with data in it, and a field "socvb" which is empty. I have a (lookup) table that has the position field and a soc field. I need to get the soc field data from the lookup table into the socvb field based on matching the values of the two position codes.

In simple flow, I want to
read employee record
read lookup record
if position(employee record) = position(lookup record)
then move soc(lookup record) to socvb(employee record) and go back to read the next employee record
else read next lookup record and repeat until end-of-table
at end-of-table go back to read the next employee record

Hoping that clarifies what I think I'm trying to do...

Thanks again!

V

[RuralGuy]

Can't you create a query that joins the two tables on those two fields?

[VicBaker]

ARGHHH. Tried that already before going the Query/SQL route.
No.
Thanks anyway.
Have a great weekend--maybe I'll have a brainstorm and this will all become clear to me.