Why does this query fail?

[Arnie]

In the query design view:

Field: FileName
Table: tblFiles
Sort:
Show:
Criteria: basNoSpaces([FileName])="SerialCommunication.rc"

Field: ProjectName
Table: tblFiles
Sort:
Show: x

Field: FileName
Table: tblFiles
Sort:
Show: x

In the query SQL view:

SELECT tblFiles.ProjectName, tblFiles.FileName
FROM tblFiles
WHERE ((basNoSpaces([FileName])="SerialCommunication.rc"));


The basNoSpaces function is:

Function basNoSpaces(str As String) As String
basNoSpaces = Replace(str, " ", "")
Debug.Print str & vbTab & basNoSpaces
End Function

When the query is executed, the Immediate Window in the VBA app shows
(among hundreds of other lines) without the quotes:

"Serial Communication.rc SerialCommunication.rc"

I get the following error message, several seconds after the query
executed execution begins:
"Data type mismatch in criteria expression."

Then, a results record set is displayed that contains a single record,
as expected. However, that record contains #Name? for both ProjectName
and FileName fields.

Using Access 2003 SP2.

I am not an SQL whiz (IANASQLW?) so any help would be greatly
appreciated.

 

[Rob Parker]

It fails because the criterion you have entered is evaluated as a boolean
vale (True, since basNoSpaces([FileName]) is equal to
"SerialCommunication.rc"); the error message is because your FileName field
is text, not Yes/No (boolean).

You need to change the field you are setting the criteria in to a calculated
field which contains the modified FileName, and enter your criterion against
that, as follows:
Field: NoSpaceFileName: basNoSpaces([FileName])
Table: tblFiles
Sort:
Show:
Criteria: ="SerialCommunication.rc"

HTH,

Rob

 

[Arnie]

Thank you Rob. I actually had two different problems. The one you
describe below and a data error (NULL) in the FileName field of one
lously record.

It fails because the criterion you have entered is evaluated as a boolean
vale (True, since basNoSpaces([FileName]) is equal to
"SerialCommunication.rc"); the error message is because your FileName field
is text, not Yes/No (boolean).

You need to change the field you are setting the criteria in to a calculated
field which contains the modified FileName, and enter your criterion against
that, as follows:
Field: NoSpaceFileName: basNoSpaces([FileName])
Table: tblFiles
Sort:
Show:
Criteria: ="SerialCommunication.rc"

HTH,

Rob

In the query design view:

Field: FileName
Table: tblFiles
Sort:
Show:
Criteria: basNoSpaces([FileName])="SerialCommunication.rc"

Field: ProjectName
Table: tblFiles
Sort:
Show: x

Field: FileName
Table: tblFiles
Sort:
Show: x

In the query SQL view:

SELECT tblFiles.ProjectName, tblFiles.FileName
FROM tblFiles
WHERE ((basNoSpaces([FileName])="SerialCommunication.rc"));


The basNoSpaces function is:

Function basNoSpaces(str As String) As String
basNoSpaces = Replace(str, " ", "")
Debug.Print str & vbTab & basNoSpaces
End Function

When the query is executed, the Immediate Window in the VBA app shows
(among hundreds of other lines) without the quotes:

"Serial Communication.rc SerialCommunication.rc"

I get the following error message, several seconds after the query
executed execution begins:
"Data type mismatch in criteria expression."

Then, a results record set is displayed that contains a single record,
as expected. However, that record contains #Name? for both ProjectName
and FileName fields.

Using Access 2003 SP2.

I am not an SQL whiz (IANASQLW?) so any help would be greatly
appreciated.