Query acting up!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query and its acting up when I add the following column

Followup: IIf([vTracking]![Datecompleted]-[vTracking]![Date]<=2,"Completed
<=48",IIf([vTracking]![Datecompleted]-[vTracking]![Date]>2,"Completed >
48",IIf([vTracking]![Datefollowup]-[vTracking]![Date]<=2,"outstanding<=48",IIf([vTracking]![Datefollowup]-[vTracking]![Date]>2,"outstanding>48","Not
in Scope"))))

Seems like when I add the above column in the query, I am not able to update
and save the query. Why could this be happening?

Please help!
 
Seems like when I add the above column in the query, I am not able to update
and save the query. Why could this be happening?
Click to expand...

Looks like you may have some syntax errors. Let's unravel this:

Followup: IIf([vTracking]![Datecompleted]-[vTracking]![Date]<=2,
"Completed<=48",
IIf([vTracking]![Datecompleted]-[vTracking]![Date]>2,
"Completed >48",
IIf([vTracking]![Datefollowup]-[vTracking]![Date]<=2,
"outstanding<=48",
IIf([vTracking]![Datefollowup]-[vTracking]![Date]>2,
"outstanding>48",
"Not in Scope")
<missing FALSE operand>)
<missing FALSE operand>)
<missing FALSE operand>)

Each IIF must have three arguments: a logical expression; the value to
return if that expression is TRUE; and the value to return if it's
FALSE. All but your innermost IIF expressions lack the third operand.

You might want to use the Switch() function instead - it takes
operands in pairs, and goes through them one pair at a time. If the
first of the pair is TRUE it returns the second member of the pair,
but if False it goes on to the next pair:

Switch(
[vTracking]![Datecompleted]-[vTracking]![Date]<=2, "Completed<=48",
[vTracking]![Datecompleted]-[vTracking]![Date]>2, "Completed >48",
[vTracking]![Datefollowup]-[vTracking]![Date]>2, "outstanding>48",
True, "Not In Scope")


John W. Vinson[MVP]
 
This is what I have now after I added one line it is , but its still acting up!

followup: Switch(
[vTracking]![Datecompleted]-[vTracking]![Date]<=2,"Completed<=48",
[vTracking]![Datecompleted]-[vTracking]![Date]>2,"Completed >48",
[vTracking]![Datefollowup]-[vTracking]![Date]<=2,"outstanding<=48",
[vTracking]![Datefollowup]-[vTracking]![Date]>2,"outstanding>48",True,"Not
In Scope")

I have tried to copy the sql statements several times to a new query but
when I try save or exit out from the query, Access closes and nothing is
updated ot saved.....
It works though gives me what I want to see.... I have tried to compact and
repair the database but its still misbehaving! So am not sure what it the
problem...

OJ!


John Vinson said:
Seems like when I add the above column in the query, I am not able to update
and save the query. Why could this be happening?
Click to expand...

Looks like you may have some syntax errors. Let's unravel this:

Followup: IIf([vTracking]![Datecompleted]-[vTracking]![Date]<=2,
"Completed<=48",
IIf([vTracking]![Datecompleted]-[vTracking]![Date]>2,
"Completed >48",
IIf([vTracking]![Datefollowup]-[vTracking]![Date]<=2,
"outstanding<=48",
IIf([vTracking]![Datefollowup]-[vTracking]![Date]>2,
"outstanding>48",
"Not in Scope")
<missing FALSE operand>)
<missing FALSE operand>)
<missing FALSE operand>)

Each IIF must have three arguments: a logical expression; the value to
return if that expression is TRUE; and the value to return if it's
FALSE. All but your innermost IIF expressions lack the third operand.

You might want to use the Switch() function instead - it takes
operands in pairs, and goes through them one pair at a time. If the
first of the pair is TRUE it returns the second member of the pair,
but if False it goes on to the next pair:

Switch(
[vTracking]![Datecompleted]-[vTracking]![Date]<=2, "Completed<=48",
[vTracking]![Datecompleted]-[vTracking]![Date]>2, "Completed >48",
[vTracking]![Datefollowup]-[vTracking]![Date]>2, "outstanding>48",
True, "Not In Scope")


John W. Vinson[MVP]
Click to expand...
 
I have tried to copy the sql statements several times to a new query but
when I try save or exit out from the query, Access closes and nothing is
updated ot saved.....
Click to expand...

Sounds like your database systems tables are corrupt.

Try creating a new, empty database; use File... Get External Data...
Import to import everything BUT this query. Create a new query and
copy and paste the SQL of the query into a new window. There's nothing
in this query which should cause Access to close!

John W. Vinson[MVP]
 
I created a new datadase and imported the everythin else apart from the the
queries and it still kicked me out even after compacting and repairing it!

I dont know why!
 
I created a new datadase and imported the everythin else apart from the the
queries and it still kicked me out even after compacting and repairing it!

I dont know why!
Click to expand...

Boy, it's getting tough.

My next (unpleasant) suggestion would be to COMPLETELY uninstall
Access (use Windows... Add and Remove Programs, and then a registry
scrubber), and reinstall it. Be sure to connect to Microsoft's Office
Update website and install ALL service packs. It seems you may have a
corrupt Access program, not just a bad database.

John W. Vinson[MVP]
 
Back
Top