Question
Flummoxed by a query
This is a new one for me. I have been querying the same
database for years. I have hundreds of queries working on this very
database on a daily basis without issues. Today a new request came
in to access a field that I have not needed to retrieve before.
Mcaidrecda (Medicaid Recertification Date) is a field in the BasicInfo table, which is part of a FoxPro database designed by a vendor.
The following query completes just fine:
SELECT *
FROM BasicInfo
WHERE IDNo = '5183'
And yes, I know that the use of asterisks in a query is not good practice. I am trying to troubleshoot here...
I do a CFDUMP of the query, and the field I am most interested in, namely BasicInfo.MCAIDRECDA, shows up as expected in the CFDUMP, along with all of the other fields in the table.
HOWEVER,
When I rewrite the query as follows:
SELECT IDNo, Mcaidrecda
FROM BasicInfo
WHERE IDNo = '5183'
I get an error saying that Column 'MCAIDRECDA' is not found. It happens when I remove the WHERE clause, too.
When I open the database table (a FoxPro table), the Mcaidrecda field is very clearly there, and this is supported by the first test (CFDUMP) shown above. Why can't it see the field when the query is written the second way??? I checked and triple checked my spelling of the field names, etc, and all is well.
The reason this is important is because I want to set a WHERE clause based on the Mcaidrecda field, but it stops cold and says the field does not exist, even though I have proven time and again that it is most certainly there.
SOMEBODY tell me I am missing the obvious here....
BTW: CF7
Mcaidrecda (Medicaid Recertification Date) is a field in the BasicInfo table, which is part of a FoxPro database designed by a vendor.
The following query completes just fine:
SELECT *
FROM BasicInfo
WHERE IDNo = '5183'
And yes, I know that the use of asterisks in a query is not good practice. I am trying to troubleshoot here...
I do a CFDUMP of the query, and the field I am most interested in, namely BasicInfo.MCAIDRECDA, shows up as expected in the CFDUMP, along with all of the other fields in the table.
HOWEVER,
When I rewrite the query as follows:
SELECT IDNo, Mcaidrecda
FROM BasicInfo
WHERE IDNo = '5183'
I get an error saying that Column 'MCAIDRECDA' is not found. It happens when I remove the WHERE clause, too.
When I open the database table (a FoxPro table), the Mcaidrecda field is very clearly there, and this is supported by the first test (CFDUMP) shown above. Why can't it see the field when the query is written the second way??? I checked and triple checked my spelling of the field names, etc, and all is well.
The reason this is important is because I want to set a WHERE clause based on the Mcaidrecda field, but it stops cold and says the field does not exist, even though I have proven time and again that it is most certainly there.
SOMEBODY tell me I am missing the obvious here....
BTW: CF7
AdChoices