QoQ issue relating to Excel file consumed

[WolfShade]

All,

I have an app that's been working _mostly_ flawlessly for over five years.  It's a rates lookup table that uses Excel sheets as a read-only database.  There are two years of rates per line of business (ie, Air Passenger rates for FY2025 and FY2026).

A user selects a fiscal year from a dropdown, and five buttons appear for the user to select from.  A selection is made and a custom form for that FY and line of business appears.  Selections are made, and when all selections are set, AJaX sends a special code to a cffunction that consumes the related Excel file, then does a QoQ of that query to find a specific rate, based upon the code submitted, and returns the rate which is then displayed on the page for the user to see.

FY2026 rates need to be in place no later than 30 SEP.  But I have an issue that I can't explain.

There is one (so far, could be more) billing rate that exists in the related Excel file that the QoQ in the cffunction cannot find.  If I create a scratch.cfm file, put the code for consuming the Excel file at the top and cfdump the results, I get slightly more than 9900 records returned.  Then when I follow that with a QoQ using the straight string of the value and cfdump that, I get the one record I'm looking for.

I've cfmail'd the value being used in the cfqueryparam to myself, and sent it as HTML with the value in a span tag styled to have a red background.  There are no spaces, the format is correct.  But the process inside the cffunction keeps returning zero records, even though eyeballing the values of the query show that this billing code is in the results and matches what's being sent for filtering the data.

There are no hidden sheets in the Excel file, so the data is being pulled from the proper sheet.  Manually querying from my scratch file works.  But the function isn't seeing it.

I'm missing something, obviously.. just can't figure out what.

WolfShade

[WolfShade]

Apologies, I had mis-read your post.  I thought it said FY25, not FY26.. I was at lunch, when I had originally read it, and it was in tiny print on my cell phone.  Not that it's much bigger on my desktop.  😕

[BKBK · Accepted solution]

This works:

<cfset myQuery = queryNew("id,code","Integer,Varchar", [ [1,"02-15/10C >=40ft/1"], [2,"02-15/10C >=40ft/2"] ])>

<cfset variables.thisCombination='02-15/10C >=40ft/2'>

<cfquery name="qry" dbtype="query" >
select id
from myQuery
where code=<cfqueryparam cfsqltype="varchar" value="#variables.thisCombination#">
</cfquery>

<cfdump var="#qry#" >

so the issue is probably not with the QoQ

[WolfShade]

Sigh.. which is kind of what I was afraid of..  I really despise going to the customer and saying, "Well, it looks like the issue is your data."

They must have changed something in the process of generating the Excel files.  But it only affects one Excel file for only one fiscal year.

[BKBK]

It's a challenging problem indeed. Thanks for all the information and insights. I remain curious about this. Please let us know how it pans out.

[WolfShade]

Indeed.. customer just CC'd me on an email to one of her data guys, for me to work with starting Monday.. if we ever figure this out, I'll be sure to post the results, here.

Thank you for your time and attention, @BKBK .  Have a great weekend.