Difficult SQL statement, works in Access, not in ASP

[kwfrazier]

I have a difficult SQL statement that is really giving me a hard time in getting results to display on my ASP page. Here's the background:
I have an Access 2000 DB with a main data table called TBL_MasterData for a ticket system. I have a stored query that groups the table's records by PSN(serial number) and CustID and gets the Max timestamp for the PSN/CustID group. This query gives me the last entry's timestamp that I can then compare back to the main table to get the true status of the ticket (New, Open, or Closed). Using this method, I can re-open a ticket - sort of like a help desk system. This works great for me and I have no problems at all. My quandry shows up when I need to pull the MIN timestamp for Open tickets. This is needed to pull timeframe calculations on multiple Open tickets for the same group(first Open response to last, first Open response to current time, etc.) I have the query set up in Access and it works great - however, when I try to either run the query directly in ASP or accessing the stored query in the DB, it does not give results. Here's what I've got:

SQL Query to get the MAX timestamp for a ticket group...

SELECT TBL_MasterData.PSN, TBL_MasterData.CustID, Max(TBL_MasterData.Timestamp) AS MaxOfTimestamp FROM TBL_MasterData GROUP BY TBL_MasterData.PSN, TBL_MasterData.CustID;

FYI - SQL Query to retrieve the true status for a ticket group...

SELECT TBL_MasterData.PSN, TBL_MasterData.CustID, TBL_MasterData.Status, TBL_MasterData.Timestamp FROM TBL_MasterData INNER JOIN QRY_GetStatus ON (TBL_MasterData.Timestamp = QRY_GetStatus.MaxOfTimestamp) AND (TBL_MasterData.CustID = QRY_GetStatus.CustID) AND (TBL_MasterData.PSN = QRY_GetStatus.PSN);

SQL Query to get the MAX and MIN timestamp for a ticket group (same as first, just added MIN Timestamp)...

SELECT TBL_MasterData.PSN, TBL_MasterData.CustID, Max(TBL_MasterData.Timestamp) AS MaxOfTimestamp, Min(TBL_MasterData.Timestamp) AS MinOfTimestamp FROM TBL_MasterData GROUP BY TBL_MasterData.PSN, TBL_MasterData.CustID;

SQL Query to show status as well as get MAX and MIN timestamps for Open tickets...

SELECT TBL_MasterData.PSN, TBL_MasterData.CustID, TBL_MasterData.Status, QRY_GetStatusOpen.MinOfTimestamp, QRY_GetStatusOpen.MaxOfTimestamp FROM TBL_MasterData INNER JOIN QRY_GetStatusOpen ON (TBL_MasterData.PSN = QRY_GetStatusOpen.PSN) AND (TBL_MasterData.CustID = QRY_GetStatusOpen.CustID) AND (TBL_MasterData.Timestamp = QRY_GetStatusOpen.MaxOfTimestamp) WHERE (((TBL_MasterData.Status) Like 'open' & '*'));

Everything works great in Access - all but the last work in ASP. I get no errors, but I don't get any results either. So the last query gives correct results in Access but none in ASP.

I've probably really confused everyone and there's probably a better way to do this anyway. But any help, pointers, laughter is welcome.


Thanks,

Kenneth Frazier, MCSE, CCA
Network Engineer

 

[shaddow]

In ADO database access you should use "%" instead of "*" as wildcard for any character.
As i see at first look this is the only problem i see.

________
George, M

 

[kwfrazier]

HA HA HA!!!

Don't I feel like a moron !!!

That's what I get for copy/paste instead of hand coding the statement. Works great now!!!! Thanks for the extra eye!!!!!

Thanks,

Kenneth Frazier, MCSE, CCA
Network Engineer

 

[Veep]

'*' is OK in Access. It's what JET expects to see. These queries already exist in his database.

Kenneth, how are you calling that last query. Could we see some code? Also, what's that concatenation operator (&) all about? If the query brings up records when you're in Access then it should bring up records when you call to it via ASP. I'm thinking there's something funny in your code.