Brad Bross said:The problem occurs in the column produced by
"qrySCLResFacType.OrganizationIdentifierValueText AS ResFacType". I tried
Compact and Repair.
SELECT qryCPSAuthorizedServices.EnrollingProviderCDKey,
qryCPSAuthorizedServices.EnrollingOrg,
qryCPSAuthorizedServices.ServiceCatKey, qryCPSAuthorizedServices.Program,
qryCPSAuthorizedServices.OrgParentOrgKey, qryCPSAuthorizedServices.OrgKey,
qryCPSAuthorizedServices.Contractor, qryCPSAuthorizedServices.SAMVendorNo,
qrySCLResFacType.OrganizationIdentifierValueText AS ResFacType,
qryCPSAuthorizedServices.DMHID, qryCPSAuthorizedServices.LastName,
qryCPSAuthorizedServices.FirstName, Sum(qryCPSAuthorizedServices.Delivered)
AS SumOfDelivered, qryCPSAuthorizedServices.EncounterStatusTxt,
qryCPSAuthorizedServices.EncounterServiceStartDate,
qryCPSAuthorizedServices.EncounterServiceEndDate,
qryCPSAuthorizedServices.EncounterStatusCode, [qrySMT-1].[SMT Total Amt],
[qrySMT-1].[Full Pay], [qrySMT-1].SMTStartDate, [qrySMT-1].SMTEndDate
FROM (qryCPSAuthorizedServices LEFT JOIN qrySCLResFacType ON
qryCPSAuthorizedServices.OrgKey = qrySCLResFacType.OrgKey) LEFT JOIN
[qrySMT-1] ON qryCPSAuthorizedServices.DMHID = [qrySMT-1].[DMH ID]
GROUP BY qryCPSAuthorizedServices.EnrollingProviderCDKey,
qryCPSAuthorizedServices.EnrollingOrg,
qryCPSAuthorizedServices.ServiceCatKey, qryCPSAuthorizedServices.Program,
qryCPSAuthorizedServices.OrgParentOrgKey, qryCPSAuthorizedServices.OrgKey,
qryCPSAuthorizedServices.Contractor, qryCPSAuthorizedServices.SAMVendorNo,
qrySCLResFacType.OrganizationIdentifierValueText,
qryCPSAuthorizedServices.DMHID, qryCPSAuthorizedServices.LastName,
qryCPSAuthorizedServices.FirstName,
qryCPSAuthorizedServices.EncounterStatusTxt,
qryCPSAuthorizedServices.EncounterServiceStartDate,
qryCPSAuthorizedServices.EncounterServiceEndDate,
qryCPSAuthorizedServices.EncounterStatusCode, [qrySMT-1].[SMT Total Amt],
[qrySMT-1].[Full Pay], [qrySMT-1].SMTStartDate, [qrySMT-1].SMTEndDate
HAVING (((qryCPSAuthorizedServices.EnrollingProviderCDKey)=22342) AND
((qryCPSAuthorizedServices.DMHID)=688490) AND
((qryCPSAuthorizedServices.EncounterStatusCode)="G2") AND
(([qrySMT-1].SMTStartDate)<=[qryCPSAuthorizedServices]![EncounterServiceStartDate])
AND
(([qrySMT-1].SMTEndDate)>=[qryCPSAuthorizedServices]![EncounterServiceEndDate]));
--
Brad Bross
MO Dept of Mental Health
Jerry Whittle said:Show us the SQL so that we can rule out that it's causing problems. Open the
query in design view. Next go to View, SQL View and copy and past it here.
Also strange characters in fields can often be a sign of corruption
problems. Make a backup of the database first, then do a Compact and Repair.
If the field in question has an index, the index could be the problem. Drop
the index then rebuild it.
Jerry Whittle said:Anwhere near STL? I'm right across the river from it.
There is nothing obvious in the query such as using some kind of function on
the data. However the field isn't pulled directly from a table, but rather
from qrySCLResFacType which I assume is another query. I'd go to that query
and see if the problem shows up there. You may want to post the SQL for that
query also. I assume, and we know what that means, that you checked the table
where OrganizationIdentifierValueText resides and the data looks good there.
Is OrganizationIdentifierValueText a memo field or Text as the field name
implies?
It still could be corruption and something that compact and repair can't fix.
Tony Toews has an excellent web page on database corruption.
http://www.granite.ab.ca/access/corruptmdbs.htm
Allen Brown also has excellent info on corruption.
http://allenbrowne.com/ser-47.html
I have a white paper in a Word document named Fix Corrupt Access Database
towards the bottom this page:
http://www.rogersaccesslibrary.com/OtherLibraries.asp
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
Brad Bross said:The problem occurs in the column produced by
"qrySCLResFacType.OrganizationIdentifierValueText AS ResFacType". I tried
Compact and Repair.
SELECT qryCPSAuthorizedServices.EnrollingProviderCDKey,
qryCPSAuthorizedServices.EnrollingOrg,
qryCPSAuthorizedServices.ServiceCatKey, qryCPSAuthorizedServices.Program,
qryCPSAuthorizedServices.OrgParentOrgKey, qryCPSAuthorizedServices.OrgKey,
qryCPSAuthorizedServices.Contractor, qryCPSAuthorizedServices.SAMVendorNo,
qrySCLResFacType.OrganizationIdentifierValueText AS ResFacType,
qryCPSAuthorizedServices.DMHID, qryCPSAuthorizedServices.LastName,
qryCPSAuthorizedServices.FirstName, Sum(qryCPSAuthorizedServices.Delivered)
AS SumOfDelivered, qryCPSAuthorizedServices.EncounterStatusTxt,
qryCPSAuthorizedServices.EncounterServiceStartDate,
qryCPSAuthorizedServices.EncounterServiceEndDate,
qryCPSAuthorizedServices.EncounterStatusCode, [qrySMT-1].[SMT Total Amt],
[qrySMT-1].[Full Pay], [qrySMT-1].SMTStartDate, [qrySMT-1].SMTEndDate
FROM (qryCPSAuthorizedServices LEFT JOIN qrySCLResFacType ON
qryCPSAuthorizedServices.OrgKey = qrySCLResFacType.OrgKey) LEFT JOIN
[qrySMT-1] ON qryCPSAuthorizedServices.DMHID = [qrySMT-1].[DMH ID]
GROUP BY qryCPSAuthorizedServices.EnrollingProviderCDKey,
qryCPSAuthorizedServices.EnrollingOrg,
qryCPSAuthorizedServices.ServiceCatKey, qryCPSAuthorizedServices.Program,
qryCPSAuthorizedServices.OrgParentOrgKey, qryCPSAuthorizedServices.OrgKey,
qryCPSAuthorizedServices.Contractor, qryCPSAuthorizedServices.SAMVendorNo,
qrySCLResFacType.OrganizationIdentifierValueText,
qryCPSAuthorizedServices.DMHID, qryCPSAuthorizedServices.LastName,
qryCPSAuthorizedServices.FirstName,
qryCPSAuthorizedServices.EncounterStatusTxt,
qryCPSAuthorizedServices.EncounterServiceStartDate,
qryCPSAuthorizedServices.EncounterServiceEndDate,
qryCPSAuthorizedServices.EncounterStatusCode, [qrySMT-1].[SMT Total Amt],
[qrySMT-1].[Full Pay], [qrySMT-1].SMTStartDate, [qrySMT-1].SMTEndDate
HAVING (((qryCPSAuthorizedServices.EnrollingProviderCDKey)=22342) AND
((qryCPSAuthorizedServices.DMHID)=688490) AND
((qryCPSAuthorizedServices.EncounterStatusCode)="G2") AND
(([qrySMT-1].SMTStartDate)<=[qryCPSAuthorizedServices]![EncounterServiceStartDate])
AND
(([qrySMT-1].SMTEndDate)>=[qryCPSAuthorizedServices]![EncounterServiceEndDate]));
--
Brad Bross
MO Dept of Mental Health
Jerry Whittle said:Show us the SQL so that we can rule out that it's causing problems. Open the
query in design view. Next go to View, SQL View and copy and past it here.
Also strange characters in fields can often be a sign of corruption
problems. Make a backup of the database first, then do a Compact and Repair.
If the field in question has an index, the index could be the problem. Drop
the index then rebuild it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
When I run the query, one column has very strange characters like little
squares with question marks inside. When I go back to look at the source
data, it has letters. Any idea why my query produces the strange characters
and how I might correct it?
Brad Bross said:We are located in Jefferson City approximately 2 1/2 hrs west of St. Louis.
The data looks good when I check the source. The
"OrganizationIdentifierValueText" field in the data source is a Memo field.
Could the problem be related to that?
--
Brad Bross
MO Dept of Mental Health
Jerry Whittle said:Anwhere near STL? I'm right across the river from it.
There is nothing obvious in the query such as using some kind of function on
the data. However the field isn't pulled directly from a table, but rather
from qrySCLResFacType which I assume is another query. I'd go to that query
and see if the problem shows up there. You may want to post the SQL for that
query also. I assume, and we know what that means, that you checked the table
where OrganizationIdentifierValueText resides and the data looks good there.
Is OrganizationIdentifierValueText a memo field or Text as the field name
implies?
It still could be corruption and something that compact and repair can't fix.
Tony Toews has an excellent web page on database corruption.
http://www.granite.ab.ca/access/corruptmdbs.htm
Allen Brown also has excellent info on corruption.
http://allenbrowne.com/ser-47.html
I have a white paper in a Word document named Fix Corrupt Access Database
towards the bottom this page:
http://www.rogersaccesslibrary.com/OtherLibraries.asp
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
Brad Bross said:The problem occurs in the column produced by
"qrySCLResFacType.OrganizationIdentifierValueText AS ResFacType". I tried
Compact and Repair.
SELECT qryCPSAuthorizedServices.EnrollingProviderCDKey,
qryCPSAuthorizedServices.EnrollingOrg,
qryCPSAuthorizedServices.ServiceCatKey, qryCPSAuthorizedServices.Program,
qryCPSAuthorizedServices.OrgParentOrgKey, qryCPSAuthorizedServices.OrgKey,
qryCPSAuthorizedServices.Contractor, qryCPSAuthorizedServices.SAMVendorNo,
qrySCLResFacType.OrganizationIdentifierValueText AS ResFacType,
qryCPSAuthorizedServices.DMHID, qryCPSAuthorizedServices.LastName,
qryCPSAuthorizedServices.FirstName, Sum(qryCPSAuthorizedServices.Delivered)
AS SumOfDelivered, qryCPSAuthorizedServices.EncounterStatusTxt,
qryCPSAuthorizedServices.EncounterServiceStartDate,
qryCPSAuthorizedServices.EncounterServiceEndDate,
qryCPSAuthorizedServices.EncounterStatusCode, [qrySMT-1].[SMT Total Amt],
[qrySMT-1].[Full Pay], [qrySMT-1].SMTStartDate, [qrySMT-1].SMTEndDate
FROM (qryCPSAuthorizedServices LEFT JOIN qrySCLResFacType ON
qryCPSAuthorizedServices.OrgKey = qrySCLResFacType.OrgKey) LEFT JOIN
[qrySMT-1] ON qryCPSAuthorizedServices.DMHID = [qrySMT-1].[DMH ID]
GROUP BY qryCPSAuthorizedServices.EnrollingProviderCDKey,
qryCPSAuthorizedServices.EnrollingOrg,
qryCPSAuthorizedServices.ServiceCatKey, qryCPSAuthorizedServices.Program,
qryCPSAuthorizedServices.OrgParentOrgKey, qryCPSAuthorizedServices.OrgKey,
qryCPSAuthorizedServices.Contractor, qryCPSAuthorizedServices.SAMVendorNo,
qrySCLResFacType.OrganizationIdentifierValueText,
qryCPSAuthorizedServices.DMHID, qryCPSAuthorizedServices.LastName,
qryCPSAuthorizedServices.FirstName,
qryCPSAuthorizedServices.EncounterStatusTxt,
qryCPSAuthorizedServices.EncounterServiceStartDate,
qryCPSAuthorizedServices.EncounterServiceEndDate,
qryCPSAuthorizedServices.EncounterStatusCode, [qrySMT-1].[SMT Total Amt],
[qrySMT-1].[Full Pay], [qrySMT-1].SMTStartDate, [qrySMT-1].SMTEndDate
HAVING (((qryCPSAuthorizedServices.EnrollingProviderCDKey)=22342) AND
((qryCPSAuthorizedServices.DMHID)=688490) AND
((qryCPSAuthorizedServices.EncounterStatusCode)="G2") AND
(([qrySMT-1].SMTStartDate)<=[qryCPSAuthorizedServices]![EncounterServiceStartDate])
AND
(([qrySMT-1].SMTEndDate)>=[qryCPSAuthorizedServices]![EncounterServiceEndDate]));
--
Brad Bross
MO Dept of Mental Health
:
Show us the SQL so that we can rule out that it's causing problems. Open the
query in design view. Next go to View, SQL View and copy and past it here.
Also strange characters in fields can often be a sign of corruption
problems. Make a backup of the database first, then do a Compact and Repair.
If the field in question has an index, the index could be the problem. Drop
the index then rebuild it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
When I run the query, one column has very strange characters like little
squares with question marks inside. When I go back to look at the source
data, it has letters. Any idea why my query produces the strange characters
and how I might correct it?
Jerry Whittle said:Memo Field. I was afraid of that. Memo fields a subject to corruption more
than any other. Also you are grouping by the memo field which causes Access
to work harder. Also you are nesting a query within another query. If there's
any weaknesses, that could cause it to show.
The data is memo fields isn't really part of the record. Rather the memo
field data is stored in a hidded table structure that you can't see. The link
to your table data and the memo data is somewhat fragile and subject to
breaking.
I suggest that you first make a very good backup of the database and put it
safely away. Then create a new database and import all the objects from the
old database into it. You might want to start with the problem table by
itself and see if you get any error messages or the table refuses to import.
Once you import all the objects, you may need to set up relationships,
security, and startup options again. Then rename the new database the same as
the problem database. Hopefully Access fixed any problems during the import.
If not you might want to check out the resourses below. My white paper has
information on how to find the records with bad memo field data and how to
save almost everything.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
Brad Bross said:We are located in Jefferson City approximately 2 1/2 hrs west of St. Louis.
The data looks good when I check the source. The
"OrganizationIdentifierValueText" field in the data source is a Memo field.
Could the problem be related to that?
--
Brad Bross
MO Dept of Mental Health
Jerry Whittle said:Anwhere near STL? I'm right across the river from it.
There is nothing obvious in the query such as using some kind of function on
the data. However the field isn't pulled directly from a table, but rather
from qrySCLResFacType which I assume is another query. I'd go to that query
and see if the problem shows up there. You may want to post the SQL for that
query also. I assume, and we know what that means, that you checked the table
where OrganizationIdentifierValueText resides and the data looks good there.
Is OrganizationIdentifierValueText a memo field or Text as the field name
implies?
It still could be corruption and something that compact and repair can't fix.
Tony Toews has an excellent web page on database corruption.
http://www.granite.ab.ca/access/corruptmdbs.htm
Allen Brown also has excellent info on corruption.
http://allenbrowne.com/ser-47.html
I have a white paper in a Word document named Fix Corrupt Access Database
towards the bottom this page:
http://www.rogersaccesslibrary.com/OtherLibraries.asp
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
The problem occurs in the column produced by
"qrySCLResFacType.OrganizationIdentifierValueText AS ResFacType". I tried
Compact and Repair.
SELECT qryCPSAuthorizedServices.EnrollingProviderCDKey,
qryCPSAuthorizedServices.EnrollingOrg,
qryCPSAuthorizedServices.ServiceCatKey, qryCPSAuthorizedServices.Program,
qryCPSAuthorizedServices.OrgParentOrgKey, qryCPSAuthorizedServices.OrgKey,
qryCPSAuthorizedServices.Contractor, qryCPSAuthorizedServices.SAMVendorNo,
qrySCLResFacType.OrganizationIdentifierValueText AS ResFacType,
qryCPSAuthorizedServices.DMHID, qryCPSAuthorizedServices.LastName,
qryCPSAuthorizedServices.FirstName, Sum(qryCPSAuthorizedServices.Delivered)
AS SumOfDelivered, qryCPSAuthorizedServices.EncounterStatusTxt,
qryCPSAuthorizedServices.EncounterServiceStartDate,
qryCPSAuthorizedServices.EncounterServiceEndDate,
qryCPSAuthorizedServices.EncounterStatusCode, [qrySMT-1].[SMT Total Amt],
[qrySMT-1].[Full Pay], [qrySMT-1].SMTStartDate, [qrySMT-1].SMTEndDate
FROM (qryCPSAuthorizedServices LEFT JOIN qrySCLResFacType ON
qryCPSAuthorizedServices.OrgKey = qrySCLResFacType.OrgKey) LEFT JOIN
[qrySMT-1] ON qryCPSAuthorizedServices.DMHID = [qrySMT-1].[DMH ID]
GROUP BY qryCPSAuthorizedServices.EnrollingProviderCDKey,
qryCPSAuthorizedServices.EnrollingOrg,
qryCPSAuthorizedServices.ServiceCatKey, qryCPSAuthorizedServices.Program,
qryCPSAuthorizedServices.OrgParentOrgKey, qryCPSAuthorizedServices.OrgKey,
qryCPSAuthorizedServices.Contractor, qryCPSAuthorizedServices.SAMVendorNo,
qrySCLResFacType.OrganizationIdentifierValueText,
qryCPSAuthorizedServices.DMHID, qryCPSAuthorizedServices.LastName,
qryCPSAuthorizedServices.FirstName,
qryCPSAuthorizedServices.EncounterStatusTxt,
qryCPSAuthorizedServices.EncounterServiceStartDate,
qryCPSAuthorizedServices.EncounterServiceEndDate,
qryCPSAuthorizedServices.EncounterStatusCode, [qrySMT-1].[SMT Total Amt],
[qrySMT-1].[Full Pay], [qrySMT-1].SMTStartDate, [qrySMT-1].SMTEndDate
HAVING (((qryCPSAuthorizedServices.EnrollingProviderCDKey)=22342) AND
((qryCPSAuthorizedServices.DMHID)=688490) AND
((qryCPSAuthorizedServices.EncounterStatusCode)="G2") AND
(([qrySMT-1].SMTStartDate)<=[qryCPSAuthorizedServices]![EncounterServiceStartDate])
AND
(([qrySMT-1].SMTEndDate)>=[qryCPSAuthorizedServices]![EncounterServiceEndDate]));
--
Brad Bross
MO Dept of Mental Health
:
Show us the SQL so that we can rule out that it's causing problems. Open the
query in design view. Next go to View, SQL View and copy and past it here.
Also strange characters in fields can often be a sign of corruption
problems. Make a backup of the database first, then do a Compact and Repair.
If the field in question has an index, the index could be the problem. Drop
the index then rebuild it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
When I run the query, one column has very strange characters like little
squares with question marks inside. When I go back to look at the source
data, it has letters. Any idea why my query produces the strange characters
and how I might correct it?
Brad Bross said:Jerry, thx for all your help. I will try your suggestions.
--
Brad Bross
MO Dept of Mental Health
Jerry Whittle said:Memo Field. I was afraid of that. Memo fields a subject to corruption more
than any other. Also you are grouping by the memo field which causes Access
to work harder. Also you are nesting a query within another query. If there's
any weaknesses, that could cause it to show.
The data is memo fields isn't really part of the record. Rather the memo
field data is stored in a hidded table structure that you can't see. The link
to your table data and the memo data is somewhat fragile and subject to
breaking.
I suggest that you first make a very good backup of the database and put it
safely away. Then create a new database and import all the objects from the
old database into it. You might want to start with the problem table by
itself and see if you get any error messages or the table refuses to import.
Once you import all the objects, you may need to set up relationships,
security, and startup options again. Then rename the new database the same as
the problem database. Hopefully Access fixed any problems during the import.
If not you might want to check out the resourses below. My white paper has
information on how to find the records with bad memo field data and how to
save almost everything.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
Brad Bross said:We are located in Jefferson City approximately 2 1/2 hrs west of St. Louis.
The data looks good when I check the source. The
"OrganizationIdentifierValueText" field in the data source is a Memo field.
Could the problem be related to that?
--
Brad Bross
MO Dept of Mental Health
:
Anwhere near STL? I'm right across the river from it.
There is nothing obvious in the query such as using some kind of function on
the data. However the field isn't pulled directly from a table, but rather
from qrySCLResFacType which I assume is another query. I'd go to that query
and see if the problem shows up there. You may want to post the SQL for that
query also. I assume, and we know what that means, that you checked the table
where OrganizationIdentifierValueText resides and the data looks good there.
Is OrganizationIdentifierValueText a memo field or Text as the field name
implies?
It still could be corruption and something that compact and repair can't fix.
Tony Toews has an excellent web page on database corruption.
http://www.granite.ab.ca/access/corruptmdbs.htm
Allen Brown also has excellent info on corruption.
http://allenbrowne.com/ser-47.html
I have a white paper in a Word document named Fix Corrupt Access Database
towards the bottom this page:
http://www.rogersaccesslibrary.com/OtherLibraries.asp
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
The problem occurs in the column produced by
"qrySCLResFacType.OrganizationIdentifierValueText AS ResFacType". I tried
Compact and Repair.
SELECT qryCPSAuthorizedServices.EnrollingProviderCDKey,
qryCPSAuthorizedServices.EnrollingOrg,
qryCPSAuthorizedServices.ServiceCatKey, qryCPSAuthorizedServices.Program,
qryCPSAuthorizedServices.OrgParentOrgKey, qryCPSAuthorizedServices.OrgKey,
qryCPSAuthorizedServices.Contractor, qryCPSAuthorizedServices.SAMVendorNo,
qrySCLResFacType.OrganizationIdentifierValueText AS ResFacType,
qryCPSAuthorizedServices.DMHID, qryCPSAuthorizedServices.LastName,
qryCPSAuthorizedServices.FirstName, Sum(qryCPSAuthorizedServices.Delivered)
AS SumOfDelivered, qryCPSAuthorizedServices.EncounterStatusTxt,
qryCPSAuthorizedServices.EncounterServiceStartDate,
qryCPSAuthorizedServices.EncounterServiceEndDate,
qryCPSAuthorizedServices.EncounterStatusCode, [qrySMT-1].[SMT Total Amt],
[qrySMT-1].[Full Pay], [qrySMT-1].SMTStartDate, [qrySMT-1].SMTEndDate
FROM (qryCPSAuthorizedServices LEFT JOIN qrySCLResFacType ON
qryCPSAuthorizedServices.OrgKey = qrySCLResFacType.OrgKey) LEFT JOIN
[qrySMT-1] ON qryCPSAuthorizedServices.DMHID = [qrySMT-1].[DMH ID]
GROUP BY qryCPSAuthorizedServices.EnrollingProviderCDKey,
qryCPSAuthorizedServices.EnrollingOrg,
qryCPSAuthorizedServices.ServiceCatKey, qryCPSAuthorizedServices.Program,
qryCPSAuthorizedServices.OrgParentOrgKey, qryCPSAuthorizedServices.OrgKey,
qryCPSAuthorizedServices.Contractor, qryCPSAuthorizedServices.SAMVendorNo,
qrySCLResFacType.OrganizationIdentifierValueText,
qryCPSAuthorizedServices.DMHID, qryCPSAuthorizedServices.LastName,
qryCPSAuthorizedServices.FirstName,
qryCPSAuthorizedServices.EncounterStatusTxt,
qryCPSAuthorizedServices.EncounterServiceStartDate,
qryCPSAuthorizedServices.EncounterServiceEndDate,
qryCPSAuthorizedServices.EncounterStatusCode, [qrySMT-1].[SMT Total Amt],
[qrySMT-1].[Full Pay], [qrySMT-1].SMTStartDate, [qrySMT-1].SMTEndDate
HAVING (((qryCPSAuthorizedServices.EnrollingProviderCDKey)=22342) AND
((qryCPSAuthorizedServices.DMHID)=688490) AND
((qryCPSAuthorizedServices.EncounterStatusCode)="G2") AND
(([qrySMT-1].SMTStartDate)<=[qryCPSAuthorizedServices]![EncounterServiceStartDate])
AND
(([qrySMT-1].SMTEndDate)>=[qryCPSAuthorizedServices]![EncounterServiceEndDate]));
--
Brad Bross
MO Dept of Mental Health
:
Show us the SQL so that we can rule out that it's causing problems. Open the
query in design view. Next go to View, SQL View and copy and past it here.
Also strange characters in fields can often be a sign of corruption
problems. Make a backup of the database first, then do a Compact and Repair.
If the field in question has an index, the index could be the problem. Drop
the index then rebuild it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
When I run the query, one column has very strange characters like little
squares with question marks inside. When I go back to look at the source
data, it has letters. Any idea why my query produces the strange characters
and how I might correct it?
Brad Bross said:Jerry, I found an alternative you might be interested in knowing about which
worked just fine - "ResFacType: Left([OrganizationIdentifierValueText],8)"
--
Brad Bross
MO Dept of Mental Health
Brad Bross said:Jerry, thx for all your help. I will try your suggestions.
--
Brad Bross
MO Dept of Mental Health
Jerry Whittle said:Memo Field. I was afraid of that. Memo fields a subject to corruption more
than any other. Also you are grouping by the memo field which causes Access
to work harder. Also you are nesting a query within another query. If there's
any weaknesses, that could cause it to show.
The data is memo fields isn't really part of the record. Rather the memo
field data is stored in a hidded table structure that you can't see. The link
to your table data and the memo data is somewhat fragile and subject to
breaking.
I suggest that you first make a very good backup of the database and put it
safely away. Then create a new database and import all the objects from the
old database into it. You might want to start with the problem table by
itself and see if you get any error messages or the table refuses to import.
Once you import all the objects, you may need to set up relationships,
security, and startup options again. Then rename the new database the same as
the problem database. Hopefully Access fixed any problems during the import.
If not you might want to check out the resourses below. My white paper has
information on how to find the records with bad memo field data and how to
save almost everything.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
We are located in Jefferson City approximately 2 1/2 hrs west of St. Louis.
The data looks good when I check the source. The
"OrganizationIdentifierValueText" field in the data source is a Memo field.
Could the problem be related to that?
--
Brad Bross
MO Dept of Mental Health
:
Anwhere near STL? I'm right across the river from it.
There is nothing obvious in the query such as using some kind of function on
the data. However the field isn't pulled directly from a table, but rather
from qrySCLResFacType which I assume is another query. I'd go to that query
and see if the problem shows up there. You may want to post the SQL for that
query also. I assume, and we know what that means, that you checked the table
where OrganizationIdentifierValueText resides and the data looks good there.
Is OrganizationIdentifierValueText a memo field or Text as the field name
implies?
It still could be corruption and something that compact and repair can't fix.
Tony Toews has an excellent web page on database corruption.
http://www.granite.ab.ca/access/corruptmdbs.htm
Allen Brown also has excellent info on corruption.
http://allenbrowne.com/ser-47.html
I have a white paper in a Word document named Fix Corrupt Access Database
towards the bottom this page:
http://www.rogersaccesslibrary.com/OtherLibraries.asp
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
The problem occurs in the column produced by
"qrySCLResFacType.OrganizationIdentifierValueText AS ResFacType". I tried
Compact and Repair.
SELECT qryCPSAuthorizedServices.EnrollingProviderCDKey,
qryCPSAuthorizedServices.EnrollingOrg,
qryCPSAuthorizedServices.ServiceCatKey, qryCPSAuthorizedServices.Program,
qryCPSAuthorizedServices.OrgParentOrgKey, qryCPSAuthorizedServices.OrgKey,
qryCPSAuthorizedServices.Contractor, qryCPSAuthorizedServices.SAMVendorNo,
qrySCLResFacType.OrganizationIdentifierValueText AS ResFacType,
qryCPSAuthorizedServices.DMHID, qryCPSAuthorizedServices.LastName,
qryCPSAuthorizedServices.FirstName, Sum(qryCPSAuthorizedServices.Delivered)
AS SumOfDelivered, qryCPSAuthorizedServices.EncounterStatusTxt,
qryCPSAuthorizedServices.EncounterServiceStartDate,
qryCPSAuthorizedServices.EncounterServiceEndDate,
qryCPSAuthorizedServices.EncounterStatusCode, [qrySMT-1].[SMT Total Amt],
[qrySMT-1].[Full Pay], [qrySMT-1].SMTStartDate, [qrySMT-1].SMTEndDate
FROM (qryCPSAuthorizedServices LEFT JOIN qrySCLResFacType ON
qryCPSAuthorizedServices.OrgKey = qrySCLResFacType.OrgKey) LEFT JOIN
[qrySMT-1] ON qryCPSAuthorizedServices.DMHID = [qrySMT-1].[DMH ID]
GROUP BY qryCPSAuthorizedServices.EnrollingProviderCDKey,
qryCPSAuthorizedServices.EnrollingOrg,
qryCPSAuthorizedServices.ServiceCatKey, qryCPSAuthorizedServices.Program,
qryCPSAuthorizedServices.OrgParentOrgKey, qryCPSAuthorizedServices.OrgKey,
qryCPSAuthorizedServices.Contractor, qryCPSAuthorizedServices.SAMVendorNo,
qrySCLResFacType.OrganizationIdentifierValueText,
qryCPSAuthorizedServices.DMHID, qryCPSAuthorizedServices.LastName,
qryCPSAuthorizedServices.FirstName,
qryCPSAuthorizedServices.EncounterStatusTxt,
qryCPSAuthorizedServices.EncounterServiceStartDate,
qryCPSAuthorizedServices.EncounterServiceEndDate,
qryCPSAuthorizedServices.EncounterStatusCode, [qrySMT-1].[SMT Total Amt],
[qrySMT-1].[Full Pay], [qrySMT-1].SMTStartDate, [qrySMT-1].SMTEndDate
HAVING (((qryCPSAuthorizedServices.EnrollingProviderCDKey)=22342) AND
((qryCPSAuthorizedServices.DMHID)=688490) AND
((qryCPSAuthorizedServices.EncounterStatusCode)="G2") AND
(([qrySMT-1].SMTStartDate)<=[qryCPSAuthorizedServices]![EncounterServiceStartDate])
AND
(([qrySMT-1].SMTEndDate)>=[qryCPSAuthorizedServices]![EncounterServiceEndDate]));
--
Brad Bross
MO Dept of Mental Health
:
Show us the SQL so that we can rule out that it's causing problems. Open the
query in design view. Next go to View, SQL View and copy and past it here.
Also strange characters in fields can often be a sign of corruption
problems. Make a backup of the database first, then do a Compact and Repair.
If the field in question has an index, the index could be the problem. Drop
the index then rebuild it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
When I run the query, one column has very strange characters like little
squares with question marks inside. When I go back to look at the source
data, it has letters. Any idea why my query produces the strange characters
and how I might correct it?
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.