Formula Query

[mesci]

Hi!

I'm currently transferring a load of data from an excel file to Access 2000 as part of designing our new database.

I need to recreate the following Excel formula so that it works in Access:

=IF(AND(G16>=15,M16>=15,Q16>=15,W16>=15),(G16*0.15+M16*0.15+Q16*0.3+W16*0.4),"")

With G16=[Case Study Actual Mark], M16=[Audit Actual Mark], Q16=[Exam Actual Mark] and W16=[Project Actual Mark]

Can anyone help me with this. I've searched through every message on this forum that looks like it might be useful but to no avail.

 

[AlanS]

Try this:

Result = IIf(([Case Study Actual Mark] >=15) And _
([Audit Actual Mark] >= 15) And _
([Exam Actual Mark] >= 15) And _
([Project Actual Mark] >= 15), _
([Case Study Actual Mark] * 0.15) + _
([Audit Actual Mark] * 0.15) + ([Exam Actual Mark] * 0.3) + _
([Project Actual Mark] * 0.4), "")

If Result is a text box on a form, this should work as is. If it is a VBA variable, it will need to be declared as a Variant so that it will accommodate both numeric and string values.

 

[mesci]

Cheers for your reply AlanS

I've tried it but it's coming up with the message that it contains an invalid syntax.

I've had a look at all the comma's and brackets but can't see anything wrong with what you gave me.

Any chance you could shed some light on the subject?

Cheers!

 

[AlanS]

The only possible problem I see is in the first line, where I typed ">=15" instead of ">= 15", but I don't think the lack of a space there is signficant. I can't see anything else wrong with it either. The only suggestions I have are to be sure to use Copy and Paste instead of re-typing the formula, and to try plugging it into the Criteria line of a query grid - the error message you get there may or may not be more helpful. Good luck!