Syntax error when exporting query to Excel

[hunoob]

Hi there Everyone!

Please try to help me as I am very desperate with my problem. I have an access database with a few tables and one query. In the query I have one calculated field (the last one). When I run the query everything runs smoothly, I get the desired results in the query. My problem is with the export to Excel. When I press the export to excel button (external data menu) I get an error saying: Syntax error (missing operator) in query expression (NamesID).
I really have no clue what to do about it! Please try to help me if you can! I am clueless and very desperate about it!

The zipped access file is attached. Please try to run the query and then export it to excel and you should see my problem.

Thank you in advance and have a nice weekend!

P.S.: I am using Access 2007 and Excel 2007.

 

[Kiwiman]

Howzit

Your Dlookup is creating an error when the Risk Owner 1 is null. If you look at your query there are a lot of errors showing. Dlookup requires a valid variable to work and is obviously more noticeable when exporting to excel.

This query works:

Owner 1 email: IIf([tbl_ICS_MainTable_Risk]![RiskOwner1] Is Null,"",DLookUp("[E-mail]","Names","[NamesID] = " & [tbl_ICS_MainTable_Risk]![RiskOwner1]))

 

[vbaInet]

I've not looked at your db but based on Kiwiman's diagnosis, you can do:

Owner 1 email: DLookUp("[E-mail]","Names","[NamesID] = " & [COLOR=Red]Nz([/COLOR][tbl_ICS_MainTable_Risk]![RiskOwner1][COLOR=Red], 0)[/COLOR])

However, why don't you just:
* Filter out the Nulls by putting Is Null under the RiskOwner1 field's criteria.

OR

* Drop the Names table in the query and OUTTER JOIN it to the via NamesID <-> RiskOwner1.

On a side note, using names like RiskOwner1 as a field name could indicate that your table is not properly normalized.

 

[hunoob]

Hi Kiwiman and vbaInet!

Thank you very very much for your replies! I tryed Kiwimans solution and it worked! Thank you for it! However I still have 2 questions, if you can please help me.
1. I do not understand one thing. When I click the export to excel after selecting the save path there is pop up window: 'Enter Parameter Value' 'ICS_report_file_2_Excel.Stand'. Why is this window popping up? What does it mean and how can I get rid of it?
2. This question is mainly a reply to vbaInet. I know that this table is not normalized properly however I have to set RiskOwner1, RiskOwner2 and so on because Excel has different columns for these users, and I really do not know how to export to excel if I choose a normalised db with multifield values. Multifield value column would be the best solution for me, however I don't know how to divide it among the excel columns.

Thank you in advance for your reply and help!

 

[vbaInet]

1. It means it can't find the ICS_report_file_2_Excel.Stand field being referred to in your query.

2. You are working with a database here so you should be building Excel to workaround Access, and not the other way round. Or use only one application to meet all your needs.

 

[hunoob]

Hi vbaInet! Thank you for your quick reply.

1. It means it can't find the ICS_report_file_2_Excel.Stand field being referred to in your query.

I do not have such field as Stand. How can I search for it?

2. You are working with a database here so you should be building Excel to workaround Access, and not the other way round. Or use only one application to meet all your needs.

Unfortunatelly my boss want everything from excel. My efforts to explain him that excel is not a database and access is lot more suitable for this were in vain. So I have to build access around excel for this time being!

Please help me in the first question because I could not find any Stand field, and I really do not know what to do.... I am totally clueless!

Thank you!

 

[vbaInet]

Are you running this export based on a query? If you are look in the Property Sheet to see if you have that field in the Filter and Order By properties.

If your boss wants everything in Excel then you should do it all in Excel

 

[hunoob]

Hi vbaInet! I found the problem! The problem was that the column header was too long: "Standard risk scenario of the operational risk" so I changed it to a shorter one (StandardRiskScen) and the problem is solved. It does not ask me to give a parameter value during the export. Thank you very much for your help!

 

[vbaInet]

Good to hear hunoob!