Expression Builder, Calculation confusion in Access 2007

120 pts.
Tags:
Access 2007
IIF function
Syntax errors
Ok I’m having trouble with this simple calculation.

 I have a Query with a field called “% of fire Calls made” it may contain a number (ie 23.34). I’ve tried to create another field in the same query that ask the question, if the # in “% of fire Calls made” is equal to or greater than 10, I then want the new field to say 25.

This is the field as it reads after I tried using the expression builder: Points for fire calls: IIf([% of fire Calls made]>=10,"25")

I keep getting an error that reads Syntax error in the query expression ‘IIf(% of fire Calls made>=10,“25”)’.

I have also looked at the book and i can't find what I'm doing wrong. Any help would be greatly appreciated

Phil



Software/Hardware used:
Access 2007

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 9  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    The expression looks correct. Can you post the SQL of your query ?
    69,175 pointsBadges:
    report
  • Phil12550
    ok I'm really new so i dont quite know what the SQL is if you can explane please. thanks
    120 pointsBadges:
    report
  • Phil12550
    well when i say im new i mean im self taught over the last year or so.
    120 pointsBadges:
    report
  • Phil12550
    ok i think i found the SQL and its really long. so here is the cut and paste: SELECT [Query Top Gun Attendance].[Query full name, Active FF].[Members Full Name], [Query LOSAP Report]![Query Top Gun Attendance.CountOfIncident # yyyy-123]/[Query LOSAP Report]![CountOfIncident # yyyy-1231]*100 AS [% of fire Calls made], [Query Sleep Over Count].[CountOftracking number], [Query Top Gun Drill Count].[CountOftracking number], [Query Company Meeting Count].[CountOftracking number], [Query WorkParty,Fund,FirePrev,Perade,Funeral,Other Count].[CountOftracking number], [Query State Training, Official Class, OSHA - sum of points].[SumOfService Award Points for Certificate class], [Query Position Points].[1st Position Held Elected or Appointed], [Query Position Points].[Service Award Point Value] FROM [Query Position Points] RIGHT JOIN ([Query Company Meeting Count] RIGHT JOIN ([Query WorkParty,Fund,FirePrev,Perade,Funeral,Other Count] RIGHT JOIN ([Query State Training, Official Class, OSHA - sum of points] RIGHT JOIN ([Query Top Gun Drill Count] RIGHT JOIN ([Query Sleep Over Count] RIGHT JOIN ([Query LOSAP Report] RIGHT JOIN [Query Top Gun Attendance] ON [Query LOSAP Report].[Query Top Gun Attendance].[Query full name, Active FF].[Members Full Name] = [Query Top Gun Attendance].[Query full name, Active FF].[Members Full Name]) ON [Query Sleep Over Count].[Query full name, Active FF].[Members Full Name] = [Query Top Gun Attendance].[Query full name, Active FF].[Members Full Name]) ON [Query Top Gun Drill Count].[Query full name, Active FF].[Members Full Name] = [Query Top Gun Attendance].[Query full name, Active FF].[Members Full Name]) ON [Query State Training, Official Class, OSHA - sum of points].[Query full name, Active FF].[Members Full Name] = [Query Top Gun Attendance].[Query full name, Active FF].[Members Full Name]) ON [Query WorkParty,Fund,FirePrev,Perade,Funeral,Other Count].[Query full name, Active & Social].[Members Full Name] = [Query Top Gun Attendance].[Query full name, Active FF].[Members Full Name]) ON [Query Company Meeting Count].[Query full name, Active & Social].[Members Full Name] = [Query Top Gun Attendance].[Query full name, Active FF].[Members Full Name]) ON [Query Position Points].[Members Full Name] = [Query Top Gun Attendance].[Query full name, Active FF].[Members Full Name] GROUP BY [Query Top Gun Attendance].[Query full name, Active FF].[Members Full Name], [Query LOSAP Report]![Query Top Gun Attendance.CountOfIncident # yyyy-123]/[Query LOSAP Report]![CountOfIncident # yyyy-1231]*100, [Query Sleep Over Count].[CountOftracking number], [Query Top Gun Drill Count].[CountOftracking number], [Query Company Meeting Count].[CountOftracking number], [Query WorkParty,Fund,FirePrev,Perade,Funeral,Other Count].[CountOftracking number], [Query State Training, Official Class, OSHA - sum of points].[SumOfService Award Points for Certificate class], [Query Position Points].[1st Position Held Elected or Appointed], [Query Position Points].[Service Award Point Value];
    120 pointsBadges:
    report
  • Phil12550
    to make it a little easier, this is the entry taken from the field of the query in question. % of fire Calls made: [Query LOSAP Report]![Query Top Gun Attendance.CountOfIncident # yyyy-123]/[Query LOSAP Report]![CountOfIncident # yyyy-1231]*100 this expression creates a number ie: 23.45 its this number that I want to do the calculation to say “if >= 10 then 25” if that makes any sense
    120 pointsBadges:
    report
  • carlosdl
    Thanks Phil. I assume you posted the query that works well, not the modified one that gives you the syntax error. Try using this SQL command: SELECT [Query Top Gun Attendance].[Query full name, Active FF].[Members Full Name], [Query LOSAP Report]![Query Top Gun Attendance.CountOfIncident # yyyy-123]/[Query LOSAP Report]![CountOfIncident # yyyy-1231]*100 AS [% of fire Calls made], [Query Sleep Over Count].[CountOftracking number], [Query Top Gun Drill Count].[CountOftracking number], [Query Company Meeting Count].[CountOftracking number], [Query WorkParty,Fund,FirePrev,Perade,Funeral,Other Count].[CountOftracking number], [Query State Training, Official Class, OSHA - sum of points].[SumOfService Award Points for Certificate class], [Query Position Points].[1st Position Held Elected or Appointed], [Query Position Points].[Service Award Point Value], Iif([% of fire Calls made]>=10,25) AS [YourNewFieldName] FROM [Query Position Points] RIGHT JOIN ([Query Company Meeting Count] RIGHT JOIN ([Query WorkParty,Fund,FirePrev,Perade,Funeral,Other Count] RIGHT JOIN ([Query State Training, Official Class, OSHA - sum of points] RIGHT JOIN ([Query Top Gun Drill Count] RIGHT JOIN ([Query Sleep Over Count] RIGHT JOIN ([Query LOSAP Report] RIGHT JOIN [Query Top Gun Attendance] ON [Query LOSAP Report].[Query Top Gun Attendance].[Query full name, Active FF].[Members Full Name] = [Query Top Gun Attendance].[Query full name, Active FF].[Members Full Name]) ON [Query Sleep Over Count].[Query full name, Active FF].[Members Full Name] = [Query Top Gun Attendance].[Query full name, Active FF].[Members Full Name]) ON [Query Top Gun Drill Count].[Query full name, Active FF].[Members Full Name] = [Query Top Gun Attendance].[Query full name, Active FF].[Members Full Name]) ON [Query State Training, Official Class, OSHA - sum of points].[Query full name, Active FF].[Members Full Name] = [Query Top Gun Attendance].[Query full name, Active FF].[Members Full Name]) ON [Query WorkParty,Fund,FirePrev,Perade,Funeral,Other Count].[Query full name, Active & Social].[Members Full Name] = [Query Top Gun Attendance].[Query full name, Active FF].[Members Full Name]) ON [Query Company Meeting Count].[Query full name, Active & Social].[Members Full Name] = [Query Top Gun Attendance].[Query full name, Active FF].[Members Full Name]) ON [Query Position Points].[Members Full Name] = [Query Top Gun Attendance].[Query full name, Active FF].[Members Full Name] GROUP BY [Query Top Gun Attendance].[Query full name, Active FF].[Members Full Name], [Query LOSAP Report]![Query Top Gun Attendance.CountOfIncident # yyyy-123]/[Query LOSAP Report]![CountOfIncident # yyyy-1231]*100, [Query Sleep Over Count].[CountOftracking number], [Query Top Gun Drill Count].[CountOftracking number], [Query Company Meeting Count].[CountOftracking number], [Query WorkParty,Fund,FirePrev,Perade,Funeral,Other Count].[CountOftracking number], [Query State Training, Official Class, OSHA - sum of points].[SumOfService Award Points for Certificate class], [Query Position Points].[1st Position Held Elected or Appointed], [Query Position Points].[Service Award Point Value]; If you get errors, please post the complete error message, and the SQL of the query that generates the error.
    69,175 pointsBadges:
    report
  • tlsanders1
    The Immediate If (IIF) needs another parameter. You have told it what to return if % of Fire Calls is >= 10, but you did not tell it what to return if that is not true. IIF(% of Fire Calls >= 10, "25","1") or some such.
    1,340 pointsBadges:
    report
  • carlosdl
    The second parameter is not mandatory, if the condition is not met and the sencond parameter is not specified, it will return null.
    69,175 pointsBadges:
    report
  • tlsanders1
    I just re-looked at the original question. I cannot tell if the original post that I was reading as an Immediate If is Upper Case I, Upper Case I, Lower Case F or Upper Case I, Lower Case L, Lower Case F. If the original SQL had eye el eff, maybe that was triggering the syntax error.
    1,340 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following