Dloookup with Table name is from a textBox (1 Viewer)

nashfaq

Registered User.
Local time
Today, 09:35
Joined
Oct 17, 2018
Messages
28
Hello Team

i want to get the values from two tables.

both table have same filed names but different values.

i want to choose the table name via a textbox that have a list of table.

=DLookUp([Bdgt Aam_Amd],[tbl_FileName]![FileName],"
Code:
 = """ & [txtCode] & """")

i am getting #Name? 

please help how to write the exact command

regards
Nasir
 

missinglinq

AWF VIP
Local time
Today, 01:35
Joined
Jun 20, 2003
Messages
6,423
As Dave Letterman used to say..."What???"

...both table have same filed names but different values...

Two Tables having the same Fields makes no sense...it violates Normalization!

...i want to choose the table name via a textbox that have a list of table...
How can a single Textbox 'have a list of table?'

In order for us to help you, you need to give us a plain language explanation of what you're trying to do...not how you're trying to do it.

Linq ;0)>
 

nashfaq

Registered User.
Local time
Today, 09:35
Joined
Oct 17, 2018
Messages
28
I have two tables... 1 for year 2017 and other for year 2018.
Both tables have same fields.

Now I have a form that looks up data
Now I have two table..
So I want that
Dlookup (expression, table name, criteria)

I want to use a combo box... To select the table name I want to look up.

Plz help how to write the Dlookup command

Regards
Nasir
 

plog

Banishment Pending
Local time
Today, 00:35
Joined
May 11, 2011
Messages
11,597
I have two tables... 1 for year 2017 and other for year 2018.

missingling nailed it--you set up your tables incorrectly. Thought experiment---why not have a table for each code?

tbl_Budget2016CodeA
tbl_Budget2016CodeB
tbl_Budget2016CodeC
....
tbl_Budget2017CodeA
tbl_Budget2017CodeB
...
tbl_Budget2018CodeA
tbl_Budget2018CodeB

What is so special about your codes that you made a field for them instead of storing them in your table name?

You don't store values in names in a database. When you have a new table for each year that's exactly what you are doing and its wrong. Instead you make a [DataYear] field in your one table to identify which records are for which year and you store all your data in that one table.

tbl_Budget

Then, when you want to lookup data from a particular year, you use that year as criteria in the Dlookup:


