Field Names in Functions (1 Viewer)

The Archn00b

Registered User.
Local time
Today, 08:06
Joined
Jun 26, 2013
Messages
76
Hello everyone,

I think this is a vba question at heart so I've posted this here.

Adding fields into a function has been a problem for me:

Code:
IIf((Count([qryADDR].[Fuel Type]>2), 'MPS', [qryADDR].[Fuel Type])

The logic I'm trying to create is the following: If the number of Fuel Types is greater than two, then the field will have the value 'MPS.' Otherwise, the field will just have the regular fuel type value. It's referencing this fuel type field in the function that is the problem. I have not been able to get this working.

Any ideas? Thanks!
 

MarkK

bit cruncher
Local time
Today, 08:06
Joined
Mar 17, 2004
Messages
8,178
VBA doesn't recognize query, table, or field names as valid identifiers like SQL does. You need to use a SQL command to open a recordset to work with table data in VBA.
 

MarkK

bit cruncher
Local time
Today, 08:06
Joined
Mar 17, 2004
Messages
8,178
Alternatively, you can call a function from SQL, but then you need to pass the data to the function as parameters . . .

in a query you might have . . .
Code:
CalculatedField: MyFunction([MyTable].[MyNumericField])

. . . and the companion function (in a standard module) . . .
Code:
Public Function MyFunction(NumberField As Single) as Single
    MyFunction = NumberField * 12
End Function

In this arrangement the query calls the function directly, and passes field values as parameters.

Hope this helps,
 

The Archn00b

Registered User.
Local time
Today, 08:06
Joined
Jun 26, 2013
Messages
76
VBA doesn't recognize query, table, or field names as valid identifiers like SQL does. You need to use a SQL command to open a recordset to work with table data in VBA.


Thanks for the reply. Is this true even if the field name is in a SQL Query?
 

andrewmrichards

Registered User.
Local time
Today, 15:06
Joined
Mar 15, 2012
Messages
18
Hi

Am I missing something?

If you're trying to count how many rows there are that meet your criteria, then you've got two choices... you could use a recordset and a SQL statement based on COUNT(). But from the code you've posted, it looks to me like you're either doing this in the query designer or just within a bit of VBA code, in which case you'll need DCount... Of course, it's perfectly possible that I'm misunderstanding your intent!

Something like this maybe?

If in a query,
Code:
IIf((DCount("[Fuel Type]","[qryADDR]")>2, 'MPS', [qryADDR].[Fuel Type])
assuming that the query called qryAddr is part of your query design, you should be able to reference a column within that query in this calculation.
And if in VBA code, say, behind a form, maybe something like
Code:
IIf((DCount("[Fuel Type]","[qryADDR]")>2, 'MPS', me.qryADDR_Fuel _Type])
Note that in this last example, you're referencing the field on the form, and you'll need to be guided by the compiler as to the exact sytax - it may be that you just need me.[Fuel Type] if there's only one table / query that this column could have come from.

Does this help, or am I way off the mark? Note that in either case there's no checking for the number of unique fuel types - but as your code didn't mention that you wanted anything along the lines of a SELECT DISTINCT I'm assuming that's not what you wanted...

Andrew
 

MarkK

bit cruncher
Local time
Today, 08:06
Joined
Mar 17, 2004
Messages
8,178
Is this true even if the field name is in a SQL Query?
Yes. SQL doesn't execute in VBA. That's what it looks like you are doing, is writing SQL code in VBA.

If VBA is the soldier, and SQL is the bullet, then you still need a gun. That will be DAO or ADO, which provide things like recordsets and querydefs.

Or you can do what Mr Richards is talking about, and use DCount() which is a shortcut provided by Access.
 

Users who are viewing this thread

Top Bottom