format data type in dlookup

[gmead7]

I have been trying to get this to work and keep getting a data type mismatch
error. The field,"FY_Estimated_PP" is a number - long integer in the
tblProportions table. I suspect the problem is that data in the form is text
and the field used as a criterion is long integer.

Start code:

Private Sub Form_Current()
Dim FYportion
FYportion = DLookup("PayPortion", "tblProportions", "FY_Estimated_PP = '" &
Me!FY_Estimated_PP & "'")
MsgBox FYportion
End Sub


I have tried to use the Format function such as below (and have tried a
multitude of variations)

Private Sub Form_Current()
Dim FYportion
FYportion = DLookup("PayPortion", "tblProportions", "FY_Estimated_PP = '" &
Format(Me!FY_Estimated_PP,'Long') & "'")
MsgBox FYportion
End Sub


Does anyone have any ideas? thanks, George

 

[Svetlana]

If its a number then you dont need the quotes.
FYportion = DLookup("PayPortion", "tblProportions", "FY_Estimated_PP =
" & Me!FY_Estimated_PP)

 

[gmead7]

Svetlana,

Thank you! Its amazing how something so simple can make you so crazy!

I'm also wondering what the & (ampersand) is for and why at some times you
need it on both ends and in this case you only need it at the beginning.

thanks again,

George

 

[gmead7]

Please disregard my additional question - the answer is something I should
have known or figured out. It is apparently used for concatenation purposes.
In this case concatenating the FY_Estimated_PP = portion to the form element
Me!FY_Estimated_PP. I see know that when text is involved you need to also
concatenate the quotations around the text thus you add the single quote (')
so that the double quotes (") are carried with the text string.

thanks again, George