Problem with DLookUp

[Nausheen]

Hi,

I'm creating a database where a user selects certain parts of a financial report and views the data...

ie. on page 10010503 is a value for say assets.
The first 2 digits represent a page, second set a subpage, third set a line number and fourth set a column number.

I have 4 different list boxes for a user to select each section and I want the data value to be displayed in a textbox at the push of a command button.

This is what I have:

TextInfo.Value = DLookup("[NumVal]", "dbo_NumData", "[DP] = Forms![Main]![Insurer]Forms![Main]![Subpage]Forms![Main]![line]Forms![Main]![Column]")

So it should print the field NumVal from the table dbo_NumData, if the field DP matches hte collaboration of the listbox selections.

Unfortunately, this does not work...in the immediate window when I type:
? Forms![Main]![Insurer]Forms![Main]![Subpage]Forms![Main]![line]Forms![Main]![Column]

it gives me exactly what i am looking for...any ideas at anything i should change or if i can achieve my goal in another manner?

Thank you...

 

[vpelleri]

Maybe try simplifying the DLookup expression by setting your 4 combobox entries as a concatenated string.
*********
Dim strDP As String
strDP = Me.lstbox1.Value & Me.lstbox2.Value & Me.lstbox3.Value & Me.lstbox4.Value
*********
Then set your DLookup criteria using the string, strDP.
If the field in your table is not a string but an integer then you might want to consider changing the data type to string. A string data type seems better because of the nature of your field (a combination of several values).

Vince

 

[Nausheen]

Thank you for your reply - unfortunately I cannot change the field to a string - my table is a linked table to a database created by someone in another office.

That individual is off the project now and I cannot get permissions to change the data type.
Is there any other way I can accomplish this?

 

[randy700]

Try something like this...

TextInfo.Value = DLookup("[NumVal]", "dbo_NumData", "[DP] = " & Forms!Main!Insurer & "'")

Randy

 

[PHV]

Have you tried this ?
TextInfo.Value = DLookup("[NumVal]", "dbo_NumData", "[DP] = Forms![Main]![Insurer]*10^6 + Forms![Main]![Subpage]*10^4 + Forms![Main]![line]*100 + Forms![Main]![Column]")


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244