By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,316 Members | 2,317 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,316 IT Pros & Developers. It's quick & easy.

Calculated field generates unexpected parameter prompt

P: 4
I have the calculated field below, which runs fine by itself. When I put a selection criteria (3) into the query, however, I get these unexplained parameter prompts. I'm running Access 2002 on Windows XP. Any thoughts?


((Nz([VotingHistory1],0)+Nz([VotingHistory2],0)+Nz([VotingHistory3],0)+Nz([VotingHistory4],0)+Nz([VotingHistory5],0))=3)
Oct 3 '08 #1
Share this Question
Share on Google+
13 Replies


missinglinq
Expert 2.5K+
P: 3,532
What exactly are you trying to do with this calculation? Is it being used as part of a If...Then construct in the query, or in something else?

We either need more explanation or, preferably, to actually see your SQL code. As I told someone else today, Carnac the Magnificent doen't come to this forum anymore!

Linq ;0)>
Oct 4 '08 #2

NeoPa
Expert Mod 15k+
P: 31,707
When I put a selection criteria (3) into the query, however, I get these unexplained parameter prompts.
Unexplained, but more importantly, unshared.

Does it not occur to you that the error message may help to indicate what the problem is. The rest of your explanation doesn't tell us anything much so we're just hoping the error message might give us a clue. Without it we are left trying to read your mind - But as Linq said already :
Carnac the Magnificent doen't come to this forum anymore!
Oct 5 '08 #3

P: 4
I thought I was sharing the relevant info. Sorry. Here is what the parameter prompt says:
"Enter Parameter Value"

"Voting History 1"

Here is the entire SQL code:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.  [San Joaquin Voter File 9-16-08].RegistrationNumber,  
  3.  [San Joaquin Voter File 9-16-08].ResidenceCity,  
  4.  [San Joaquin Voter File 9-16-08].PrecinctID,  
  5.  [San Joaquin Voter File 9-16-08].StreetName,  
  6.  [San Joaquin Voter File 9-16-08].HouseNumber,  
  7.  [LastName] & ", " & [FirstName] AS FullName,  
  8.  [San Joaquin Voter File 9-16-08].LastName,  
  9.  [San Joaquin Voter File 9-16-08].FirstName,  
  10.  [San Joaquin Voter File 9-16-08].MiddleName,  
  11.  [San Joaquin Voter File 9-16-08].Gender,  
  12.  [San Joaquin Voter File 9-16-08].ResidenceZipCode,  
  13.  [San Joaquin Voter File 9-16-08].StreetSuffix,  
  14.  [StreetName] & " " & [StreetSuffix] AS FullStreet,  
  15.  [San Joaquin Voter File 9-16-08].PhoneNumber,  
  16.  [San Joaquin Voter File 9-16-08].PartyAbbr,  
  17.  [San Joaquin Voter File 9-16-08].AVStatus,  
  18.  IIf([AVStatus]="Perm VBM","Y"," ") AS AVStatusYesNo,  
  19.  [San Joaquin Voter File 9-16-08].HistoryVotingPrecinct_1,  
  20.  IIf([HistoryVotingPrecinct_1]<>" ",1) AS VotingHistory1,  
  21.  [San Joaquin Voter File 9-16-08].HistoryVotingPrecinct_2,  
  22.  IIf([HistoryVotingPrecinct_2]<>" ",1) AS VotingHistory2,  
  23.  [San Joaquin Voter File 9-16-08].HistoryVotingPrecinct_3,  
  24.  IIf([HistoryVotingPrecinct_3]<>" ",1) AS VotingHistory3,  
  25.  [San Joaquin Voter File 9-16-08].HistoryVotingPrecinct_4,  
  26.  IIf([HistoryVotingPrecinct_4]<>" ",1) AS VotingHistory4,  
  27.  [San Joaquin Voter File 9-16-08].HistoryVotingPrecinct_5,  
  28.  IIf([HistoryVotingPrecinct_5]<>" ",1) AS VotingHistory5, 
  29.  Nz([VotingHistory1],0)+Nz([VotingHistory2],0)+Nz([VotingHistory3],0)+Nz([VotingHistory4],0)+Nz([VotingHistory5],0) AS Last5VotingHistory,  
  30.  [San Joaquin Voter File 9-16-08].BirthDate,  
  31.  DateDiff("yyyy",[BirthDate],Now()) AS Age,  
  32.  "*" & [RegistrationNumber] & "*" AS BarCode 
  33. FROM 
  34.  [San Joaquin Voter File 9-16-08] 
  35. WHERE 
  36.  ((([San Joaquin Voter File 9-16-08].ResidenceCity)="stockton")  
  37.   AND (([San Joaquin Voter File 9-16-08].StreetName)="hatchers")  
  38.   AND (([San Joaquin Voter File 9-16-08].PartyAbbr)="DEM")  
  39.   AND ((IIf([AVStatus]="Perm VBM","Y"," "))<>"Y")  
  40.   AND ((Nz([VotingHistory1],0)+Nz([VotingHistory2],0)+Nz([VotingHistory3],0)+Nz([VotingHistory4],0)+Nz([VotingHistory5],0))<3)  
  41.   AND ((DateDiff("yyyy",[BirthDate],Now()))<=40))  
  42.  OR ((([San Joaquin Voter File 9-16-08].ResidenceCity)="stockton")  
  43.   AND (([San Joaquin Voter File 9-16-08].StreetName)="hatchers")  
  44.   AND (([San Joaquin Voter File 9-16-08].PartyAbbr)="DS")  
  45.   AND ((IIf([AVStatus]="Perm VBM","Y"," "))<>"Y")  
  46.   AND ((Nz([VotingHistory1],0)+Nz([VotingHistory2],0)+Nz([VotingHistory3],0)+Nz([VotingHistory4],0)+Nz([VotingHistory5],0))<3)  
  47.   AND ((DateDiff("yyyy",[BirthDate],Now()))<=40)) 
  48. ORDER BY 
  49.  [San Joaquin Voter File 9-16-08].ResidenceCity,
  50.  [San Joaquin Voter File 9-16-08].PrecinctID, 
  51.  [San Joaquin Voter File 9-16-08].StreetName, 
  52.  [San Joaquin Voter File 9-16-08].HouseNumber, 
  53.  [LastName] & ", " & [FirstName]; 
  54.  
