Greeting,

I have the following code to lookup for two values in table and put them in
two controls in subform as follows:


Private Sub RevenueID_AfterUpdate()
Me.Revenue = DLookup("[Revenue]", "[Revenue]", "[ID]='" &
[Forms]!Sales!Sales_subform![RevenueID] & "'")
Me.Price = DLookup("[Price]", "[Revenue]", "[ID]='" &
[Forms]!Sales!Sales_subform![RevenueID] & "'")

The problem is , once I select the Revenue number from the RevenueID Combo
box the following msg appears:

Run-time erro’3464’:
Data type mismatech in critieria expression.


Any help please

If you open the Revenue table in design view, what data type is the ID
field?

If Number (including AutoNumber), not Text, lose the extra quotes:

Me.Revenue = DLookup("[Revenue]", "[Revenue]", "[ID]=" &
Nz([Forms]!Sales!Sales_subform.Form![RevenueID],0))

For an explanation of DLookup(), see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

For an explanation of the .Form bit, see:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

The Nz() is to prevent the error if the RevenueID in the subform is null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ghost" <ghost@discussions.microsoft.com> wrote in message
news:97267B07-F275-475C-B2FF-2B88DAC1E8F8@microsoft.com...
> Greeting,
>
> I have the following code to lookup for two values in table and put them
> in
> two controls in subform as follows:
>
>
> Private Sub RevenueID_AfterUpdate()
> Me.Revenue = DLookup("[Revenue]", "[Revenue]", "[ID]='" &
> [Forms]!Sales!Sales_subform![RevenueID] & "'")
> Me.Price = DLookup("[Price]", "[Revenue]", "[ID]='" &
> [Forms]!Sales!Sales_subform![RevenueID] & "'")
>
> The problem is , once I select the Revenue number from the RevenueID Combo
> box the following msg appears:
>
> Run-time erro’3464’:
> Data type mismatech in critieria expression.
>
>
> Any help please
>

It works, many thanks!!

"Allen Browne" wrote:

> If you open the Revenue table in design view, what data type is the ID
> field?
>
> If Number (including AutoNumber), not Text, lose the extra quotes:
>
> Me.Revenue = DLookup("[Revenue]", "[Revenue]", "[ID]=" &
> Nz([Forms]!Sales!Sales_subform.Form![RevenueID],0))
>
> For an explanation of DLookup(), see:
> Getting a value from a table: DLookup()
> at:
> http://allenbrowne.com/casu-07.html
>
> For an explanation of the .Form bit, see:
> Referring to Controls on a Subform
> at:
> http://allenbrowne.com/casu-04.html
>
> The Nz() is to prevent the error if the RevenueID in the subform is null.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "ghost" <ghost@discussions.microsoft.com> wrote in message
> news:97267B07-F275-475C-B2FF-2B88DAC1E8F8@microsoft.com...
> > Greeting,
> >
> > I have the following code to lookup for two values in table and put them
> > in
> > two controls in subform as follows:
> >
> >
> > Private Sub RevenueID_AfterUpdate()
> > Me.Revenue = DLookup("[Revenue]", "[Revenue]", "[ID]='" &
> > [Forms]!Sales!Sales_subform![RevenueID] & "'")
> > Me.Price = DLookup("[Price]", "[Revenue]", "[ID]='" &
> > [Forms]!Sales!Sales_subform![RevenueID] & "'")
> >
> > The problem is , once I select the Revenue number from the RevenueID Combo
> > box the following msg appears:
> >
> > Run-time erro’3464’:
> > Data type mismatech in critieria expression.
> >
> >
> > Any help please
> >
>
>