=DLookUp([Bdgt Aam_Amd],[tbl_Budget],"[DataYear]=" & [txtYear] & " AND
Code:
 = """ & [txtCode] & """")
 

nashfaq

Registered User.
Local time
Today, 09:35
Joined
Oct 17, 2018
Messages
28
=DLookUp([Bdgt Aam_Amd said:
,[tbl_Budget],"[DataYear]=" & [txtYear] & " AND
Code:
 = """ & [txtCode] & """")[/QUOTE]

i moved all the data makred with dataYear.
now i have data for 2017 and 2018. in one table.


my txtYear is :=Year(DLookUp("YearStartMK","tbl_FinancialYear"))

Now i am still getting error #Name?

please help.
 

plog

Banishment Pending
Local time
Today, 00:35
Joined
May 11, 2011
Messages
11,597
You can't just build things on top of things on top of things then post back here when you get an error. Find out where that error is occuring. Here's what your code pyramid looks like right now

DLookup(Bdgt_Aam_Amd) -- which relies on--
-- txtYear -- which relies on --
-- -- Year() -- which relies on --
-- -- -- Dlookup(YearStartMK)

So start at the bottom and see if that DLookup(YearStartMK) actaully works and returns something. If it does, make sure that Year function works then keep working up the pyramid until you are at the top and everything works.
 

nashfaq

Registered User.
Local time
Today, 09:35
Joined
Oct 17, 2018
Messages
28
Hi

as per your suggestion, i test as below

=Year(DLookUp("YearStartMK","tbl_FinancialYear")) works perfect give me 2018.

=DLookUp([Bdgt Aam_Amd],[tbl_Budgets17-18]," [BudgetYear] = '2018' ")
gives error #Name?

my tbl_Budgets17-18, field BudgetYear is text filed having data 2017 and 2018

i tried

=DLookUp([Bdgt Aam_Amd],[tbl_Budgets17-18]," [BudgetYear] = 2018")
but still have same error

Now Please suggest.

whats going wrong in
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:35
Joined
Sep 21, 2011
Messages
13,964
Why do you use " for the first DLlookup for field and source, but not for the other?
 

nashfaq

Registered User.
Local time
Today, 09:35
Joined
Oct 17, 2018
Messages
28
Why do you use " for the first DLlookup for field and source, but not for the other?

sorry didnt get you..

Dear I am not very experience programmer...
please help me out
 

nashfaq

Registered User.
Local time
Today, 09:35
Joined
Oct 17, 2018
Messages
28
hello

i have tried this
=DLookUp("[Bdgt Aam_Amd]","tbl_Budgets17-18","[BudgetYear]= '2018'" And "
Code:
 =  """ & [txtCode] & """")

but when my code changes, data is not changing...

please suggest.
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:35
Joined
Sep 21, 2011
Messages
13,964
What does

Code:
=DLookUp("[Bdgt Aam_Amd]","[tbl_Budgets17-18]"," [BudgetYear] = 2018")

give you?
 

nashfaq

Registered User.
Local time
Today, 09:35
Joined
Oct 17, 2018
Messages
28
What does

Code:
=DLookUp("[Bdgt Aam_Amd]","[tbl_Budgets17-18]"," [BudgetYear] = 2018")

give you?

if use it, it give me the value for year 2018, even if i change it to 2017 it give value for year 2017.

But if i use the below code the Result is not changing as the code changes

=DLookUp("nz( [Bdgt Aam_Amd],0)","tbl_Budgets17-18","[BudgetYear] =2018" And "
Code:
 = """ & [txtCode] & """")

please suggest.
note in my table year field is NUMBER
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:35
Joined
Sep 21, 2011
Messages
13,964
Well for a start if you want to change the possibility of getting a null value from the dlookup and replacing it with 0, you need to surround the whole dlookup with the Nz function.?

Let's get one part at a time working. Have you even got the names correct, as you have underscores in some places and not in others? Bdgt Aam_Amd
Code:
=DLookUp([Bdgt Aam_Amd]","tbl_Budgets17-18","BudgetYear = 2018 & " And [yourcode] = '" & [txtCode] & "'")

I have had to change your code field name to yourcode as it inteferes with the code tags on the site.
 

nashfaq

Registered User.
Local time
Today, 09:35
Joined
Oct 17, 2018
Messages
28
Let's get one part at a time working. Have you even got the names correct said:
=DLookUp([Bdgt Aam_Amd]","tbl_Budgets17-18","BudgetYear = 2018 & " And [yourcode] = '" & [txtCode] & "'")
[/code]

I have had to change your code field name to yourcode as it inteferes with the code tags on the site.

Friend
DLookUp([Bdgt Aam_Amd]","tbl_Budgets17-18", [yourcode] = '" & [txtCode] & "'")

this works fine. and i a,m getting proper data.
underscores are in the field name, and not have any issue.

when i add one more criteria , getting only one result, that is on the top of my table.
criteria for Year is not working
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:35
Joined
Sep 21, 2011
Messages
13,964
You keep changing the Dlookup criteria :banghead:

You do not even have the Year in there now.:confused:

Please the supply the whole Dlookup as YOU ARE USING IT NOW

Also supply what it returns.

If it is now working, then it is your data at fault. It will only return the first entry it finds that matches your criteria.?
 

nashfaq

Registered User.
Local time
Today, 09:35
Joined
Oct 17, 2018
Messages
28
i am not changing the criteria.

i am explaing you that with only one criteria my dlookup works fine.

however, i found a solution, i make a querry and for year i used the TextBox, in the criteria of the querry,

then i use that query in the dlookup i was facing problem.

now querry filter data with respect to the year text box, and dlookup get values for the form.

i dont know is this a good approach or not...but it works for me.

can you help me, with the first condition

=DLookUp([Bdgt Aam_Amd
,[tbl_Budget],"[DataYear]=" & [txtYear] & " AND
Code:
 = """ & [txtCode] & """")

i am getting error on it.

please give me the right code
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:35
Joined
Sep 21, 2011
Messages
13,964
You can use as many criteria as you wish I believe, you just have to get the syntax correct.

What error are you getting as what you posted there is incorrect, no closing ] for the first parameter.?
 

nashfaq

Registered User.
Local time
Today, 09:35
Joined
Oct 17, 2018
Messages
28
If I use "budgetYear =' 2018 '" in criteria
So it only get the value for one person.
Even when I change the code from combo box
Value not changes.

If I use "budgetyear" = &[txtbox]&

It gives #Name?

Regards
Nasir
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:35
Joined
Sep 21, 2011
Messages
13,964
If I use "budgetYear =' 2018 '" in criteria
So it only get the value for one person.
Even when I change the code from combo box
Value not changes.

If I use "budgetyear" = &[txtbox]&

It gives #Name?

Regards
Nasir

You are making the budgetyear a text doing what you are doing there.

Use
"budgetyear = " & [txtbox]
 

Users who are viewing this thread

Top Bottom