lookup for 2 values code

[ghost]

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

 

[Allen Browne]

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.

 

[ghost]

It works, many thanks!!

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

Reply to group, rather than allenbrowne at mvps dot org.

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