Query Field not available in Builder (1 Viewer)

[Nyanko]

Hi,

Just curious. Is there a way to add a created field to another field in a crosstab query ?

I've created a field called

Expected: DateAdd("d",Nz([DaysToPay],30)+Left([terms],2),[ScheduledDate])

This gives me a date on which I can expect my invoice to be paid.

When I want to use this date to distinguish what Financial Year it falls I am using this :

FY: IIf(Month([Expected])>=4,Year([Expected]) & "-" & Right(Year([Expected])+1,2),Year([Expected])-1 & "-" & Right(Year([Expected]),2))

When running I get the error : The Microsoft Access database engine does not recognize '[Expected]' as a valid field name or expression

I guess I could put the Expected formula everytime it is mentioned in the FY field, but that feels messy.

I've definitely had it work that the fields used in the query are immediately available in the Builder>Expression Categories section.
Any advice ?

 

[arnelgp]

its messy, just substitute the expression of Expected in your second query (the long expression).

 

[Ranman256]

If Query 1 has the expected date,
And Q2 uses Q1 to calculate FY, it will work.
But you cannot calc FY, in Q1 this way.

If you want FY in Q1, you must build a nested function,And put that DateAdd inside the IIF
Function, because EXPECTED has not created yet.

 

[Nyanko]

Just to clarify this is all same query, but different fields in that query. There are 4 fields in this query :

Expected : The scheduled date + the term days + days to pay

FY : Find the financial year that the Expected date is in

Sort : A yyyy-mm formatted version of Expected for the column headers

InvoiceValue : Value from the main table

This is the SQL

TRANSFORM 
Sum(tbl_MainData.InvoiceValue) AS SumOfInvoiceValue
SELECT 
DateAdd("d",Nz([DaysToPay],30)+Left([terms],2),[ScheduledDate]) AS Expected, 
IIf(Month([ExpectedDate])>=4,Year([ExpectedDate]) & "-" & Right(Year([ExpectedDate])+1,2),Year([ExpectedDate])-1 & "-" & Right(Year([ExpectedDate]),2)) AS FY
FROM 
(lkp_ProjectList INNER JOIN tbl_MainData ON lkp_ProjectList.ProjectCode = tbl_MainData.ProjectCode) INNER JOIN lkp_Status ON tbl_MainData.Status = lkp_Status.Status
GROUP BY 
DateAdd("d",Nz([DaysToPay],30)+Left([terms],2),[ScheduledDate]), IIf(Month([ExpectedDate])>=4,Year([ExpectedDate]) & "-" & Right(Year([ExpectedDate])+1,2),Year([ExpectedDate])-1 & "-" & Right(Year([ExpectedDate]),2))
ORDER BY 
DateAdd("d",Nz([DaysToPay],30)+Left([terms],2),[ScheduledDate])
PIVOT 
Format(DateAdd("d",Nz([DaysToPay],30)+Left([terms],2),[ScheduledDate]),"yyyy-mm");

I want to replace ExpectedDate with the field Expected as ExpectedDate is a calculated field in a table that I want to remove as I need to add a variable to the calculation that I can't do in the table. Seems like a bad idea to have it there.

 

[Ranman256]

you cant use EXPECTED date because it hasn't been created yet.
You must use 2 queries. Q1 to create the EXPECTED, then use Q1 in your xtab query.

 

[Nyanko]

Ahh I think it may be a grouping issue and this affects Crosstab queries

I recreated the query as a select and it let me choose the fileds in the Expression Builder and ran no problem with created fields in other calculations :

SELECT 
Format([Expected],"yyyy-mm") AS SORT, 
tbl_MainData.ProjectCode, 
tbl_MainData.ScheduledDate, 
lkp_ProjectList.DaysToPay, 
DateAdd("d",30,[ScheduledDate]) AS Expected, 
IIf(Month([Expected])>=4,Year([Expected]) & "-" & Right(Year([Expected])+1,2),Year([Expected])-1 & "-" & Right(Year([Expected]),2)) AS FY, 
tbl_MainData.InvoiceValue
FROM 
lkp_ProjectList INNER JOIN tbl_MainData ON lkp_ProjectList.ProjectCode = tbl_MainData.ProjectCode;

The moment I change the above to Crosstab then [Expected] is no longer recognised

TRANSFORM 
Sum(tbl_MainData.InvoiceValue) AS SumOfInvoiceValue
SELECT 
tbl_MainData.ProjectCode,
 tbl_MainData.ScheduledDate, 
DateAdd("d",30,[ScheduledDate]) AS Expected, 
IIf(Month([Expected])>=4,Year([Expected]) & "-" & Right(Year([Expected])+1,2),Year([Expected])-1 & "-" & Right(Year([Expected]),2)) AS FY
FROM 
lkp_ProjectList INNER JOIN tbl_MainData ON lkp_ProjectList.ProjectCode = tbl_MainData.ProjectCode
GROUP BY 
tbl_MainData.ProjectCode, 
tbl_MainData.ScheduledDate, 
lkp_ProjectList.DaysToPay, 
DateAdd("d",30,[ScheduledDate]), 
IIf(Month([Expected])>=4,Year([Expected]) & "-" & Right(Year([Expected])+1,2),Year([Expected])-1 & "-" & Right(Year([Expected]),2))
PIVOT 
Format([Expected],"yyyy-mm");

If I change this back to a select it still gives me the error warning even though it worked before. When I remove the grouping, it works again :/

Any thoughts? Or do I just have to accept this is a feature and create a work around ?

 

[Nyanko]

OK, So I was playing with the GROUP BY options and got this to work with the Expected field value. I set all calculated fields in the Row heading to Expression. Only the Column Heading had to be a group by so I couldn't use the Expected value there.

TRANSFORM 
Sum(tbl_MainData.InvoiceValue) AS SumOfInvoiceValue
SELECT 
tbl_MainData.ProjectCode, 
tbl_MainData.ScheduledDate, 
DateAdd("d",30,[ScheduledDate]) AS Expected, 
IIf(Month([Expected])>=4,Year([Expected]) & "-" & Right(Year([Expected])+1,2),Year([Expected])-1 & "-" & Right(Year([Expected]),2)) AS FY
FROM 
lkp_ProjectList INNER JOIN tbl_MainData ON lkp_ProjectList.ProjectCode = tbl_MainData.ProjectCode
GROUP 
BY tbl_MainData.ProjectCode, tbl_MainData.ScheduledDate
PIVOT 
Format(DateAdd("d",Nz([DaysToPay],30)+Left([terms],2),[ScheduledDate]),"yyyy-mm");

Just in case this helps anyone else
(Or more than likely, me in a year or so !!)