Oct 6 '08 #4

missinglinq
Expert 2.5K+
P: 3,532
Your IIF() function is misformed a number of times, beginning with

IIf([HistoryVotingPrecinct_1]<>" ",1) AS VotingHistory1

The IIF() function has 3 *** required *** arguments:

IIF(Expression, TruePart, FalsePart)

You have an Expression

[HistoryVotingPrecinct_1]<>" "

a TruePart argument

1

but no FalsePart argument!

You then repeat this mistake four more times, I believe. Access is merely bombing out at the instance of the mistake.

They may be other problems with your SQL, but correct these first and, as the Beatles said "See how she runs!"

Linq ;0)>
Oct 6 '08 #5

NeoPa
Expert Mod 15k+
P: 31,707
First quick post. I intend to post more when I have it prepared.

Linq is sort of correct for the IIf() function, but only in VBA. Your usage of IIf() within SQL is actually correct usage and your working with the results is also fine.

Referring to your earlier reply (post #4), I'm happy you were posting what you thought necessary at the time. That is all we can expect really, but I suspect you still need to appreciate the importance of exactly accurate information. "Voting History 1" doesn't seem to appear in your SQL anywhere, so I assume you meant "VotingHistory1". Can I also assume for my understanding of the problem, that this also applied to "VotingHistory2" through "VotingHistory5"?
Oct 6 '08 #6

NeoPa
Expert Mod 15k+
P: 31,707
At this point, let me start by reformulating your SQL to get rid of some of the stuff that's not necessary. I may fix some of the more obvious problems, but not where it's not absolutely clear exactly what you intend. Then I will make a comment or two about what I've noticed.

It should be a much easier point to start fixing problems from though :
Expand|Select|Wrap|Line Numbers
  1. SELECT RegistrationNumber,
  2.        ResidenceCity,
  3.        PrecinctID,
  4.        StreetName,
  5.        HouseNumber,
  6.        [LastName] & ', ' & [FirstName] AS FullName,
  7.        LastName,
  8.        FirstName,
  9.        MiddleName,
  10.        Gender,
  11.        ResidenceZipCode,
  12.        StreetSuffix,
  13.        [StreetName] & ' ' & [StreetSuffix] AS FullStreet,
  14.        PhoneNumber,
  15.        PartyAbbr,
  16.        AVStatus,
  17.        IIf([AVStatus]='Perm VBM','Y',' ') AS AVStatusYesNo,
  18.        HistoryVotingPrecinct_1,
  19.        IIf([HistoryVotingPrecinct_1]<>' ',1,0) AS VotingHistory1,
  20.        HistoryVotingPrecinct_2,
  21.        IIf([HistoryVotingPrecinct_2]<>' ',1,0) AS VotingHistory2,
  22.        HistoryVotingPrecinct_3,
  23.        IIf([HistoryVotingPrecinct_3]<>' ',1,0) AS VotingHistory3,
  24.        HistoryVotingPrecinct_4,
  25.        IIf([HistoryVotingPrecinct_4]<>' ',1,0) AS VotingHistory4,
  26.        HistoryVotingPrecinct_5,
  27.        IIf([HistoryVotingPrecinct_5]<>' ',1,0) AS VotingHistory5,
  28.        [VotingHistory1]+
  29.        [VotingHistory2]+
  30.        [VotingHistory3]+
  31.        [VotingHistory4]+
  32.        [VotingHistory5] AS Last5VotingHistory,
  33.        BirthDate,
  34.        DateDiff('yyyy',[BirthDate],Date()) AS Age,
  35.        '*' & [RegistrationNumber] & '*' AS BarCode
  36. FROM [San Joaquin Voter File 9-16-08]
  37. WHERE (([ResidenceCity]='stockton')
  38.   AND  ([StreetName]='hatchers')
  39.   AND  ([PartyAbbr] In('DEM','DS'))
  40.   AND  (IIf([AVStatus]='Perm VBM','Y',' ')<>'Y')
  41.   AND  ([VotingHistory1]+
  42.         [VotingHistory2]+
  43.         [VotingHistory3]+
  44.         [VotingHistory4]+
  45.         [VotingHistory5]<3)
  46.   AND  (DateDiff('yyyy',[BirthDate],Date())<=40))
  47. ORDER BY [ResidenceCity],
  48.          [PrecinctID],
  49.          [StreetName],
  50.          [HouseNumber],
  51.          [LastName] & ', ' & [FirstName]
Oct 6 '08 #7

NeoPa
Expert Mod 15k+
P: 31,707
To put you out of your misery I will mention that your problem is that you are trying to refer to calculated field references in your WHERE clause. As the WHERE clause needs to be processed before the SELECT clause, these items are not yet available to reference, therefore this will always fail.
Oct 6 '08 #8

NeoPa
Expert Mod 15k+
P: 31,707
Notice, it is easier counting the voting history if you use 0 in the FALSE part of the IIf() rather than Null.
I will post just the WHERE clause as that is changed to handle your specific problem.
Expand|Select|Wrap|Line Numbers
  1. WHERE (([ResidenceCity]='stockton')
  2.   AND  ([StreetName]='hatchers')
  3.   AND  ([PartyAbbr] In('DEM','DS'))
  4.   AND  (IIf([AVStatus]='Perm VBM','Y',' ')<>'Y')
  5.   AND  (IIf([HistoryVotingPrecinct_1]<>' ',1,0)+
  6.         IIf([HistoryVotingPrecinct_2]<>' ',1,0)+
  7.         IIf([HistoryVotingPrecinct_3]<>' ',1,0)+
  8.         IIf([HistoryVotingPrecinct_4]<>' ',1,0)+
  9.         IIf([HistoryVotingPrecinct_5]<>' ',1,0)<3)
  10.   AND  (DateDiff('yyyy',[BirthDate],Date())<=40))
All the double-quotes (") have been changed to single-quotes ('). Although Access handles either, the SQL standards all say to use the single-quotes (See Quotes (') and Double-Quotes (") - Where and When to use them).

