How to return a single value from a query into a formfield?

  • Thread starter Thread starter Mike @ XS4ALL
  • Start date Start date
M

Mike @ XS4ALL

Hello,

Using a form I can select 3 queries. These 3 all address the same table.
They read a field which holds a string,.Depending on the selected query a
different part of the field is read; I.e. my field holds a value like
AABBBCCCC, where the first query reads the AA part, the second the BBB and
the third the CCCC part (all parts have a different length) The queries all
read their own part and return the MAX value.

My question, how can I "capture" this value and return it in a text box on
my original form, or in a variable?

Any tips, tricks and techniques are highly appreciated!

Thnx,

Mike
 
Post the sql for the query you have written into the
Control Source property of the text box?

Jason
 
Use the following in the control source of the textbox:

=DLookup("[QueryFieldname]',"NameOfQuery")


--
PC Datasheet
A Resource for Access, Excel and Word Applications
(e-mail address removed)
www.pcdatasheet.com

· Design and basic development for new applications
· Additions, Modifications and "Fixes" for existing applications
· Mentoring for do-it-yourselfers who want guidance
· Complete application design and development
· Applications Using Palm Pilot To Collect Data And
Synchronize The Data Back To Access Or Excel
 
Hi Mike

The first tip/trick I have is to redesign your table so that AA, BBB, and
CCCC are each stored in separate fields. You are breaking one of the
fundamental laws of database design here, by storing multiple data in the
same field.

You can then return all three values in the one query:
Select Max(AA) as A, Max(BBB) as B, Max(CCCC) as C from YourTable

You then simply choose (by whatever criteria) whether to display A, B, or C.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.
 
Dear PC datasheet,

I'll give it a go!

Thank you,

Mike

PC Datasheet said:
Use the following in the control source of the textbox:

=DLookup("[QueryFieldname]',"NameOfQuery")


--
PC Datasheet
A Resource for Access, Excel and Word Applications
(e-mail address removed)
www.pcdatasheet.com

· Design and basic development for new applications
· Additions, Modifications and "Fixes" for existing applications
· Mentoring for do-it-yourselfers who want guidance
· Complete application design and development
· Applications Using Palm Pilot To Collect Data And
Synchronize The Data Back To Access Or Excel

Mike @ XS4ALL said:
Hello,

Using a form I can select 3 queries. These 3 all address the same table.
They read a field which holds a string,.Depending on the selected query a
different part of the field is read; I.e. my field holds a value like
AABBBCCCC, where the first query reads the AA part, the second the BBB and
the third the CCCC part (all parts have a different length) The queries all
read their own part and return the MAX value.

My question, how can I "capture" this value and return it in a text box on
my original form, or in a variable?

Any tips, tricks and techniques are highly appreciated!

Thnx,

Mike
Click to expand...
Click to expand...
 
This worked like a charm...
Thnx!


PC Datasheet said:
Use the following in the control source of the textbox:

=DLookup("[QueryFieldname]',"NameOfQuery")


--
PC Datasheet
A Resource for Access, Excel and Word Applications
(e-mail address removed)
www.pcdatasheet.com

· Design and basic development for new applications
· Additions, Modifications and "Fixes" for existing applications
· Mentoring for do-it-yourselfers who want guidance
· Complete application design and development
· Applications Using Palm Pilot To Collect Data And
Synchronize The Data Back To Access Or Excel

Mike @ XS4ALL said:
Hello,

Using a form I can select 3 queries. These 3 all address the same table.
They read a field which holds a string,.Depending on the selected query a
different part of the field is read; I.e. my field holds a value like
AABBBCCCC, where the first query reads the AA part, the second the BBB and
the third the CCCC part (all parts have a different length) The queries all
read their own part and return the MAX value.

My question, how can I "capture" this value and return it in a text box on
my original form, or in a variable?

Any tips, tricks and techniques are highly appreciated!

Thnx,

Mike
Click to expand...
Click to expand...
 
Back
Top