Select Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi ALL,
please help How can I capture result of this SQL in my Textbox(txt1.value):

SELECT Max([Seventh]+1) AS Expr1
FROM TBL7 INNER JOIN country ON TBL7.Country = country.[COUNTRY-NAME]
GROUP BY country.CCODE
HAVING (((country.CCODE) Like [Forms]![Frmchange7]![txt5]));

any help would be appreciate
 
I would create a group by query with the two tables and use DMax().
=DMax("[Seventh]+1"," Your group by query","CCODE Like '" &
[Forms]![Frmchange7]![txt5] & "'")
If the text box is on FrmChange7 then you don't need Forms!Frmchange7.
 
How are you plan to do this? I mean are you thinking of using VBA to set a
field value or do you simply want a reference value in a box as a default-
type setting? It sounds like you want the latter so this might work:

Create a query like the one you have without the aggregate function Max().
Then set the value of the textbox to =DMax("Expr1","NewQuery").
Hi ALL,
please help How can I capture result of this SQL in my Textbox(txt1.value):

SELECT Max([Seventh]+1) AS Expr1
FROM TBL7 INNER JOIN country ON TBL7.Country = country.[COUNTRY-NAME]
GROUP BY country.CCODE
HAVING (((country.CCODE) Like [Forms]![Frmchange7]![txt5]));

any help would be appreciate
Click to expand...
 
Hi Duane
Thank you for code ,I replace your recommend to an event click a command
button:
Me.txt1.Value = DMax("[Seventh]+1", "country.CCODE", "CCODE Like '" & _
[Forms]![Frmchange7]![txt5] & "'")
But I received Error 3078 means can't find GROUP BY "country.CCODE"
As my select query I have a key field named [Seventh] in TBL7 with samples
like:
1011911
1015476
3028839
5634749
and I also have Left([Seventh],3) in TXT5 as criteria (for example 101)
What I want to do is that , with click a button(cmd1) find maximum
[(101....)+1] and
display result(For exaple it must be 1015477) in a text box (TXT1)
All controls are in a Form named Frmchange7
Thank you again
Bijan

Duane Hookom said:
I would create a group by query with the two tables and use DMax().
=DMax("[Seventh]+1"," Your group by query","CCODE Like '" &
[Forms]![Frmchange7]![txt5] & "'")
If the text box is on FrmChange7 then you don't need Forms!Frmchange7.

--
Duane Hookom
Microsoft Access MVP


bijan said:
Hi ALL,
please help How can I capture result of this SQL in my Textbox(txt1.value):

SELECT Max([Seventh]+1) AS Expr1
FROM TBL7 INNER JOIN country ON TBL7.Country = country.[COUNTRY-NAME]
GROUP BY country.CCODE
HAVING (((country.CCODE) Like [Forms]![Frmchange7]![txt5]));

any help would be appreciate
Click to expand...
Click to expand...
 
Check Help on the proper syntax for using DMax(). Did you create a query like
I suggested? You need this query in your DMax() as the 2nd argument.

--
Duane Hookom
Microsoft Access MVP


bijan said:
Hi Duane
Thank you for code ,I replace your recommend to an event click a command
button:
Me.txt1.Value = DMax("[Seventh]+1", "country.CCODE", "CCODE Like '" & _
[Forms]![Frmchange7]![txt5] & "'")
But I received Error 3078 means can't find GROUP BY "country.CCODE"
As my select query I have a key field named [Seventh] in TBL7 with samples
like:
1011911
1015476
3028839
5634749
and I also have Left([Seventh],3) in TXT5 as criteria (for example 101)
What I want to do is that , with click a button(cmd1) find maximum
[(101....)+1] and
display result(For exaple it must be 1015477) in a text box (TXT1)
All controls are in a Form named Frmchange7
Thank you again
Bijan

Duane Hookom said:
I would create a group by query with the two tables and use DMax().
=DMax("[Seventh]+1"," Your group by query","CCODE Like '" &
[Forms]![Frmchange7]![txt5] & "'")
If the text box is on FrmChange7 then you don't need Forms!Frmchange7.

--
Duane Hookom
Microsoft Access MVP


bijan said:
Hi ALL,
please help How can I capture result of this SQL in my Textbox(txt1.value):

SELECT Max([Seventh]+1) AS Expr1
FROM TBL7 INNER JOIN country ON TBL7.Country = country.[COUNTRY-NAME]
GROUP BY country.CCODE
HAVING (((country.CCODE) Like [Forms]![Frmchange7]![txt5]));

any help would be appreciate
Click to expand...
Click to expand...
Click to expand...
 
Hi Duane
Thank you,
I found out where I was going wrong finaly!
Bijan



Duane Hookom said:
I would create a group by query with the two tables and use DMax().
=DMax("[Seventh]+1"," Your group by query","CCODE Like '" &
[Forms]![Frmchange7]![txt5] & "'")
If the text box is on FrmChange7 then you don't need Forms!Frmchange7.

--
Duane Hookom
Microsoft Access MVP


bijan said:
Hi ALL,
please help How can I capture result of this SQL in my Textbox(txt1.value):

SELECT Max([Seventh]+1) AS Expr1
FROM TBL7 INNER JOIN country ON TBL7.Country = country.[COUNTRY-NAME]
GROUP BY country.CCODE
HAVING (((country.CCODE) Like [Forms]![Frmchange7]![txt5]));

any help would be appreciate
Click to expand...
Click to expand...
 
Back
Top