Access not accepting functions in queries

[Simon Baker]

This morning, after years of successful operation, a series of queries using
functions as parameters ceased to work. To illustrate the problem, I tried
the following SQL query:

SELECT ClientContact.[Client ref], ClientContact.Analyst,
ClientContact.Ttype, ClientContact.Date
FROM ClientContact
WHERE (((ClientContact.Ttype)="voicemail") AND
((ClientContact.Date)=Format(Now(),"dd/mm/yyyy")));

which generates the error 'Function is not available in query expression'

Changing it to

SELECT ClientContact.[Client ref], ClientContact.Analyst,
ClientContact.Ttype, ClientContact.Date
FROM ClientContact
WHERE (((ClientContact.Ttype)="voicemail") AND
((ClientContact.Date)=#1/29/2007#));

works fine. I suspect our IT department has released a patch that has
screwed something up, but cannot pin it down. Has anyone else seen a similar
problem? Needless to say, this has caused untold problems! I am using Access
2003 (v. 11.6566.8107) SP2 on XP Professional v. 5.1.2600 SP2

Thanks in advance


Simon

 

[Allen Browne]

Simon, change the last line of your query statement to:
(ClientContact.[Date] = Date());

If that still fails, you have an issue with Sandbox mode. For details, see:
http://office.microsoft.com/assista...3&CTT=8&Origin=EC011081751033&Product=acc2003

There are several pitfalls with the expression as you have it:
1. Date is a reserved word in both JET and VBA.
Access 2007 warns you if you create a field with this name.
For a list of other words to avoid as field names, see:
Problem names and reserved words
at:
http://allenbrowne.com/AppIssueBadWord.html
(It's a significant list.)

2. Now() returns the date and time.
Judging from your Format() expression, you wanted the date only, so use
Date().

3. The Format() function returns a string. Assuming that your Date field is
a Date/Time type, you have now asked JET to match a date against a string.
Results are not reliable.

4. Where you do want a literal date in the string, delimit with # and format
as US (as in the example that works.) So if there was a valid reason for
including a literal date string, you would use:
Format(Date(), "\#mm\/dd\/yyyy\#")
Explanation in:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

 

[Simon Baker]

Thank for the advice Alan, but unfortunately the problem persists. If I
change Now() to Date(), it still happens, and also change
ClientContact.[Date] to ClientContact.conDate makes no difference. I also
think I have disabled Sandbox mode to no avail. Any other suggestions?

thanks


Simon

Simon, change the last line of your query statement to:
(ClientContact.[Date] = Date());

If that still fails, you have an issue with Sandbox mode. For details, see:
http://office.microsoft.com/assista...3&CTT=8&Origin=EC011081751033&Product=acc2003

There are several pitfalls with the expression as you have it:
1. Date is a reserved word in both JET and VBA.
Access 2007 warns you if you create a field with this name.
For a list of other words to avoid as field names, see:
Problem names and reserved words
at:
http://allenbrowne.com/AppIssueBadWord.html
(It's a significant list.)

2. Now() returns the date and time.
Judging from your Format() expression, you wanted the date only, so use
Date().

3. The Format() function returns a string. Assuming that your Date field is
a Date/Time type, you have now asked JET to match a date against a string.
Results are not reliable.

4. Where you do want a literal date in the string, delimit with # and format
as US (as in the example that works.) So if there was a valid reason for
including a literal date string, you would use:
Format(Date(), "\#mm\/dd\/yyyy\#")
Explanation in:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

This morning, after years of successful operation, a series of queries
using
functions as parameters ceased to work. To illustrate the problem, I tried
the following SQL query:

SELECT ClientContact.[Client ref], ClientContact.Analyst,
ClientContact.Ttype, ClientContact.Date
FROM ClientContact
WHERE (((ClientContact.Ttype)="voicemail") AND
((ClientContact.Date)=Format(Now(),"dd/mm/yyyy")));

which generates the error 'Function is not available in query expression'

Changing it to

SELECT ClientContact.[Client ref], ClientContact.Analyst,
ClientContact.Ttype, ClientContact.Date
FROM ClientContact
WHERE (((ClientContact.Ttype)="voicemail") AND
((ClientContact.Date)=#1/29/2007#));

works fine. I suspect our IT department has released a patch that has
screwed something up, but cannot pin it down. Has anyone else seen a
similar
problem? Needless to say, this has caused untold problems! I am using
Access
2003 (v. 11.6566.8107) SP2 on XP Professional v. 5.1.2600 SP2

Thanks in advance


Simon

 

[Gary Walter]

You don't say whether you checked References, i.e,
deleted a Reference (especially if "Missing"),
then added it back

Thank for the advice Alan, but unfortunately the problem persists. If I
change Now() to Date(), it still happens, and also change
ClientContact.[Date] to ClientContact.conDate makes no difference. I also
think I have disabled Sandbox mode to no avail. Any other suggestions?

thanks


Simon

Simon, change the last line of your query statement to:
(ClientContact.[Date] = Date());

If that still fails, you have an issue with Sandbox mode. For details,
see:
http://office.microsoft.com/assista...3&CTT=8&Origin=EC011081751033&Product=acc2003

There are several pitfalls with the expression as you have it:
1. Date is a reserved word in both JET and VBA.
Access 2007 warns you if you create a field with this name.
For a list of other words to avoid as field names, see:
Problem names and reserved words
at:
http://allenbrowne.com/AppIssueBadWord.html
(It's a significant list.)

2. Now() returns the date and time.
Judging from your Format() expression, you wanted the date only, so use
Date().

3. The Format() function returns a string. Assuming that your Date field
is
a Date/Time type, you have now asked JET to match a date against a
string.
Results are not reliable.

4. Where you do want a literal date in the string, delimit with # and
format
as US (as in the example that works.) So if there was a valid reason for
including a literal date string, you would use:
Format(Date(), "\#mm\/dd\/yyyy\#")
Explanation in:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

This morning, after years of successful operation, a series of queries
using
functions as parameters ceased to work. To illustrate the problem, I
tried
the following SQL query:

SELECT ClientContact.[Client ref], ClientContact.Analyst,
ClientContact.Ttype, ClientContact.Date
FROM ClientContact
WHERE (((ClientContact.Ttype)="voicemail") AND
((ClientContact.Date)=Format(Now(),"dd/mm/yyyy")));

which generates the error 'Function is not available in query
expression'

Changing it to

SELECT ClientContact.[Client ref], ClientContact.Analyst,
ClientContact.Ttype, ClientContact.Date
FROM ClientContact
WHERE (((ClientContact.Ttype)="voicemail") AND
((ClientContact.Date)=#1/29/2007#));

works fine. I suspect our IT department has released a patch that has
screwed something up, but cannot pin it down. Has anyone else seen a
similar
problem? Needless to say, this has caused untold problems! I am using
Access
2003 (v. 11.6566.8107) SP2 on XP Professional v. 5.1.2600 SP2

Thanks in advance


Simon

 

[John Spencer]

To do its job, Access makes use of various external program and object
libraries. If you move a database from one machine to another, these
references may be "broken".

Perhaps you have a missing references problem.

Here are MVP Doug Steele's instructions

*** Quote ***

Any time functions that previously worked suddenly don't, the first thing to
suspect is a references problem.

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

For far more than you could ever want to know about this problem, check out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

Just so you know: the problem will occur even if the library that contains
the specific function that's failing doesn't have a problem.

**** End Quote ****

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thank for the advice Alan, but unfortunately the problem persists. If I
change Now() to Date(), it still happens, and also change
ClientContact.[Date] to ClientContact.conDate makes no difference. I also
think I have disabled Sandbox mode to no avail. Any other suggestions?

thanks


Simon

Simon, change the last line of your query statement to:
(ClientContact.[Date] = Date());

If that still fails, you have an issue with Sandbox mode. For details,
see:
http://office.microsoft.com/assista...3&CTT=8&Origin=EC011081751033&Product=acc2003

There are several pitfalls with the expression as you have it:
1. Date is a reserved word in both JET and VBA.
Access 2007 warns you if you create a field with this name.
For a list of other words to avoid as field names, see:
Problem names and reserved words
at:
http://allenbrowne.com/AppIssueBadWord.html
(It's a significant list.)

2. Now() returns the date and time.
Judging from your Format() expression, you wanted the date only, so use
Date().

3. The Format() function returns a string. Assuming that your Date field
is
a Date/Time type, you have now asked JET to match a date against a
string.
Results are not reliable.

4. Where you do want a literal date in the string, delimit with # and
format
as US (as in the example that works.) So if there was a valid reason for
including a literal date string, you would use:
Format(Date(), "\#mm\/dd\/yyyy\#")
Explanation in:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

This morning, after years of successful operation, a series of queries
using
functions as parameters ceased to work. To illustrate the problem, I
tried
the following SQL query:

SELECT ClientContact.[Client ref], ClientContact.Analyst,
ClientContact.Ttype, ClientContact.Date
FROM ClientContact
WHERE (((ClientContact.Ttype)="voicemail") AND
((ClientContact.Date)=Format(Now(),"dd/mm/yyyy")));

which generates the error 'Function is not available in query
expression'

Changing it to

SELECT ClientContact.[Client ref], ClientContact.Analyst,
ClientContact.Ttype, ClientContact.Date
FROM ClientContact
WHERE (((ClientContact.Ttype)="voicemail") AND
((ClientContact.Date)=#1/29/2007#));

works fine. I suspect our IT department has released a patch that has
screwed something up, but cannot pin it down. Has anyone else seen a
similar
problem? Needless to say, this has caused untold problems! I am using
Access
2003 (v. 11.6566.8107) SP2 on XP Professional v. 5.1.2600 SP2

Thanks in advance


Simon

 

[Simon Baker]

John, Gary

I have now tried that and it makes no difference. I think it may be some
Sandbox mode issue (as Allen suggested), but cannot seem to determine whether
that is on or off (changing it seems to make no difference).

In the mean time, I have come up with a temporary solution of setting the
querydefs as required with the requiste values rather than functions, but I
would still be very keen to find out what on earth happened

Thanks again for all your help

Simon

To do its job, Access makes use of various external program and object
libraries. If you move a database from one machine to another, these
references may be "broken".

Perhaps you have a missing references problem.

Here are MVP Doug Steele's instructions

*** Quote ***

Any time functions that previously worked suddenly don't, the first thing to
suspect is a references problem.

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

For far more than you could ever want to know about this problem, check out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

Just so you know: the problem will occur even if the library that contains
the specific function that's failing doesn't have a problem.

**** End Quote ****

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thank for the advice Alan, but unfortunately the problem persists. If I
change Now() to Date(), it still happens, and also change
ClientContact.[Date] to ClientContact.conDate makes no difference. I also
think I have disabled Sandbox mode to no avail. Any other suggestions?

thanks


Simon

Simon, change the last line of your query statement to:
(ClientContact.[Date] = Date());

If that still fails, you have an issue with Sandbox mode. For details,
see:
http://office.microsoft.com/assista...3&CTT=8&Origin=EC011081751033&Product=acc2003

There are several pitfalls with the expression as you have it:
1. Date is a reserved word in both JET and VBA.
Access 2007 warns you if you create a field with this name.
For a list of other words to avoid as field names, see:
Problem names and reserved words
at:
http://allenbrowne.com/AppIssueBadWord.html
(It's a significant list.)

2. Now() returns the date and time.
Judging from your Format() expression, you wanted the date only, so use
Date().

3. The Format() function returns a string. Assuming that your Date field
is
a Date/Time type, you have now asked JET to match a date against a
string.
Results are not reliable.

4. Where you do want a literal date in the string, delimit with # and
format
as US (as in the example that works.) So if there was a valid reason for
including a literal date string, you would use:
Format(Date(), "\#mm\/dd\/yyyy\#")
Explanation in:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

This morning, after years of successful operation, a series of queries
using
functions as parameters ceased to work. To illustrate the problem, I
tried
the following SQL query:

SELECT ClientContact.[Client ref], ClientContact.Analyst,
ClientContact.Ttype, ClientContact.Date
FROM ClientContact
WHERE (((ClientContact.Ttype)="voicemail") AND
((ClientContact.Date)=Format(Now(),"dd/mm/yyyy")));

which generates the error 'Function is not available in query
expression'

Changing it to

SELECT ClientContact.[Client ref], ClientContact.Analyst,
ClientContact.Ttype, ClientContact.Date
FROM ClientContact
WHERE (((ClientContact.Ttype)="voicemail") AND
((ClientContact.Date)=#1/29/2007#));

works fine. I suspect our IT department has released a patch that has
screwed something up, but cannot pin it down. Has anyone else seen a
similar
problem? Needless to say, this has caused untold problems! I am using
Access
2003 (v. 11.6566.8107) SP2 on XP Professional v. 5.1.2600 SP2

Thanks in advance


Simon

 

[Simon Baker]

Guys,

I have resolved the problem...and it was related to references. On Friday, I
upgraded my version of the Redemption dll and unwittingly had it in two
locations, one used for my machine in the C:\ drive and one on a seprate
drive for the use of the other users. Uninstalling it on my machine and
pointing to the other location has fixed the problem. Quite how Redemption
should interfere in such a way is beyond me!

Yet again this newsgroup provides first-class, rapid advice- many thanks to
all.

Simon

To do its job, Access makes use of various external program and object
libraries. If you move a database from one machine to another, these
references may be "broken".

Perhaps you have a missing references problem.

Here are MVP Doug Steele's instructions

*** Quote ***

Any time functions that previously worked suddenly don't, the first thing to
suspect is a references problem.

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

For far more than you could ever want to know about this problem, check out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

Just so you know: the problem will occur even if the library that contains
the specific function that's failing doesn't have a problem.

**** End Quote ****

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thank for the advice Alan, but unfortunately the problem persists. If I
change Now() to Date(), it still happens, and also change
ClientContact.[Date] to ClientContact.conDate makes no difference. I also
think I have disabled Sandbox mode to no avail. Any other suggestions?

thanks


Simon

Simon, change the last line of your query statement to:
(ClientContact.[Date] = Date());

If that still fails, you have an issue with Sandbox mode. For details,
see:
http://office.microsoft.com/assista...3&CTT=8&Origin=EC011081751033&Product=acc2003

There are several pitfalls with the expression as you have it:
1. Date is a reserved word in both JET and VBA.
Access 2007 warns you if you create a field with this name.
For a list of other words to avoid as field names, see:
Problem names and reserved words
at:
http://allenbrowne.com/AppIssueBadWord.html
(It's a significant list.)

2. Now() returns the date and time.
Judging from your Format() expression, you wanted the date only, so use
Date().

3. The Format() function returns a string. Assuming that your Date field
is
a Date/Time type, you have now asked JET to match a date against a
string.
Results are not reliable.

4. Where you do want a literal date in the string, delimit with # and
format
as US (as in the example that works.) So if there was a valid reason for
including a literal date string, you would use:
Format(Date(), "\#mm\/dd\/yyyy\#")
Explanation in:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

This morning, after years of successful operation, a series of queries
using
functions as parameters ceased to work. To illustrate the problem, I
tried
the following SQL query:

SELECT ClientContact.[Client ref], ClientContact.Analyst,
ClientContact.Ttype, ClientContact.Date
FROM ClientContact
WHERE (((ClientContact.Ttype)="voicemail") AND
((ClientContact.Date)=Format(Now(),"dd/mm/yyyy")));

which generates the error 'Function is not available in query
expression'

Changing it to

SELECT ClientContact.[Client ref], ClientContact.Analyst,
ClientContact.Ttype, ClientContact.Date
FROM ClientContact
WHERE (((ClientContact.Ttype)="voicemail") AND
((ClientContact.Date)=#1/29/2007#));

works fine. I suspect our IT department has released a patch that has
screwed something up, but cannot pin it down. Has anyone else seen a
similar
problem? Needless to say, this has caused untold problems! I am using
Access
2003 (v. 11.6566.8107) SP2 on XP Professional v. 5.1.2600 SP2

Thanks in advance


Simon