DLookup Doesn't Return Correct Value

[GendoPose]

Hi All,

I'm trying to use a DLookup to get a specific value from a field in a table.

This is what my code currently looks like;

JOBID = DLookup("[ID]", "MASTER PLANNER", "[JOB NUMBER] = '" & JOB_NUMBER & "'")

Basically I want to get the ID (a number) from a specific record where the JOB NUMBER equals the string I have typed in to a field on the form, also called JOB NUMBER.

However, my problem is that it doesn't navigate to the record where the criteria matches, it just chooses the ID from the very first record of the table.

Anyone know what I'm doing wrong?

Thanks all!

 

[Gasman]

If the job_number is a number then you only need ?

"[JOB NUMBER] = " & JOB_NUMBER )

HTH

 

[GendoPose]

If the job_number is a number then you only need ?

"[JOB NUMBER] = " & JOB_NUMBER )

HTH

See this is where it gets annoying, it's actually a string i.e "S019191-1", not just a number...

 

[Minty]

This is what my code currently looks like;

JOBID = DLookup("[ID]", "MASTER PLANNER", "[JOB NUMBER] = '" & JOB_NUMBER & "'")

Assuming Job_Number is the name of the control on the form then you need to refer to it as Me.Job_Number

It is good practice to not accept the defaults for control names on forms, try call the control txtJob_Number then you know you are referring to the control and not a field name.

 

[GendoPose]

Assuming Job_Number is the name of the control on the form then you need to refer to it as Me.Job_Number

It is good practice to not accept the defaults for control names on forms, try call the control txtJob_Number then you know you are referring to the control and not a field name.

Tried that earlier, no luck...

I've also tried creating a separate string to get the value of the text box before running the DLookup, but I get the same results.

 

[Minty]

So to clarify Job_Number is definitely the name of the control on the form, NOT the field name?

And what did you do here - can you post the code you tried?

I've also tried creating a separate string to get the value of the text box before running the DLookup, but I get the same results.

 

[GendoPose]

So to clarify Job_Number is definitely the name of the control on the form, NOT the field name?

And what did you do here - can you post the code you tried?

Yeah definitely.

ID is a numerical ID field, [JOB NUMBER] is a string (like this; S019191-1) and JOB_NUMBER is the field on the form.

The code I have using a variable is this;

Dim JOBNUM As String
    JOBNUM = Me.JOB_NUMBER
    MsgBox ("" & JOBNUM & "")
    JOBID = DLookup("[ID]", "[MASTER PLANNER]", "[JOB NUMBER] = '" & Me.JOB_NUMBER & "'")
    MsgBox ("" & JOBID & "")

The first msgbox shows the correct value of the textbox, the second msgbox is what should be showing the ID number of that record, but instead it shows the ID of the first record in the table.

 

[CJ_London]

it might be because your table is called Master Planner - if it is you need to use square brackets. But better to not use spaces at all - if you need them for fields, use the caption property

update:I see you've corrected for that

 

[GendoPose]

Ignore me, turns out I can't spell.......

The field in the table is actually called [JOB NO], not [JOB NUMBER] as was written in the code.....

Herpa derp.

 

[Minty]

Okay what if you run a query using the same criteria in the query design window. Something somewhere isn't quite what you think by the look of it.