Using User defined functions in query WHERE clauses

[h0pal0ng]

Hi, I have a problem and I'm hoping people here can help me.

I have a Access 2007 Database with a table with a field Surname. I have
added a module to the project and wrote a public Soundex function which takes
a string input and processes it into a 4 character Soundex code.

What I want to be able to do is create a query that will bring back soundex
matches in the table.

Two possible ways of doing this come to my mind - either have a calculated
field in the table itself and then just query the field as normal - but I'm
not sure how to do trhis in Access (or even if it's possible) or create a
query which has the calculation included and then access the query as if it
was a table. I've tried the latter method but then I create the last query
and try to put the WHERE clause in:

WHERE ((([Query - Personal Member Details with
soundex].[SoundName])=SOUNDEX([Enter Member Name])))

I get the error message "This expression is typed incorrectly, or it is too
complex to be evaluated. For example, a numeric expression may contain too
many complicated elements. Try simplifying the expression by assigning parts
of the expression to variables. (Error 3071)"

Now if I just add a field to the table and manually populate it, a similar
style query directly on the table seems to work - no error and a valid
result. Using this method is possible but I would then need to know the best
way of getting the SoundName field to automatically repopulate when the
Surname field is changed.

So I hope to hear soon from you.

Thanks for all your help,

Shane

 

[Douglas J. Steele]

That looks as though it should work, so let's do some trouble shooting.

Assuming that you have an entry where SoundName equals say S133, what
happens if you use

WHERE ((([Query - Personal Member Details with
soundex].[SoundName])='S133'))

Are you getting the [Enter Member Name] prompt, or does the error come back
immediately?

 

[h0pal0ng]

WHERE ((([Query - Personal Member Details with soundex].SoundName)="B525"));

fails with data type mismatch error. also if I use single quotes. Similarly
using variable entry, you do get prompted for the value but it then fails
with the data type mismatch error.

WHERE ((([Query - Personal Member Details with soundex].SoundName)=[Enter
value]));

Yet surely the query column is a text field considering that SOUNDEX is
defined as a string function?


--
Thanks for all your help,

Shane

That looks as though it should work, so let's do some trouble shooting.

Assuming that you have an entry where SoundName equals say S133, what
happens if you use

WHERE ((([Query - Personal Member Details with
soundex].[SoundName])='S133'))

Are you getting the [Enter Member Name] prompt, or does the error come back
immediately?


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi, I have a problem and I'm hoping people here can help me.

I have a Access 2007 Database with a table with a field Surname. I have
added a module to the project and wrote a public Soundex function which
takes
a string input and processes it into a 4 character Soundex code.

What I want to be able to do is create a query that will bring back
soundex
matches in the table.

Two possible ways of doing this come to my mind - either have a calculated
field in the table itself and then just query the field as normal - but
I'm
not sure how to do trhis in Access (or even if it's possible) or create a
query which has the calculation included and then access the query as if
it
was a table. I've tried the latter method but then I create the last query
and try to put the WHERE clause in:

WHERE ((([Query - Personal Member Details with
soundex].[SoundName])=SOUNDEX([Enter Member Name])))

I get the error message "This expression is typed incorrectly, or it is
too
complex to be evaluated. For example, a numeric expression may contain too
many complicated elements. Try simplifying the expression by assigning
parts
of the expression to variables. (Error 3071)"

Now if I just add a field to the table and manually populate it, a similar
style query directly on the table seems to work - no error and a valid
result. Using this method is possible but I would then need to know the
best
way of getting the SoundName field to automatically repopulate when the
Surname field is changed.

So I hope to hear soon from you.

Thanks for all your help,

Shane

 

[Douglas J. Steele]

Sorry, only you can see what's in the field!

What happens if you wrap the CStr function around the field?

WHERE ((CStr([Query - Personal Member Details with
soundex].SoundName)=[Enter value]));


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

WHERE ((([Query - Personal Member Details with
soundex].SoundName)="B525"));

fails with data type mismatch error. also if I use single quotes.
Similarly
using variable entry, you do get prompted for the value but it then fails
with the data type mismatch error.

WHERE ((([Query - Personal Member Details with soundex].SoundName)=[Enter
value]));

Yet surely the query column is a text field considering that SOUNDEX is
defined as a string function?


--
Thanks for all your help,

Shane

That looks as though it should work, so let's do some trouble shooting.

Assuming that you have an entry where SoundName equals say S133, what
happens if you use

WHERE ((([Query - Personal Member Details with
soundex].[SoundName])='S133'))

Are you getting the [Enter Member Name] prompt, or does the error come
back
immediately?


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi, I have a problem and I'm hoping people here can help me.

I have a Access 2007 Database with a table with a field Surname. I have
added a module to the project and wrote a public Soundex function which
takes
a string input and processes it into a 4 character Soundex code.

What I want to be able to do is create a query that will bring back
soundex
matches in the table.

Two possible ways of doing this come to my mind - either have a
calculated
field in the table itself and then just query the field as normal - but
I'm
not sure how to do trhis in Access (or even if it's possible) or create
a
query which has the calculation included and then access the query as
if
it
was a table. I've tried the latter method but then I create the last
query
and try to put the WHERE clause in:

WHERE ((([Query - Personal Member Details with
soundex].[SoundName])=SOUNDEX([Enter Member Name])))

I get the error message "This expression is typed incorrectly, or it is
too
complex to be evaluated. For example, a numeric expression may contain
too
many complicated elements. Try simplifying the expression by assigning
parts
of the expression to variables. (Error 3071)"

Now if I just add a field to the table and manually populate it, a
similar
style query directly on the table seems to work - no error and a valid
result. Using this method is possible but I would then need to know the
best
way of getting the SoundName field to automatically repopulate when the
Surname field is changed.

So I hope to hear soon from you.

Thanks for all your help,

Shane

 

[h0pal0ng]

Douglas,

Thanks for all your help. I was able to solve my problem a different way by
adding the field to the table, manually populating it, adding code to
automatically forward any changes when editing and then using this table
field in the query - which works fine. I figured out that the code which was
failing on the 1st attempt needed to use the Value property, not the text as
I originally had. So the database is now working as I want it so I'm happy.

Hopefully this thread can help others. Again thanks for all your help,

Shane

Sorry, only you can see what's in the field!

What happens if you wrap the CStr function around the field?

WHERE ((CStr([Query - Personal Member Details with
soundex].SoundName)=[Enter value]));


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

WHERE ((([Query - Personal Member Details with
soundex].SoundName)="B525"));

fails with data type mismatch error. also if I use single quotes.
Similarly
using variable entry, you do get prompted for the value but it then fails
with the data type mismatch error.

WHERE ((([Query - Personal Member Details with soundex].SoundName)=[Enter
value]));

Yet surely the query column is a text field considering that SOUNDEX is
defined as a string function?


--
Thanks for all your help,

Shane

That looks as though it should work, so let's do some trouble shooting.

Assuming that you have an entry where SoundName equals say S133, what
happens if you use

WHERE ((([Query - Personal Member Details with
soundex].[SoundName])='S133'))

Are you getting the [Enter Member Name] prompt, or does the error come
back
immediately?


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi, I have a problem and I'm hoping people here can help me.

I have a Access 2007 Database with a table with a field Surname. I have
added a module to the project and wrote a public Soundex function which
takes
a string input and processes it into a 4 character Soundex code.

What I want to be able to do is create a query that will bring back
soundex
matches in the table.

Two possible ways of doing this come to my mind - either have a
calculated
field in the table itself and then just query the field as normal - but
I'm
not sure how to do trhis in Access (or even if it's possible) or create
a
query which has the calculation included and then access the query as
if
it
was a table. I've tried the latter method but then I create the last
query
and try to put the WHERE clause in:

WHERE ((([Query - Personal Member Details with
soundex].[SoundName])=SOUNDEX([Enter Member Name])))

I get the error message "This expression is typed incorrectly, or it is
too
complex to be evaluated. For example, a numeric expression may contain
too
many complicated elements. Try simplifying the expression by assigning
parts
of the expression to variables. (Error 3071)"

Now if I just add a field to the table and manually populate it, a
similar
style query directly on the table seems to work - no error and a valid
result. Using this method is possible but I would then need to know the
best
way of getting the SoundName field to automatically repopulate when the
Surname field is changed.

So I hope to hear soon from you.

Thanks for all your help,

Shane