format data type in dlookup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gmead7 - 08 Feb 2007 18:11 GMT

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

Reply to this Message

Svetlana - 08 Feb 2007 19:07 GMT

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

Reply to this Message

gmead7 - 08 Feb 2007 19:19 GMT

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

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

Reply to this Message

gmead7 - 08 Feb 2007 19:24 GMT

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

> 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
[quoted text clipped - 21 lines]
>
> Does anyone have any ideas? thanks, George

Reply to this Message