2 SQL statements into 1

[Greaser]

Hi,
Is there a way to combine 2 SQL statements into 1?

Query1:
SELECT itsTbl.itsNo, itsTbl.workplanNo, itsTbl.workplanSectionNo
FROM itsTbl
WHERE (((itsTbl.workplanNo)=[Forms]![itsNewFrm]![workplanNoCombo]) AND ((itsTbl.workplanSectionNo)=[Forms]![itsNewFrm]![workplanSectionNoCombo]));

Query2:
SELECT Max(Right([itsNo],3))+1 AS itsNo_3
FROM Query1;
This SQL query will be attached to [Forms]![itsNewFrm]![workplanSectionNoCombo] AfterUpdate statement

Then, I would like to return the value of the result of Query2 to unbound field itsNo_3 in form itsNewFrm

Thanks,
John

 

[Golom]

SELECT Max(Right(Q1.[itsNo],3))+1 AS itsNo_3
FROM 

(
SELECT itsTbl.itsNo
     , itsTbl.workplanNo
     , itsTbl.workplanSectionNo

FROM itsTbl

WHERE itsTbl.workplanNo=[Forms]![itsNewFrm]![workplanNoCombo] 
  AND itsTbl.workplanSectionNo=[Forms]![itsNewFrm]![workplanSectionNoCombo]
) As Q1

 

[Greaser]

Thanks Golom.
I have one more question:
In what context do I use the query?
If I use DoCmd.RunSQL, the debugger will only let me use the UPDATE statement, not the SELECT statement.
Also, Do I declare itsNo_3 as a string?

Thanks again,
John

 

[PHV]

I would like to return the value of the result of Query2 to unbound field itsNo_3 in form itsNewFrm
Why not simply something like this ?

Me!itsNo_3 = Nz(DMax("Right(itsNo,3)", "itsTbl", "workplanNo='" _
  & Me!workplanNoCombo & "' AND workplanSectionNo='" _
  & Me!workplanSectionNoCombo & "'"), 0) + 1

If either workplanNo or workplanSectionNo are defined as numeric in itsTbl then get rid of the corresponding single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Greaser]

Thanks for your help.
workplanNo and workplanSectionNo are declared as Text (2 chars) in itsTbl.

I get a "type mismatch" error message.

When I remove the single quotes, I get a "Type mismatch in criteria expression" error message.
Thanks again,
Cheers,
John

 

[PHV]

And this ?
Me!itsNo_3 = Val(DMax("Right(itsNo,3)", "itsTbl", "workplanNo='" _
& Me!workplanNoCombo & "' AND workplanSectionNo='" _
& Me!workplanSectionNoCombo & "'") & "") + 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Greaser]

Worked like a charm.
Thanks.
Cheers,
John