Problem with a Query

  • Thread starter Thread starter John Ortt
  • Start date Start date
J

John Ortt

I have written the query two work out flexi time at work. The SQL code is
shown at the bottom of this e-mail. The query refferences two fields within
itself for the final field. The query runs and displays the correct result,
but two prompts appear asking for the value of FCPNew and FCNew. If you
just press return each time the query works correctly and displays the data
(I have included an example at the bottom of the page.)

Could anybody explain how to get rid of the prompt boxes please.

Thankyou,

John Ortt


SELECT [Q:Dates].Date, (select last(FlexiBalance) from [T:Flexi] where
[Q:Dates].[Date]>=[T:Flexi].[Date]) AS LFB, (select last([Date]) from
[T:Flexi] where [Q:Dates].[Date]>=[T:Flexi].[Date]) AS LFD, CDate(nz((select
sum((FC)) from [Q:Times] where [Q:Dates].[Date]=[Q:Times].[Date]))) AS
FCNew, CDate(nz((select (FCP) from [Q:Times] where
[Q:Dates].[Date]=[Q:Times].[Date]))) AS FCPNew, IIf(Left([FCPNew],1)=0 Or
Left([FCPNew],1)=3,"","-") & Left([FCNew],5) AS FC
FROM [Q:Times] RIGHT JOIN ([T:Flexi] RIGHT JOIN [Q:Dates] ON [T:Flexi].Date
= [Q:Dates].Date) ON [Q:Times].Date = [Q:Dates].Date
GROUP BY [Q:Dates].Date, IIf(Left([FCPNew],1)=0 Or
Left([FCPNew],1)=3,"","-") & Left([FCNew],5);


Date LFB LFD FCNew FCPNew FC
11/08/02 -01.48 11/08/02 00:00 0.00 00:00
10/08/03 -07.55 10/08/03 00:00 0.00 00:00
11/08/03 -07.55 10/08/03 00:34 1.00 00:34
12/08/03 -07.55 10/08/03 00:00 1.00 00:00
13/08/03 -07.55 10/08/03 07:24 -1.00 -07:24
05/01/04 -05.44 05/01/04 02:24 1.00 02:24
06/01/04 -05.44 05/01/04 00:01 -1.00 -00:01
07/01/04 -05.44 05/01/04 02:24 1.00 02:24
08/01/04 -05.44 05/01/04 01:51 1.00 01:51
 
John,
The Group By clause does not recognize the aliases FCPNew and FCNew. Replace
them with the actual field names.
 
Hi Lynn,

The problem is that they are select statements.

For example:





Lynn Trapp said:
John,
The Group By clause does not recognize the aliases FCPNew and FCNew. Replace
them with the actual field names.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


John Ortt said:
I have written the query two work out flexi time at work. The SQL code is
shown at the bottom of this e-mail. The query refferences two fields
within
itself for the final field. The query runs and displays the correct
result,
but two prompts appear asking for the value of FCPNew and FCNew. If you
just press return each time the query works correctly and displays the
data
(I have included an example at the bottom of the page.)

Could anybody explain how to get rid of the prompt boxes please.

Thankyou,

John Ortt


SELECT [Q:Dates].Date, (select last(FlexiBalance) from [T:Flexi] where
[Q:Dates].[Date]>=[T:Flexi].[Date]) AS LFB, (select last([Date]) from
[T:Flexi] where [Q:Dates].[Date]>=[T:Flexi].[Date]) AS LFD,
CDate(nz((select
sum((FC)) from [Q:Times] where [Q:Dates].[Date]=[Q:Times].[Date]))) AS
FCNew, CDate(nz((select (FCP) from [Q:Times] where
[Q:Dates].[Date]=[Q:Times].[Date]))) AS FCPNew, IIf(Left([FCPNew],1)=0 Or
Left([FCPNew],1)=3,"","-") & Left([FCNew],5) AS FC
FROM [Q:Times] RIGHT JOIN ([T:Flexi] RIGHT JOIN [Q:Dates] ON
[T:Flexi].Date
= [Q:Dates].Date) ON [Q:Times].Date = [Q:Dates].Date
GROUP BY [Q:Dates].Date, IIf(Left([FCPNew],1)=0 Or
Left([FCPNew],1)=3,"","-") & Left([FCNew],5);


