I figured out how to create basic formulas in queries but I can't get an "If" formula to work. I can do them in excel no problem but the naming convention is different in access. Anyone know how to do it? And in a query I can't get a column that I made a forumla to show up as a "$" amount. Can anyone help?

i'm pretty good with access. dont currently have it installed anywhere to test anything out at the moment (don't really do a lot of access development anymore, mysql, perl, ajax and php mostly these days).

what is the "if" trying to do. is it a conditional for a query or some kind of if format for a column view? if it's a query just post the entire query txt here and I'll take a look at it, see if anything jumps out at me.

Plate Change Cost: IIf([Plant Produced]=([GreenBay]),[# of Plate Changes]*550,[# of Plate Changes]*325)

That is the formula I have set up. This is what I want it to do. I have a table set up with Order information which contains the order number and the plant that the order is running in. I have another table set up with # of plates that are changing (im in the printing business). So what I want it to do is set it up so that if I specify the plant as Green Bay it will multiply the # of plates by 550, if it is something other than Green Bay it will muliply the # of plates by 325. And after all that, I need it to show up as a dollar amount. That help at all?

Also, after I save the query and I go to open it, it tells me to "Enter Parameter Value". I can enter something (not sure what), or I can just click OK. And it spits out info but it is not accurate. I have no idea what Parameter I need to enter, if any.

looks pretty simple. let me think about it a moment. i might need to install access to see what's really going on here.

so as it is right now, does it produce the right number if plant = greenbay, or does the bail out clause (*325) always kick in no matter what?

Right now it is giving the *325 for all values regardless of plant. I tried whiching the 550 and 325 around to see if that would help, tried chaning =green bay to <>green bay. Couldn't get anything to work. If you can help me I would appreciate it.

the way I'm reading that statement, it's only looking to greenbay, therefor everything is true, and the false will never hit

BTW, when are you running this statement?

the second coma should be the false statement. i do these hings all the time in excel but it is set up differently in access. i bet it is the littlest thing i am missing that is makingit not work

what do you mean when?

how big is the access file?

not big at all. there is actually no data in it yet since i am just creating it from scratch. i just put in some test data to see if the loic works

it doesn't look like the if conditional will fire.

if something=somethingElse may not fire because the = is being evaluated as an assingment operator, not a comparison operator. it's been a while with access for me so i can't remember exactly but have you tried == instead of =.

Usually the == will do a comparison, while a single = will do an assignment.

also, is the column name ambiguous? should the tableName.columnName notation be used instead? just a thought. sounds like Kagen has more recent access experience than I do though.

I don't currently use it for anything. just trying to help.

not big at all. there is actually no data in it yet since i am just creating it from scratch. i just put in some test data to see if the loic works

hmm. that looks like the same post as earlier. did you look at the access language reference to see if == is used for comparison and = is used for assignment? or the fully qualified table.Column notation?

i will try the == tom when I get to work. i dont think you need the table name b/c with the formula you just type it in. Thats what someone else told me anyway. Ill post back tom when i have more time to play around with it.

either way, this looks pretty simple. hopefully we can help you get it sorted.

where else you gonna go for database programming help? serato forums of course.

the other day someone helped explain to me how to replace a broken faucet. all right here on the serato forums.

Hahaha...its true. I come here for everything. With such a wide array of people on this forum, you can really get info on anything you need. DJ related or not.

I tried the == thing and it doesnt work. It gives me a syntax error. Kagen, check your emails!

(pressing F1 in Access)

In the following example, if the value in cell A10 is 100, then Logical_test is TRUE, and the total value for the range B5:B15 is calculated. Otherwise, Logical_test is FALSE, and empty text ("") is returned that blanks the cell that contains the IF function.

IF(A10=100,SUM(B5:B15),"")

this thread is too intellectual for me :( I know SQL! :)

Yea, thats how you would do it in Excel, but access is different. You need to put the table you are pulling from and the actual field. It is not as easy as it is to do in excel.

ok then, i've helped as much as i can without installing access somewhere. without a windows pc handy, that's not easy.

I hear ya man. And thanks again for the help. It is definitely appreciated. I sent it over to Kagen. Hopefully by looking at it, he can help me out.

email sent back.

I cracked in to one of those emails... I opened your DB - I see where you pulled that logic from. Lemme dig some more and see what's actually going on.

to get the format to "Currency" - highlight the row, right click properties and set the format to currency.

Also - I HIGHLY suggest you rename your tables and queries, this will save your ass when they get more complicated.. take out the spaces also makes it easier to read... and you still sort normally.
example:

qry_OrderInformationQuery
qry_AdditionalOrderCharges

now to figure out the stupid Iff..

Thanks man. Got the currency thing. That was helpful.

It is going to be a really simple database, so it won't get too complicated as time goes on but I do see where you are going with the naming convention. I will def do that once I get it working. At least you can see where I am going with things now so it should be easier to work for you.

you can rename on-the-fly - nothing bad will happen. I did it over here already.

Yea yea, Thats what I am going to do.

that's what they all say

nope - first time I've heard that.. no really.

Haha. It seriously is, I am setting it up for this one guy and for the reports he wants to run. Thats it!

I hope!