Syntax Error Missing Operator (1 Viewer)

[jackh113]

I am attempting to perform a Dlookup to populate an unbound control with the following command:

Me.txtad_Ship.Value = DLookup("[ad_Ship]", "[Tanks]", "[Job_ID] = " & Me.Job_ID)

For some of the records, I get an error message of "Syntax error (missing operator) in query experssion '[Job_ID] ='."

The crazy part that I don't get is that this command works for most of the records in the same form and the field [Job_ID] contains the correct ID#. I've also checked the table and it contains the appropriate value. I've also tried fetching the field with a sql Select statement with the same results.

I just don't get it. Why does it work on most records but not a select few? Any help would be greatly appreciated!!

 

[arnelgp]

Me.txtad_Ship.Value = DLookup("[ad_Ship]", "[Tanks]", "[Job_ID] = " & Nz(Me.Job_ID,0))

 

[jackh113]

Thank you for the replay.

While this would avoid the error, it would defeat my purpose as I still wouldn't fetch the value. The field [Job_ID] contains a value. I can see it. For some reason it doesn't pull into the expression for some records.

 

[plog]

In the datasource of the form, what data type is Job_ID? In Tanks, what is the data type of Job_ID?

 

[jackh113]

Tanks is a table. The [Job_ID] is an Autonumber, Long Interger.

The datasource for the form is a query. Sorry, but don't know how to check the datatype in a query?

 

[plog]

In a query you have to see how the cell justifies. Do the values align to the right or left when you run the query?

Sounds like they may be treated like text and you might have a non-numeric value apearing in Job_ID in the query. Can you sort Job_ID in the query Ascending and tell me what the first value is? The sort descending and tell me what the first value is.

 

[jackh113]

Everything is right justified in the query. This indicates numeric, correct?

Sorted ascending, the first value is 1.
Sorted descending, the last value is 1620.

Looks like the data type is numeric in both the table and query, correct?

 

[plog]

Correct to both questions. I don't know what to tell you.

My gut tells me has something to do with a specific value of Job_ID. Find the value causing the error and research that value. Sorry I can't help more than that.

 

[jackh113]

That's my thought too but wasn't really sure where to start with it. It only does it with select records. Just wasn't sure where to start looking. I have the same expression in another Form that seems to be working fine. May force one of the offending records to appear in that Form and see what happens.

Thanks for your help.