Problem with a Query

[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 [Qates].Date, (select last(FlexiBalance) from [T:Flexi] where
[Qates].[Date]>=[T:Flexi].[Date]) AS LFB, (select last([Date]) from
[T:Flexi] where [Qates].[Date]>=[T:Flexi].[Date]) AS LFD, CDate(nz((select
sum((FC)) from [Q:Times] where [Qates].[Date]=[Q:Times].[Date]))) AS
FCNew, CDate(nz((select (FCP) from [Q:Times] where
[Qates].[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 [Qates] ON [T:Flexi].Date
= [Qates].Date) ON [Q:Times].Date = [Qates].Date
GROUP BY [Qates].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

 

[Lynn Trapp]

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

 

[John Ortt]

Hi Lynn,

The problem is that they are select statements.

For example:

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

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 [Qates].Date, (select last(FlexiBalance) from [T:Flexi] where
[Qates].[Date]>=[T:Flexi].[Date]) AS LFB, (select last([Date]) from
[T:Flexi] where [Qates].[Date]>=[T:Flexi].[Date]) AS LFD,
CDate(nz((select
sum((FC)) from [Q:Times] where [Qates].[Date]=[Q:Times].[Date]))) AS
FCNew, CDate(nz((select (FCP) from [Q:Times] where
[Qates].[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 [Qates] ON
[T:Flexi].Date
= [Qates].Date) ON [Q:Times].Date = [Qates].Date
GROUP BY [Qates].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 Ortt]

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
[Qates].[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

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

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 [Qates].Date, (select last(FlexiBalance) from [T:Flexi] where
[Qates].[Date]>=[T:Flexi].[Date]) AS LFB, (select last([Date]) from
[T:Flexi] where [Qates].[Date]>=[T:Flexi].[Date]) AS LFD,
CDate(nz((select
sum((FC)) from [Q:Times] where [Qates].[Date]=[Q:Times].[Date]))) AS
FCNew, CDate(nz((select (FCP) from [Q:Times] where
[Qates].[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 [Qates] ON
[T:Flexi].Date
= [Qates].Date) ON [Q:Times].Date = [Qates].Date
GROUP BY [Qates].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