Refering Text Box Value to Field Name

[satyaprasad369]

hi,

I have a table with many fields. I want user to select field based on text box value.

Eg: if user enters "Diameter", the output table should contain only "Diameter" field.

Can some one help me regarding this.

My end goal is to do Avg, Min, Max and SD on that pirticular field what user wants and output those values to a form to display.

Thank you.
Satya.

 

[Shallo9]

Can you elaborate your question? Where would the user enters the diameter are there any forms involved? If yes is that form a Single form or Datasheet form?

If you could upload the db, we can look into it.

 

[satyaprasad369]

Thank you for responding.

If user enters "diameter" in text box then press Buton in the form, following should happen.

1. look in the table for the field name as "Diameter"
2. Do avg, min, max and SD on that field
3. Output those values to a form which displays Avg, Min, Max and SD of that pirticular field.

Eg:
SN Diameter Length Height .....are field names
1 20 15 12
2 25 30 15
4 35 30 18
......

This is an similar example. My database has about 100 columns on which I need to search. Sorry, I could not upload my access file as it is confidential.

The porblem I am facing is to find a way to find the field that matches text box value ("Diameter").

 

[Shallo9]

Try the below code...


MsgBox DSum(Textboxname.Value, "TableName")

MsgBox DMax(Textboxname.Value, "TableName")

MsgBox DMin(Textboxname.Value, "TableName")

MsgBox DAvg(Textboxname.Value, "TableName")

MsgBox DStDev(Textboxname.Value, "TableName")

Good Luck

 

[satyaprasad369]

This is the expression I have used as you suggested. I have used this expression in control source as I am not using programming for my project.

=DAvg([Stepno].[Value],"[k data]")

Aim is to compute average for field name as "100" in table "K data". Depending on the text box "Stepno".

Result:

1. I did not assign "[ ]" to stepno or value but access is do it automatically.
2. Output in the text box is same as input to textbox "stepno".
eg: I entered "100" in text box "Stepno" and my output was "100", entered "220"(another field name) output is "220" which is not desired.

Can you tell me whats going on.

Both my text boxes are General Number format.

 

[Shallo9]

eg: I entered "100" in text box "Stepno" and my output was "100", entered "220"(another field name) output is "220" which is not desired.

Can you tell me whats going on.

Both my text boxes are General Number format.

I am bit confused with the above info. Are you sure you've named the Columns as 100 & 220 or you referring to the values...?

However please look at this example n see if this is what you meant.

 

[satyaprasad369]

Thank you.
I will try it on my data and let you know. This might solve my problem.

 

[missinglinq]

This is the expression I have used...

=DAvg([Stepno].[Value],"[k data]")

Aim is to compute average for field name as "100" in table "K data". Depending on the text box "Stepno".

Shallo9 is a bit up on me, only being a "bit confused!" Your line

Aim is to compute average for field name as "100" in table "K data". Depending on the text box "Stepno".

makes absolutely no sense, as written.

If you want the Average of the Stepno field in all Records, the syntax would be

=DAvg("[Stepno]","[k data]")

Syntax would be different, requiring a Where Clause, if you only want to Average the Stepno field in some of the Records.

You simply have to explain the above highlighted line better in order for us to help you!

Linq ;0)>

 

[satyaprasad369]

Thank you Shallo9. Your code worked.

But I made one change in my table. I renamed all my field names from "100","220",.....to "a100","a220",...... I think Access has problem with field names starting with numbers. After the change it worked.

Is it possible to achieve the same result without changing field names, as present names are standard field names for my database.

Thank you.

 

[Shallo9]

Wonder why not. Some people argue and recommend to use square brackets around [] but there is no evidence if thats not gonna fail in a long run. However the Naming Convention may help you to decide whats best.

 

[missinglinq]

The Access VBA Gnomes don't like names that start with a Digit nor ones that have Spaces.

If you name a Control 200, then go into the code module to write some event code, such as an AfterUpdate event, Access will generate the Sub Header as

Private Sub Ctl220_AfterUpdate()

In code you can refer to the Control as Ctl200 or [200], but in the Sub Header it has to be referred to as Ctl200.


Likewise, if you name a Control Last Name, with a Space between Last and Name, then go into the code module, once again, Access will now generate the Sub Header as

Private Sub Last_Name_AfterUpdate()

with the UnderScore between the Last and Name.

Again, you can refer to the Control in code using either [Last Name] or Last_Name, but in the Sub Header it has to be referred to as Last_Name!

Simple, isn't it? Not! It's most confusing, and the Access Gnomes find it to be confusing, as well, and sometimes fail to recognize the name for what it is, even when they should!

And just to add to the confusion, there are times, within Access, when you have to enclose a name within Square Brackets, regardless of how it's formatted! A simple name like MissingLinq, with no Spaces and no leading Digits, has to be entered as [MissingLinq] if it is used in an Expression in Conditional Formatting or in a Properties Box, such as a Default Value or a Control Source.

Its just makes sense to use a naming convention that doesn't include Spaces nor leading Digits! But if you absolute, positively have to keep you 100 and 220, etc, remember the above rules! And I have no idea about how they'll run in a SQL Statement
!
Linq ;0)>