I removed all table references in your SQL except for the FROM clause. When there is only one record source it is so much easier to read and understand if they are omitted.

Please let us know how you get on with this and Welcome to Bytes!
Oct 6 '08 #9

missinglinq
Expert 2.5K+
P: 3,532
Curious, Ade! So what does IIF() return for the FalsePart if nothing is designated? Zero or Null?

Linq :0)>

P.S. When are we getting BOLD tags back? It's a handicap not being able to emphasize things, especially parts of code you need to highlight!
Oct 6 '08 #10

Expert Mod 2.5K+
P: 2,545
Hi Linq. In a query Access returns a null if the false part is left out from an IIF. I tested this using IsNull to see what the output was.

But as you pointed out it really is an expected argument and should be supplied. Trying to use it without a False clause from the immediate window in the VB editor generates a compiler error, as shown in the screenshot below.

(I miss the Bold tags too...)

-Stewart
Attached Images
 
Oct 6 '08 #11

NeoPa
Expert Mod 15k+
P: 31,707
I'm sorry guys. Questions are outstanding on the tags and I will try to get some information (or even better a fix) disseminated ASAP. This is not something I have any control over I'm afraid.

Anyway, as Stewart rightly says, a missing FALSE argument returns a Null. As my post made clear, this is ONLY true for SQL in Access. VBA is as per the help file and the FALSE parameter is mandatory there.

IIf() is also different in SQL in that it doesn't evaluate each side regardless (as the VBA one does). It works more efficiently in SQL and is generally safer to use.
Oct 6 '08 #12

missinglinq
Expert 2.5K+
P: 3,532
IIf() is also different in SQL in that it doesn't evaluate each side regardless (as the VBA one does). It works more efficiently in SQL and is generally safer to use.
Makes sense. Nulls are generally handled better in SQL than in VBA.

I have to say I'm not really enamoured of IIF(). I particularly dislike the popular trend towards nesting 29 IIFs in a single statement! Especially when the person doing it expects someone here to wade thru it and figure out what they did wrong! The things are almost incomprehensible.

Understand that I'm not talking about the usage shown here, but rather where a second IFF() is used as the FalsePart of the first IIF(), in a never ending daisy chain! Back in the days of 10mb hard drives, when storage was dear, it made sense to use the absolute smallest code possible to complete a task, often at the sake of clarity.Teams held contests to see who could perform a task with the least number of bytes being used. But those days are long past, and anything that makes the purpose of a piece of code less obvious today is to be shunned.

Linq ;0)>
Oct 6 '08 #13

NeoPa
Expert Mod 15k+
P: 31,707
No argument there Linq ;)

SQL is a funny one though, in that a comlex calculation is often the only way to achieve the result. Where in code (VBA) you can build up the result in different ways, SQL is often quite restricted in how you can express something.
Oct 7 '08 #14

Post your reply

Sign in to post your reply or Sign up for a free account.