All fields disappear from query ! Acc2013

[Michael Abrams]

I will probably need to supply more info, but I will try to be clear .

I have a base query: QUERY NAME = CONTRACT CODE CHANGE

Code: Select all

SELECT [CURRENT MONTH MEMBER COUNT].[ALPHA PREFIX+HCID], [CURRENT MONTH MEMBER COUNT].MEMNUM, [CURRENT MONTH MEMBER COUNT].[MEMBER NAME], [CURRENT MONTH MEMBER COUNT].DOB, [CURRENT MONTH MEMBER COUNT].[EMP GROUP #], [CURRENT MONTH MEMBER COUNT].[PLAN CODE], [CURRENT MONTH MEMBER COUNT].[CONTRACT CODE], [PREVIOUS MONTH MEMBER COUNT].[CONTRACT CODE], [CURRENT MONTH MEMBER COUNT].[PCP NAME], [CURRENT MONTH MEMBER COUNT].[PROV EEF DATE], [CURRENT MONTH MEMBER COUNT].ERISA
FROM [CURRENT MONTH MEMBER COUNT] LEFT JOIN [PREVIOUS MONTH MEMBER COUNT] ON [CURRENT MONTH MEMBER COUNT].MEMNUM=[PREVIOUS MONTH MEMBER COUNT].MEMNUM
WHERE ((([CURRENT MONTH MEMBER COUNT].[CONTRACT CODE])<>[PREVIOUS MONTH MEMBER COUNT].[CONTRACT CODE]) AND (([PREVIOUS MONTH MEMBER COUNT].MEMNUM) Is Not Null));

QUERY BASED ON ABOVE QUERY – NAME = CONTRACT CODE CHANGE NOT ON ACTIVITY FILE

Code: Select all

SELECT [CONTRACT CODE CHANGE].[ALPHA PREFIX+HCID], [CONTRACT CODE CHANGE].[MEMBER NAME], [CONTRACT CODE CHANGE].DOB, [CONTRACT CODE CHANGE].[EMP GROUP #], [CONTRACT CODE CHANGE].[PLAN CODE], [CONTRACT CODE CHANGE].[CURRENT MONTH MEMBER COUNT].[CONTRACT CODE], [CONTRACT CODE CHANGE].[PREVIOUS MONTH MEMBER COUNT].[CONTRACT CODE], [CONTRACT CODE CHANGE].[PCP NAME], [CONTRACT CODE CHANGE].[PROV EEF DATE], [CONTRACT CODE CHANGE].ERISA
FROM [CONTRACT CODE CHANGE] LEFT JOIN ACTIVITY ON [CONTRACT CODE CHANGE].MEMNUM=ACTIVITY.MEMNUM
WHERE (((ACTIVITY.MEMNUM) Is Null));

FORM CODE (COMMAND BUTTON)=

Code: Select all

DoCmd.OutputTo acQuery, "CONTRACT CODE CHANGE NOT ON ACTIVITY FILE", "Microsoft Excel Workbook(*.xlsx)", _
"R:\HMOs 2017\BLUE TIMES 2017\" & strMonth & "\MANUAL REPORTS\CONTRACT CODE CHANGE NOT ON ACTIVITY FILE.xlsx", False, ""
MsgBox "REPORT EXPORT COMPLETE", , "EXPORT COMPLETE"

DoCmd.Close acForm, Me.Name

The report exports fine into Excel, but..... when I return to look at the query CONTRACT CODE CHANGE NOT ON ACTIVITY FILE - ALL FIELDS are gone !
(The base query remains fine)

What happened? What should I be looking for to correct this behavior?

Thanks for any help !

Michael

[HansV]

Do the fields only disappear if you export CONTRACT CODE CHANGE NOT ON ACTIVITY FILE to Excel? Or also if you open the query in Access?

And what happens if you use

Code: Select all

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "CONTRACT CODE CHANGE NOT ON ACTIVITY FILE", _
"R:\HMOs 2017\BLUE CROSS 2017\" & strMonth & "\MANUAL REPORTS\CONTRACT CODE CHANGE NOT ON ACTIVITY FILE.xlsx", True

[Michael Abrams]

The query works fine in Access - it only happenED when exporting to Excel.

Except now that I changed the code to DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml,
it works fine.

You are amazing Hans.

Can you tell me WHY this behavior?

Michael

[HansV]

Frankly, I don't know without seeing the database. But for exporting a table or query to Excel, TransferSpreadsheet is generally to be preferred above OutputTo. TransferSpreadsheet only works with tables and queries though, you can't use it to export a form or report.

[Michael Abrams]

Possibly the upgrade from Access 2007 to Access 2013.

I did have to update certain other codes.

Thanks Hans.

Michael