What am I doing wrong?

[nanasandy]

I have a Client table with a field called Primary_Heating_Fuel. There are 7 different values that could be in this field. I need to count how many of each value there are and this is the first value I'm trying to count. I keep getting an error. The record needs to meet both values to get counted.


PF1 Count: IIf(Client!Primary_Heating_Fuel="Natural Gas" And [~tempJobCompleted]![Add On]=False,1,0)

 

[dhookom]

Is there anything you would like to share about [~tempJobCompleted]?

It would help to see the full SQL of the query and information about significant fields such as their data types.

Also, when you state "I keep getting an error", it is appropriate to tell us the error message.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[nanasandy]

Sorry, this is my first time.

Honestly I'm not sure why this temp file is here other than it appears to give other criteria that has to be met before the counts on everything is done.
I didn't write this DB only trying to modify it.

This is one that works:
OwnerSF Count: IIf(Client!Owner=True And Client!SF=True And [~tempJobCompleted]![Add On]=False,1,0)

This is mine.
PF1 Count: IIf(Client!Primary_Heating_Fuel="Natural Gas" And [~tempJobCompleted]![Add On]=False,1,0)

These are in a query used to create statistical information about the clients.
I need to count how many clients use Natural Gas for their primary fuel. This query is use to create a report.

The error I get is just a query field PF1 Count with #error in the table for all records except the ones that get a 0 because they don't meet the criteria.

I'm sure I'm missing something simply but I don't know what.

Thank you for any help you can give me.

 

[dhookom]

Is the Primary_Heating_Fuel field data type Text? Again, could you show us your complete SQL view?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[nanasandy]

Yes the Primary_Heating_Fuel type is text.
I hope this is what you are asking for:


SELECT Client.*, [~tempJobCompleted].*, County.County, Client![Last Name] & ", " & Client![First Name] AS EName, Not Client!Owner AS Renter, IIf([~tempJobCompleted]![Add On],0,1) AS [Jobs Completed], IIf(Client![Poverty Percentage]<=75 And [~tempJobCompleted]![Add On]=False,1,0) AS [0-75 POP], IIf(Client![Poverty Percentage]<=100 And Client![Poverty Percentage]>=76 And [~tempJobCompleted]![Add On]=False,1,0) AS [76-100 POP], IIf(Client![Poverty Percentage]<=125 And Client![Poverty Percentage]>=101 And [~tempJobCompleted]![Add On]=False,1,0) AS [101-125 POP], IIf(Client![Poverty Percentage]<=150 And Client![Poverty Percentage]>=126 And [~tempJobCompleted]![Add On]=False,1,0) AS [126-150 POP], IIf(Client![Poverty Percentage]>=151 And [~tempJobCompleted]![Add On]=False,1,0) AS [151 Up POP], IIf(Client!Owner=True And [~tempJobCompleted]![Add On]=False,1,0) AS [Owner Count], IIf(Client!Owner=True And Client!MH=True And [~tempJobCompleted]![Add On]=False,1,0) AS [Owner MH Count], IIf(Client!Owner=False And [~tempJobCompleted]![Add On]=False,1,0) AS [Renter Count], IIf([~tempJobCompleted]![Add On]=True,"Y","N") AS AddOn, IIf([~tempJobCompleted]![Add On],0,Client![# Of Peaple In Home]) AS NumPeps, IIf([~tempJobCompleted]![Add On],0,Client!Disabled) AS NumDis, IIf([~tempJobCompleted]![Add On],0,Client!Elderly) AS NumEld, IIf([~tempJobCompleted]![Add On],0,Client!Other) AS NumOther, IIf([~tempJobCompleted]![Add On],0,Client![Ages 0-2]) AS [Num0-2], IIf([~tempJobCompleted]![Add On],0,Client![Ages 3-5]) AS [Num3-5], IIf([~tempJobCompleted]![Add On],0,Client![Ages 6-18]) AS [Num6-18], IIf([~tempJobCompleted]![Add On],0,Client!Children) AS NumChild, IIf(Client!Owner=True And Client!MH=True And [~tempJobCompleted]![Add On]=False,1,0) AS [OwnerMH Count], IIf(Client!Owner=True And Client!MF=True And [~tempJobCompleted]![Add On]=False,1,0) AS [OwnerMF Count], IIf(Client!Owner=True And Client!SF=True And [~tempJobCompleted]![Add On]=False,1,0) AS [OwnerSF Count], IIf(Client!Owner=False And Client!MH=True And [~tempJobCompleted]![Add On]=False,1,0) AS [RenterMH Count], IIf(Client!Owner=False And Client!MF=True And [~tempJobCompleted]![Add On]=False,1,0) AS [RenterMF Count], IIf(Client!Owner=False And Client!SF=True And [~tempJobCompleted]![Add On]=False,1,0) AS [RenterSF Count], IIf(Client!Primary_Heating_Fuel="Natural Gas" And [~tempJobCompleted]![Add On]=False,1,0) AS [PF1 Count], IIf(Client!Primary_Heating_Fuel="Fuel Oil" And [~tempJobCompleted]![Add On]=False,1,0) AS [PF2 Count]
FROM County INNER JOIN (Client INNER JOIN [~tempJobCompleted] ON Client.JobID = [~tempJobCompleted].JobID) ON County.CountyID = Client.CountyID;

 

[nanasandy]

Sorry after reviewing your question closer, the field type in Client file is number due to doing a field lookup to fill this field.

 

[dhookom]

Do you now understand your issue that you are comparing a number with text?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[nanasandy]

Well, yes, but how can I get the number value for each thing? I still need to count how many have each item.

 

[PHV]

Have a look here:

http://www.mvps.org/access/lookupfields.htm

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

 

[nanasandy]

That isn't what I wanted to hear obviously it keeps me from doing what I wanted to do, but thank you for setting me straight.

 

[dhookom]

You should be able to use a field/column in your query like:

IIf(Client!Primary_Heating_Fuel=1 And [~tempJobCompleted]![Add On]=False,1,0) AS [PF1 Count], 
IIf(Client!Primary_Heating_Fuel=2 And [~tempJobCompleted]![Add On]=False,1,0) AS [PF2 Count],

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]