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

[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

 

[PC Datasheet]

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

 

[Graham Mandeno]

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.

 

[Mike @ XS4ALL]

Dear PC datasheet,

I'll give it a go!

Thank you,

Mike

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

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

 

[Mike @ XS4ALL]

This worked like a charm...
Thnx!

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

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