Passing parameter from Query to vba

[Marcie]

I have a table with field names like:

Present Status Q3
Your satisfaction Q4 etc...

when the field is populated it has one of many different answers
Present Status Q3 could have
Employed, part-time
Employed, Full-time
Not employeed but seeking
etc...

I started building IIf statements but they got very ugly very quickly and I
did see something about no more than 2 so I started trying to pass this
information into a module and cannot seem to get the paramaters to pass
In the query I have Status:WrkStat([Present Status Q3]) and in moudle I have

Public Function wrkstat([present status q3] as String)) As String

If Status Like "Employed, part-time%" Then
wrkstat = 1
End If
End Function

I cannot get the function to accept the field name. I did also try to do
something in query like Status[present status q3]) and then use that and
was still not succesful and that would be a ton of work because I have about
20 questions that I need to turn there answer into a 1, 2, 3, 4 etc based on
answer

 

[XPS350]

I have a table with field names like:

Present Status Q3
Your satisfaction Q4 etc...

when the field is populated it has one of many different answers
Present Status Q3  could have
Employed, part-time
Employed, Full-time
Not employeed but seeking
etc...

I started building IIf statements but they got very ugly very quickly andI
did see something about no more than 2 so I started trying to pass this
information into a module and cannot seem to get the paramaters to pass
In the query I have Status:WrkStat([Present Status Q3]) and in moudle I have

Public Function wrkstat([present status q3] as String)) As String

If Status Like "Employed, part-time%" Then
wrkstat = 1
End If
End Function

I cannot get the function to accept the field name.  I did also try to do
something in query like Status[present status q3]) and then use that and
was still not succesful and that would be a ton of work because I have about
20 questions that I need to turn there answer into a 1, 2, 3, 4 etc basedon
answer

I gues the problem here is 'Status'. That field does not refer to
anything.

You should try something like:
Public Function wrkstat(Status as String)) As String

If Status Like "Employed, part-time%" Then
wrkstat = "1"
End If
End Function

In a query you write:
SELECT wrkstat([Present Status Q3]) AS Stat FROM .....

But..................

IMHO this is still "ugly".
Why don't you make a table to define the relation between "full
status" and "status code"?


Groeten,

Peter
http://access.xps350.com

 

[John W. Vinson]

I have a table with field names like:

Present Status Q3
Your satisfaction Q4 etc...

when the field is populated it has one of many different answers
Present Status Q3 could have
Employed, part-time
Employed, Full-time
Not employeed but seeking
etc...

I started building IIf statements but they got very ugly very quickly and I
did see something about no more than 2 so I started trying to pass this
information into a module and cannot seem to get the paramaters to pass
In the query I have Status:WrkStat([Present Status Q3]) and in moudle I have

Public Function wrkstat([present status q3] as String)) As String

If Status Like "Employed, part-time%" Then
wrkstat = 1
End If
End Function

I cannot get the function to accept the field name. I did also try to do
something in query like Status[present status q3]) and then use that and
was still not succesful and that would be a ton of work because I have about
20 questions that I need to turn there answer into a 1, 2, 3, 4 etc based on
answer

Fieldnames in a table can (but generally shouldn't, there are many annoyances
from doing so) contain blanks; if they do, they must be delimited with square
brackets.

Variable names in VBA procedures CANNOT contain blanks.

Your statement

Public Function wrkstat([present status q3] as String)) As String

will not work, because you're attempting to define [present status q3] as a
variable name. No can do!

What's the rest of the code in wrkstat? Could you use

Public Function wrkstat(strStat As String) As String

and refer to strStat as the passed status text string?

If you do so, you could call it using

Status: wrkstat([present status q3])