Too many fields defined error in a query output. What is this?

[roaml]

Hi,

I am trying to run a query through a date criteria form and I receive this error:

"Too many fields defined."

I have 45 fields that I am trying to export. Is a query limited to number of fields?

Thanks.

 

[THWatson]

roaml
According to this article

http://support.microsoft.com/?id=199076

Access has a limit of 255 fields.

Tom

 

[THWatson]

roaml
I should have added that I note that if the query is an Update query, you can get the error message if there are more than 127 fields in the query. However, you only have 45 fields, so that still falls considerably short of the maximum.

The problem must lie elsewhere than with the number of fields in the query.

Tom

 

[dhookom]

I think there is also a limit to the number of fields you can use in a Group By.

If you can't figure this out, post back with your SQL View.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

I think there is also a limit to the number of fields you can use in a Group By
10 in JetSQL 4.0

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[roaml]

Hi All,

Actually, I believe it may be my code. I am executing a query through a criteria form. I receive the "Too many fields defined." message after I enter the criteria dates and click on the "Export" button.

Here is the code that I am using to export data to an Excel spreadsheet.

<BEGIN CODE>

Private Sub ExportRec_Click()
On Error GoTo Err_ExportRec_Click

Dim StrCriterion As String
Dim strDocName As String

''Select criteria to populate data
Select Case Me![Criteria]

Case 1
StrCriterion = "[nsc_enter_date] >=#" & Me![BeginDate] & "# And [nsc_enter_date] <=#" & [EndDate] & "#"
End Select

'strDocName = "owner_project_status_All_report"
'DoCmd.OpenReport strDocName, acPreview

Forms!criteria_export_issue_records_dialog_form.Visible = False

'EXPORT DATA
Set myXL = CreateObject("Excel.Application")
Set myWB = myXL.Workbooks.Open("C:\Issue_Report.xls")
myWB.Sheets("Network_Issues").UsedRange.ClearContents
myWB.Save
myXL.Quit
Set myXL = Nothing

'NETWORK ISSUE REPORT
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "daily_issues_qry", "C:\Issue_Report.xls", True, "Network_Issues"

' MISSAGE DIALOG BOX -- Display message when data export is completed.
Dim strMsg As String, strTitle As String
Dim intStyle As Integer

strMsg = "Issue Data was successfully exported!"
strTitle = "Export Issue Data"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Cancel = True

Exit_ExportRec_Click:
Exit Sub

Err_ExportRec_Click:
MsgBox Err.Description
Resume Exit_ExportRec_Click

End Sub

<END CODE>

Thanks so much for your responses.

 

[PHV]

And where is used StrCriterion ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[roaml]

Sorry PHV, I don't understand.

 

[PHV]

What is the purpose of your String variable StrCriterion ?
In your posted code it is useless ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[roaml]

Really, I thought this function was used to pull the data by the date criteria.

Should I remove this section?

 

[roaml]

PHV,

I looked at some other code that I am using that does not have the "StrCriterion" and it does the same thing. It seems to be a hit and miss issue. Here is the code I used without the criteria form interface.

Private Sub Export_Issue_Data_Click()
On Error GoTo Err_Export_Issue_Data_Click

'Open Excel report and clear existing data

Set myXL = CreateObject("Excel.Application")
Set myWB = myXL.Workbooks.Open("C:\Issue_Report.xls")
myWB.Sheets("Network_Issues").UsedRange.ClearContents
myWB.Save
myXL.Quit
Set myXL = Nothing

'NETWORK ISSUE REPORT

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "export_issue_data_qry", "C:\Issue_Report.xls", True, "Network_Issues"

' MISSAGE DIALOG BOX -- Display message when data export is completed.

Dim strMsg As String, strTitle As String
Dim intStyle As Integer

strMsg = "Records was successfully exported!"
strTitle = "Export Data"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Cancel = True

Exit_Export_Issue_Data_Click:
Exit Sub

 

[PHV]

BTW, I think the error is raised by the TransferSpreadsheet method as the Range argument is illegal when TransferType:=acExport

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[roaml]

