iF THEN VBA STATMENT

[GCLIFTON]

Need help with vba/sql if then statment

See the below. In the Tracker. Table. which is populated by a form having check boxes. I would like in query how to write the statement If Columns by unit, starting with [1 Demo], has a -1 then give me whats in [Tracker_Budget_Upload_. [1 Demo] that corresponds with that unit



Tracker


Tracker_Budget_upload_


This is what i attempted- Demo: IIF(Tracker.[1 Demo]="-1",Tracker_Budget_Upload_.[1 Demo]

[pbaldy]

That's incomplete, and if it's a Yes/No field you wouldn't want the quotes. Try

Demo: IIF(Tracker.[1 Demo]=-1,Tracker_Budget_Upload_.[1 Demo]. 0)

Or whatever you want for the False argument.

[ranman256]

You can do this with a query, no vba.
select table.tracker from table where table2.[1 demo]

[GCLIFTON]

OK I Recieiving the false statement when i changed it to "" but i am not receive any value of 100 for 1932-3

[GCLIFTON]

That is how i am doing this. In Design view in The Field column. Are you stating something different that i should do

[pbaldy]

Perhaps it isn't a Yes/No field. Try with the quotes:

Demo: IIF(Tracker.[1 Demo]="-1",Tracker_Budget_Upload_.[1 Demo]. "")

[John_G]

Just to verify - you do have a query with both tables in it, joined on the "combine" field?

You could try using a True/False comparison, since that is what checkboxes give you:

Demo: IIF(Tracker.[1 Demo]=True,Tracker_Budget_Upload_.[1 Demo], "")

[GCLIFTON]

John that was it. I totally forgot to join the tables duh lol. And quick questions for any one to answer, why does this sql doesn't work. and what should it be. Total:Sum([window]+[Demo]+[MEP Rough]+[Bathtub]) I have tried with a , and + . With the + i get the numbers joined together. For instance 49.7657+100.00 will equal 49.7657100. Not 149.555. And then when i use , i get an error stating i cant not sum fields with "*"

[pbaldy]

I can tell from the way they left-align that your fields are text. That will cause them to concatenate rather than add. If they are supposed to hold numeric values, they should have a numeric data type. A kludge workaround would be using the appropriate conversion function around each (CCur(), CDbl(), etc).

[GCLIFTON]

Ok maybe i am missing something, This is what i tried. Well first i went back to the table and made the data currency. Then i tried the following and the results

Total: (CDbl([Demo]+[Window]+[MEP Rough]))


Total: ([Demo]+[Window]+[MEP Rough])


Total: (CCur([Demo]+[Window]+[MEP Rough]))

[pbaldy]

I mentioned around each, so more like:

Total: CDbl([Demo])+CDbl([Window])+CDbl([MEP Rough])

given your de-normalized design with what must be Nulls:

Total: CDbl(Nz([Demo],0))+CDbl(Nz([Window],0))+CDbl(Nz([MEP Rough],0))

[GCLIFTON]

That worked 2 question left

1. Why are there #Error signs. I have been trying to figure out why and get rid of them


And how can i get rid of all of the columns to the right of Total1. Like 1demo, and 3 Windows. I dont even have them in my design view Fields

[pbaldy]

What's the SQL? You may have a "*" in there.

[GCLIFTON]

Just figured out the error problem. So is there a way to romove the columns that i ddin't select

[GCLIFTON]

This one

SELECT Tracker.Combine, Tracker.[Floor Plan], Tracker.Type, Tracker.Scope, IIf([Tracker].[3 Windows]="-1",[Tracker_Budget_Upload_].[3# Windows],0) AS Window, IIf([Tracker].[1 Demo]="-1",[Tracker_Budget_Upload_].[1# Demo],0) AS Demo, IIf([Tracker].[4 Mep Rough]="-1",[Tracker_Budget_Upload_].[4# Mep Rough],0) AS [MEP Rough], IIf([Tracker].[6 Bathtub]="-1",[Tracker_Budget_Upload_].[6# Bathtub],0) AS Bathtub, CDbl(Nz([Demo],0))+CDbl(Nz([Window],0))+CDbl(Nz([MEP Rough],0)) AS Total1, *
FROM Tracker INNER JOIN Tracker_Budget_Upload_ ON Tracker.Combine = Tracker_Budget_Upload_.Combine;