Date LFB LFD FCNew FCPNew FC
11/08/02 -01.48 11/08/02 00:00 0.00 00:00
10/08/03 -07.55 10/08/03 00:00 0.00 00:00
11/08/03 -07.55 10/08/03 00:34 1.00 00:34
12/08/03 -07.55 10/08/03 00:00 1.00 00:00
13/08/03 -07.55 10/08/03 07:24 -1.00 -07:24
05/01/04 -05.44 05/01/04 02:24 1.00 02:24
06/01/04 -05.44 05/01/04 00:01 -1.00 -00:01
07/01/04 -05.44 05/01/04 02:24 1.00 02:24
08/01/04 -05.44 05/01/04 01:51 1.00 01:51
Click to expand...
Click to expand...
 
OOpps, sorry I clipped enter.... to finish what I was saying.

Hi Lynn,

The problem is that they are select statements.

For example:

CDate(nz((select (FCP) from [Q:Times] where
[Q:Dates].[Date]=[Q:Times].[Date]))) AS FCPNew

is the code for the FCPNew field which is subsequently used in a separate
field.

As stated the code works but it just brings up the annoying prompt box.

I have tried pasting the entire string in in place of the FCPNew but it
doesn't like it.

Thanks for looking anyway,

John


Lynn Trapp said:
John,
The Group By clause does not recognize the aliases FCPNew and FCNew. Replace
them with the actual field names.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


John Ortt said:
I have written the query two work out flexi time at work. The SQL code is
shown at the bottom of this e-mail. The query refferences two fields
within
itself for the final field. The query runs and displays the correct
result,
but two prompts appear asking for the value of FCPNew and FCNew. If you
just press return each time the query works correctly and displays the
data
(I have included an example at the bottom of the page.)

Could anybody explain how to get rid of the prompt boxes please.

Thankyou,

John Ortt


SELECT [Q:Dates].Date, (select last(FlexiBalance) from [T:Flexi] where
[Q:Dates].[Date]>=[T:Flexi].[Date]) AS LFB, (select last([Date]) from
[T:Flexi] where [Q:Dates].[Date]>=[T:Flexi].[Date]) AS LFD,
CDate(nz((select
sum((FC)) from [Q:Times] where [Q:Dates].[Date]=[Q:Times].[Date]))) AS
FCNew, CDate(nz((select (FCP) from [Q:Times] where
[Q:Dates].[Date]=[Q:Times].[Date]))) AS FCPNew, IIf(Left([FCPNew],1)=0 Or
Left([FCPNew],1)=3,"","-") & Left([FCNew],5) AS FC
FROM [Q:Times] RIGHT JOIN ([T:Flexi] RIGHT JOIN [Q:Dates] ON
[T:Flexi].Date
= [Q:Dates].Date) ON [Q:Times].Date = [Q:Dates].Date
GROUP BY [Q:Dates].Date, IIf(Left([FCPNew],1)=0 Or
Left([FCPNew],1)=3,"","-") & Left([FCNew],5);


Date LFB LFD FCNew FCPNew FC
11/08/02 -01.48 11/08/02 00:00 0.00 00:00
10/08/03 -07.55 10/08/03 00:00 0.00 00:00
11/08/03 -07.55 10/08/03 00:34 1.00 00:34
12/08/03 -07.55 10/08/03 00:00 1.00 00:00
13/08/03 -07.55 10/08/03 07:24 -1.00 -07:24
05/01/04 -05.44 05/01/04 02:24 1.00 02:24
06/01/04 -05.44 05/01/04 00:01 -1.00 -00:01
07/01/04 -05.44 05/01/04 02:24 1.00 02:24
08/01/04 -05.44 05/01/04 01:51 1.00 01:51
Click to expand...
Click to expand...
 
Back
Top