473,326 Members | 2,023 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Calculated field generates unexpected parameter prompt

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
13 5461
missinglinq
3,532 Expert 2GB
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
32,556 Expert Mod 16PB
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
jboisa
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
3,532 Expert 2GB
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
3,532 Expert 2GB
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
Stewart Ross
2,545 Expert Mod 2GB
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
32,556 Expert Mod 16PB
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
3,532 Expert 2GB
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
32,556 Expert Mod 16PB
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

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

Similar topics

8
by: MS | last post by:
Hello, I have a form with a LoanRequired field which accepts numeric data only. If user enters >14900 then I want the user to continue as normal with the default form. If user enters <1000 then...
1
by: Norbert Lieckfeldt | last post by:
MS Access 2002 here. I am just trying to set up a simple database for a friend who's an optician. Basically, all clients with address details, date of last eyetest and a drop-down combo box to...
7
by: Dave Brydon | last post by:
Access 2003 Several days ago, I deleted a field from a table, which was no longer required. Additionally, all associated elements for this field were also removed from the database, i.e. from...
5
by: Henrik | last post by:
The problem is (using MS Access 2003) I am unable to retrieve long strings (255 chars) from calculated fields through a recordset. The data takes the trip in three phases: 1. A custom public...
2
by: jcf378 | last post by:
hi all. I have a form which contains a calculated control ("days") that outputs the # of days between two dates (DateDiff command between the fields and ). However, when I click "Filter by...
0
by: siddu | last post by:
Hi All, I know how to create a parameter field and Formulafield. Please tell me How to use parameter field in my Report. I Created one parameter field with name.A window opened which taking...
3
by: fperri | last post by:
Hello, I have a calculated field in my query that uses a function to come up with the value. The function has various fields from the table used in the query passed into it as parameters. For some...
3
by: myemail.an | last post by:
I use Access 2007 and create queries with a number of calculated fields/expressions (I'm still a novice so please forgive me if my wording is imprecise...), like: MyCalculation = Field1 - Field2. ...
7
by: Mike Kent | last post by:
It's often useful for debugging to print something to stderr, and to route the error output to a file using '2>filename' on the command line. However, when I try that with a python script, all...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.