PHV,

Really! Is there another mehtod to get around this?

Thanks.

 

[dhookom]

roami,
I haven't seen a post of your SQL view of export_issue_data_qry. Would you mind sharing?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[roaml]

dhookom,

Here you go.

<BEGIN SQL QUERY>

SELECT network_issue_table.ni_id AS [Issue ID], network_issue_table.nsc_enter_date AS [Enter date], network_issue_table.nsc_enter_time AS [Enter Time], network_issue_table.a_caller_issue_date AS [A-Caller Issue Date], network_issue_table.a_caller_issue_time AS [A-Caller Issue Time], [rpt_emp_lastname]+", "+[rpt_emp_firstname] AS [Employee Name], network_issue_table.rpt_emp_cuid AS [Employee CUID], network_issue_table.rpt_emp_contactnum AS [Employee Phone], [a_caller_lastname]+", "+[a_caller_firstname] AS [A-Caller Name], network_issue_table.a_caller_cuid AS [A-Caller CUID], network_issue_table.a_caller_dept AS [A-Caller Dept], network_issue_table.a_caller_location AS [A-Caller Location], network_issue_table.a_caller_address AS [A-Caller Address], network_issue_table.a_caller_city AS [A-Caller City], network_issue_table.a_caller_problem_number AS [A-Caller Phone Num], network_issue_table.a_caller_call_type AS [A-Caller Call Type], network_issue_table.a_caller_number_network AS [A-Caller Service Network], [a_issuetype_call_quality]+', ' & [a_issuetype_coverage]+', ' & [a_issuetype_translations]+', ' & [a_issuetype_other]+', ' AS RawConcat, Left([RawConcat],Len([RawConcat])-2) AS [A-Caller Issue Category], network_issue_table.a_net_element_site AS [A-Caller Site], network_issue_table.a_caller_comments AS [A-Caller Comments], [b_receiver_lastname]+", "+[b_receiver_firstname] AS [B-Receiver Name], network_issue_table.b_receiver_cuid AS [B-Receiver CUID], network_issue_table.b_receiver_issue_date AS [B-Receiver Issue Date], network_issue_table.b_reciever_issue_time AS [B-Receiver Issue Time], network_issue_table.b_receiver_dept AS [B-Receiver Dept], network_issue_table.b_receiver_location AS [B-Receiver Location], network_issue_table.b_receiver_address AS [B-Receiver Address], network_issue_table.b_receiver_city AS [B-Receiver City], network_issue_table.b_receiver_number AS [B-Receiver Phone Num], [b_issuetype_call_quality]+', ' & [b_issuetype_coverage]+', ' & [b_issuetype_translations]+', ' & [b_issuetype_other]+', ' AS RawConcat_B, Left([RawConcat_B],Len([RawConcat_B])-2) AS [B-Issue Category], network_issue_table.b_net_element_site AS [B-Receiver Site], network_issue_table.b_net_element_bsc AS [B-Receiver BSC], network_issue_table.b_net_element_dist AS [B-Receiver Dist], network_issue_table.b_net_element_zone AS [B-Receiver Zone], network_issue_table.b_receiver_comments AS [B-Receiver Comments], network_issue_table.nsc_team_tt_number AS [FDS Trouble Ticket], network_issue_table.nsc_call_logged_by AS [NSC Operator], network_issue_table.nsc_call_status AS [NSC Call Status], network_issue_table.nsc_comments AS [NSC Comments]
FROM network_issue_table
WHERE (((network_issue_table.nsc_enter_date) Between [Forms]![criteria_export_issue_records_dialog_form]![BeginDate] And [Forms]![criteria_export_issue_records_dialog_form]![EndDate]))
ORDER BY network_issue_table.nsc_enter_date, network_issue_table.a_caller_issue_date;

<END QUERY>

Thanks.

 

[dhookom]

I would never use a derived column as an expression in another column. You have RawConcat and RawConcat_B used in other calculations. This might work but I don't think it is good practice.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]