Make Table Query Creating Binary Data

[jketcher]

I am creating a table using a make table query. The data from the table is used in a mail merge Word document. I noticed that the data entered in text boxes is not displaying in the word document after the mail merge is completed. Then I discovered that the text data is created as binary in the make table. How do I get this data to be inserted as 'text' rather than binary? The table name is tblExport. The code to create the table is:

DoCmd.OpenQuery "qry_data_new", , acEdit

Thanks!

 

[jketcher]

Hi, I just wanted to make sure someone saw my question from yesterday. I hoping you have an answer for me. Thanks!

 

[boblarson]

First, we need to see the SQL of qry_data_new to see what it has in it.

 

[jketcher]

When I open the query it immediately asks questions for creating the table. It does not give an option of SQL in the view list. Is there another way to look at the SQL? qry_data_new is a Make Table query.

 

[boblarson]

When I open the query it immediately asks questions for creating the table. It does not give an option of SQL in the view list. Is there another way to look at the SQL?

When you open the query in DESIGN VIEW? You open it in Design view and then click on the SQL view.

 

[jketcher]

Here is the SQL:

SELECT [Issue Tracker].ID, [Engagement Links].[Account Name], [Issue Tracker].Client, [Issue Tracker].Issue, [Issue Tracker].Category, [Issue Tracker].Description, [Issue Tracker].Priority, [Issue Tracker].[Assigned To], [Issue Tracker].[Target Completion Date], [Issue Tracker].Status, [Issue Tracker].[Percentage Complete], [Issue Tracker].[Closed Date], [Issue Tracker].Resolution, [Issue Tracker].[Modified By], qry_count_rows_sum.SumOfCounter AS Total_Records, [Forms]![Client Status Report Creation Database]![Special Alerts] AS Special_Alerts, [Forms]![Client Status Report Creation Database]![Administrative] AS Administrative, [Forms]![Client Status report Creation Database]![Report_Date] AS Report_Date, [Forms]![Client Status Report Creation Database]![Activities Completed] AS Activities_Completed, [Forms]![Client Status Report Creation Database]![Activities Scheduled] AS Activities_Scheduled, DateAdd('d',7,[Report_Date]) AS Report_Date_Next INTO tblExport
FROM [Issue Tracker] INNER JOIN ([Engagement Links] INNER JOIN qry_count_rows_sum ON [Engagement Links].[Account Name] = qry_count_rows_sum.[Account Name]) ON [Issue Tracker].Client = [Engagement Links].[Account Name]
WHERE ((([Issue Tracker].Client)=[Forms]![Client Status Report Creation Database]![ClientSelected]) AND (([Issue Tracker].Status)<>'Hidden'));

The fields; Special_Alerts, Administrative, Activities_Completed; Activities_Scheduled, are the problem fields that are being written as binary in the table. The data comes from text entry boxes from a form. The data is defined as 'text'.

 

[boblarson]

I would suggest NOT using a make table query, if you can. Create the table once, go modify the fields to the way they should be and then use a delete query to remove the data and an append query to append the new data. Then the datatypes will be set by YOU and not by Access.

 

[LPurvis]

Hi

What you're experiencing is perfectly normal.
Without an underlying bound datatype to guide the expression resolution, Binary is the default fallback (it's a good "catch all" option).

FWIW there's nothing shocking or unusual about the Binary datatype. OK - you probably don't want it as it behaves slightly differently to the Text type you perhaps want (text comparison is case sensitive which can actually be very useful when you do want it - for example for Passwords ;-).

To coerce the type just give the query a hint of sorts.

SELECT I.ID, E.[Account Name], I.Client, I.Issue, I.Category, I.Description, I.Priority, I.[Assigned To], I.[Target Completion Date], I.Status, I.[Percentage Complete], I.[Closed Date], I.Resolution, I.[Modified By], Q.SumOfCounter AS Total_Records,
IIF([Forms]![Client Status Report Creation Database]![Special Alerts] Is Null, Null, CStr([Forms]![Client Status Report Creation Database]![Special Alerts])) AS Special_Alerts,
IIF([Forms]![Client Status Report Creation Database]![Administrative] Is Null, Null, CStr([Forms]![Client Status Report Creation Database]![Administrative])) AS Administrative,
IIF([Forms]![Client Status report Creation Database]![Report_Date] Is Null, Null, CStr([Forms]![Client Status report Creation Database]![Report_Date])) AS Report_Date,
IIF([Forms]![Client Status Report Creation Database]![Activities Completed] Is Null, Null, CStr([Forms]![Client Status Report Creation Database]![Activities Completed])) AS Activities_Completed,
IIF([Forms]![Client Status Report Creation Database]![Activities Scheduled] Is Null, Null, CStr([Forms]![Client Status Report Creation Database]![Activities Scheduled])) AS Activities_Scheduled,
DateAdd('d',7,[Report_Date]) AS Report_Date_Next
INTO tblExport
FROM [Issue Tracker] I INNER JOIN ([Engagement Links] E INNER JOIN qry_count_rows_sum Q ON [E].[Account Name] = Q.[Account Name]) ON .Client = [E].[Account Name]
WHERE .Client=[Forms]![Client Status Report Creation Database]![ClientSelected] AND .Status<>'Hidden'

That's a fearsomely long (and space containing) form name though. :-s
Makes for a much uglier query.

Anyway - there you have it.

Cheers.