DLookup

[ZetecS]

Hi all ,

been on this bit of coding all morning and have had little success. I am working on a form and within that form i am trying to pull through the value of a field from the previous record into the present one and do a calculation on it. I have played around a bit and have had it working well sort of.

The code is as follows:

Me![name of field the calculation will appear in] = DLookup("[name of field in previous form]", "[table name]", "[ID]=" & Forms![form name]![ID] - 1) + [field that is need for calculation]

The error message being displayed is:

Run-time error '3075':

Syntax error (missing operator) in query expression
'ID ='.

Any one have any ideas?

Cheers

 

[Galaxiom]

I think it might be confusing the ID control on the form with the ID field in the table.
Fully specify the left side of the condition as in:
.... "[tablename]![ID]=" ....

Also maybe add parentheses around the right side of the condition:
.... & (Forms![form name]![ID] - 1) ....

 

[ZetecS]

Thank you for the reply Galaxiom I have done as you have described above but another error now exists. Don't you just love VBA :/

The error now being displayed is:

Run-time error '3078'

The Microsoft jet engine cannot find the input table or query '[tablename]![ID]=7'. Make sure it exists and that its name is spelt correctly.

I know for a fact that they are spelt correctly so I am slightly bewildered as to how to fix this

Cheers

 

[Galaxiom]

I have had corrupted queries where Access keeps asking for objects that are not even in the query. This is the opposite problem but perhaps they are both the result of corruption. The actual query that is sent to Jet is not what the sql shows but some behind the scene representation that can get messed up despite perfectly formed sql.

I have fixed my problems every time by pasting the sql into a new query. It might be worth trying that.