Evaluating query results and setting a value

[anoushka]

hi

i have a query that returns a dataset that looks like this:

Account Status
123 Paused
123 Paused
123 Not Paused

I want to evaluate these results and in a second query, for the account 123 return the value "Not Paused" if there is at least one status set to "Not Paused".

How can i do this?

Thanks in advance.

 

[boblarson]

You can actually use a DCount:

=IIf(DCount("[Status]", "YourQueryNameHere","[Account]=" Me!YourAccountNumberFieldHere & " And [Status]= 'Not Paused'")>0,"Not Paused","")

 

[anoushka]

i am not sure what i am doing wrong

when i enter that expression into my query it says it does not like this bit

"Me!AccountNumber"

 

[Rabbie]

Anoushka, Where exactly are you inserting this expression. Me usually refers to the currently open form/report and the expression as given you by Bob would work if you used it in VBA on your form etc. If you are using it directly in your query then you would need to remove the ME! bit

 

[anoushka]

i removed the ME! bit and it still doesnt work..

i am using it in a query, lets say QUERY1 (ME)..and the dcount is on QUERY2

=IIf(DCount("[Status]", "QUERY2","[AccountNumber]=" Me!AccountNumber & " And [Status]= 'Not Paused'")>0,"Not Paused","")

 

[Rabbie]

i removed the ME! bit and it still doesnt work..

i am using it in a query, lets say QUERY1 (ME)..and the dcount is on QUERY2

=IIf(DCount("[Status]", "QUERY2","[AccountNumber]=" Me!AccountNumber & " And [Status]= 'Not Paused'")>0,"Not Paused","")

I don't think you can use Me as a reference to a query. Have you tried replacing ME with Query1. You will need to have AccountNumber as a field in the query. If that doesn't work can you post the whole SQL for Query1 so we can help you.

 

[anoushka]

SELECT DW_Account_Rollup.CalendarYearNumber, DW_Account_Rollup.CalendarMonthNumber, DW_Account_Rollup.AccountName, DW_Account_Rollup.AccountNumber, DW_Account_Rollup.Agency, DW_Account_Rollup.SumOfTotalSpend, DW_Account_Rollup.AvgOfAverageDailySpend, IIf(DCount("[PauseStatus]","DW_Account_Rollup","[AccountNumber]= [AccountNumber] And [PauseStatus]= 'Not Paused'")>0,"Not Paused","Paused") AS Status
FROM DW_Account_Rollup;

I have tried

[AccountNumber]=" [Query1].[AccountNumber] & "

[AccountNumber]= [Query1].[AccountNumber] & "

 

[Rabbie]

SELECT DW_Account_Rollup.CalendarYearNumber, DW_Account_Rollup.CalendarMonthNumber, DW_Account_Rollup.AccountName, DW_Account_Rollup.AccountNumber, DW_Account_Rollup.Agency, DW_Account_Rollup.SumOfTotalSpend, DW_Account_Rollup.AvgOfAverageDailySpend, IIf(DCount("[PauseStatus]","DW_Account_Rollup","[AccountNumber]= [AccountNumber] And [PauseStatus]= 'Not Paused'")>0,"Not Paused","Paused") AS Status
FROM DW_Account_Rollup;

I have tried

[AccountNumber]=" [Query1].[AccountNumber] & "

[AccountNumber]= [Query1].[AccountNumber] & "

It might be worth trying DW_Account_Rollup.accountnumber = query2.accountnumber.


This assumes there is an account number field in Query2

Good luck!

 

[anoushka]

that does not work...it says it cannot find the expression...i think there must be another way to reference the query the dcount is in

 

[Rabbie]

Do you have the accountnumber in query2? How is that field spelled.

 

[anoushka]

they are the same in both queries...AccountNumber...

 

[Rabbie]

Sorry. I am afraid I have run out of ideas for you.

 

[anoushka]

thanks for all your help anyway. i found a round-about solution.

 

[Fernando]

If you just have 2 states "Paused" and "NotPaused" you can make it a Boolean field and make something like this

SELECT Max(fldPaused), fldAccNumber
FROM tbl..... bla bla bla
GROUP BY fldAccNumber

Having that 0 or False is Not Paused and -1 or True is Paused, you will get the AccNumbers and if at least 1 is Not Paused you will get fldPaused = False since 0 > -1