Sending Query to Excel syntax issues

[aussie92]

Hey Everyone -

I have a query that works fine and sends info to excel without a problem. When I add this one field, excel throws a fit...


max: DMax("Date_Entered","data_workflow_log","Agent_Name ='" & [agent_name] & "' and Date_Entered >= #" & Format([Date_entered],"mm/\1/yy") & "# and Date_Entered < #" & DateAdd("m",1,Format([date_entered],"mm/\1/yy")) & "# ")

The field works fine in the query, but I guess translating that to excel causes some issues. Any ideas on how to either make it so it can translate to excel, or create that type of column with excel syntax? I tried a dMax in excel and it is not the same

Thanks!!
:banghead:

 

[Pat Hartman]

What does "throw a fit" mean?

One problem you may run into is that you are using the apostrophe as the delimiter for a name. This will not work if you have names such as O'Brian. Change this to use double quotes instead.

 

[aussie92]

Thanks for the reply...

Excel simply says it cannot connect anymore. I think I solved my issue tho...I made a max sumproduct line in excel to calculate the same thing.

And good call on the ' vs. " I do not have any abnormally named fields, however.

Thanks!

 

[Pat Hartman]

You don't have any Irish agents? I'm not talking about field names, I'm talking about people's names that are stored in the Agent_name field.

 

[aussie92]

Oh ok! Sorry... And no, the agent name field is actually a log-on name so its all lowercase firstname.lastname with no spaces or apostrophes

 

[PeterF]

Your calculated field name is a reserved word, try some other name.