Dlookup Help

[samjh]

Hi,

I am trying put a default value into a form (note this may be changed by the user at a later date) the default value is the value from the commitment (which is another form) divided by 2.

Below is what I have used in the VBA, but it just isn't working, I keep getting Run-time error '13': Type Mismatch. I don't know what the mismatch is, as the CommitmentID is an autonumber within the original Commitment Table, the subfrmComms2 is based on a query which was based on the original Commitment Table but has the calculation for Contracted Rate Per Year in it. the only thing i can think of that could be the mismatch is the original CommitmentID has a default to 4 digits i.e.0087. and i notice on my fmr income health it is coming through as 87. I don't know how to fix this though.

Private Sub Form_Current()
Me.HealthIncome = DLookup("Contracted Rate Per Year" / 2, "subfrmComms2", "CommitmentID = " & Me.CommitmentID)
End Sub


thanks

 

[edtab]

Try:

Private Sub Form_Current()
Me.HealthIncome = DLookup("[Contracted Rate Per Year]" / 2, "subfrmComms2", "CommitmentID = " & Me.CommitmentID)
End Sub

 

[samjh]

I have tried changing it but it still doesn't work.

 

[pr2-eugin]

DLookUp involves three arguments First is the FieldName, Second is the TableName and finally (optional) Criteria..
You have placed a /2 just after the FieldName whihc is not right.. You should first return a value then use that to divide by 2..See the code below..

Private Sub Form_Current()
    Me.HealthIncome = Nz(DLookup("[Contracted Rate Per Year]", "subfrmComms2", "CommitmentID = " & Me.CommitmentID),0)[COLOR=Blue]/ 2[/COLOR]
End Sub

Always use Nz() for DLookUp, this is to avoid the Invalid Use of Null runtime error..

 

[samjh]

I've got it working, thank you.

 

[pr2-eugin]

Glad to hear..