Data Type Mismatch in Criteria Expression problem

[bournes]

I've checked the threads and the faqs but haven't been able to get my query to work right.

The basic sql is:

SELECT DISTINCT T.AcctNum, T.Date, T.Time, T.SiteId, CDbl([Qty]) AS Qty1, tblAccountDiff.EffectiveDate
FROM (qryTransactionFieldsForQueriesWithPriceDateAndDiff AS T INNER JOIN tblPrice AS P ON T.SiteId = P.SiteID) INNER JOIN tblAccountDiff ON T.AcctNum = tblAccountDiff.AccountNum
WHERE (((tblAccountDiff.EffectiveDate)=(SELECT cdate(format( Max([EffectiveDate]), "short date")) FROM tblAccountDiff   WHERE AccountNum=T.AcctNum AND format( [EffectiveDate],"mmddyyyy")<=format(T.Date,"mmddyyyy"))) AND ((P.PriceDate)=(SELECT Max([PriceDate]) FROM tblPrice   WHERE SiteID=T.SiteID AND format( [PriceDate],"short date")<=format(T.Date,"short date"))))
ORDER BY T.AcctNum, T.SiteId;

When I run that sql it gives me the data type mismatch message.

However, if I use an account number and a date as a criteria it will run:

SELECT DISTINCT T.AcctNum, T.Date, T.Time, T.SiteId, CDbl([Qty]) AS Qty1, tblAccountDiff.EffectiveDate
FROM (qryTransactionFieldsForQueriesWithPriceDateAndDiff AS T INNER JOIN tblPrice AS P ON T.SiteId = P.SiteID) INNER JOIN tblAccountDiff ON T.AcctNum = tblAccountDiff.AccountNum
WHERE (((T.AcctNum)="102870-2") AND ((T.Date)>#5/1/2007#) AND ((tblAccountDiff.EffectiveDate)=(SELECT cdate(format( Max([EffectiveDate]), "short date")) FROM tblAccountDiff   WHERE AccountNum=T.AcctNum AND format( [EffectiveDate],"mmddyyyy")<=format(T.Date,"mmddyyyy"))) AND ((P.PriceDate)=(SELECT Max([PriceDate]) FROM tblPrice   WHERE SiteID=T.SiteID AND format( [PriceDate],"short date")<=format(T.Date,"short date"))))
ORDER BY T.AcctNum, T.SiteId;

The criteria subqueries are there because account price and diff(erentials) change over time and the right figure needs to be applied for each transaction. If I run the query without tblAccountDiff it works so somehow that extra querying must have something to do with it.

I've been playing with it for hours and I'm sure it has something to do with the criteria subqueries but can't put my finger on the solution.

All help is appreciated but I am leaving for the day soon so I probably won't see replies until tomorrow.

Bear

 

[lespaul]

Are PriceDate and EffectiveDate actual date fields? If so you shouldn't need all the formatting of the date information to compare to other formatted date information.

Additionally, having Date and Time as "Field Names" (I know they are actually just aliases from the qryTransaction.... query) is a bad practice you may want to adjust those names or add [] around each.

I would suggest that you use an "in-line" query for this. Basically you create a query that gets the AcctNum and Max Price date and then join into that. I'm not as good as others at dissecting SQL so I can just "fix" your query like those others could do.

So maybe something like this:

SELECT T.AcctNum, T.Date, T.Time, T.SiteId, CDbl([Qty]) AS Qty1, tblAccountDiff.EffectiveDate 
FROM qryTransactionFieldsForQueriesWithPriceDateAndDiff
INNER JOIN (SELECT AccountNum, Max(EffectiveDate) FROM tblAccountDiff GROUP BY AccountNum) A ON t.AccountNum = A.AccountNum
INNER JOIN (SELECT SiteID, Max(PriceDate) FROM tblPrice GROUP BY SiteID) B ON t.SiteID = B.SiteID

HTH

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[bournes]

Thank you Leslie.

I haven't been able to spend a lot of time on it today but when I did I couldn't make your sql work. After working on the punctuation I got an error message indicating a syntax error in the FROM clause of the last join. I didn't have enough time to work that out.

I am wondering though if that will assign the right price and the right differential for each transaction based on the prevailing rates at the time of the transaction.

Once again I thank you for your help and hope you have a good weekend.

Bear

 

[lespaul]

Like I said, I'm not the best at refactoring an already created query. I'm better at doing it on the fly based on seeing example data and expected results.

I tried to help you create a query all in one that would do what you need. Obviously I missed something, not surprising (and I can see part of what I missed!)

am wondering though if that will assign the right price and the right differential for each transaction based on the prevailing rates at the time of the transaction.

that's what the inline queries were trying to do. Let's say SiteID is 25 and there are 4 records in tblPrice for that SiteID. This query:

SELECT SiteID, Max(PriceDate) As MaxPriceDate FROM tblPrice GROUP BY SiteID

will return a SINGLE record for each siteID with the maximum date. If you then use an INNER JOIN to your qryTransactionFieldsForQueriesWithPriceDateAndDiff:

INNER JOIN (SELECT SiteID, Max(PriceDate) As MaxPriceDate FROM tblPrice GROUP BY SiteID) A on A.SiteID = qryTransaction...SiteID AND A.MaxPriceDate = qryTransaction...PriceDate

Now you will only get the records from qryTransaction... that have the same SiteID and PriceDate as the MaxPriceDate in the "in line" query.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[bournes]

Leslie, thanks for your continued interest and help. I am at home now and unable to try your latest suggestions but was interested to see if you had replied to the thread.

I am going to go into more detail here and give some examples of data and expected results.

First some tables with sample data:

Transaction Table [tt]

Account # SiteID Date Time Qty
111111 001 4/1/2007 12:20 148.9
111112 001 4/1/2007 14:08 89.3
111111 002 4/1/2007 14:53 259.3
111112 002 4/3/2007 08:25 136.2
111112 001 4/25/2007 10:54 145.2
111111 001 4/25/2007 11:56 22.0[/tt]


tblPrice – each site has a price that fluctuates with the market:

SiteID Price PriceDate(the day the price went into effect)
001 2.50 4/1/2007
002 2.75 4/1/2007
001 2.25 4/20/2007
002 3.00 4/20/2007

tblAccountDiff – each account has an amount added to the base price whatever the site:

Account# Diff EffectiveDate – the date the price differential went into effect
111111 .05 4/1/2007
111112 .10 4/1/2007
111112 .15 4/2/2007
111111 .10 4/20/2007

So, with that we want to determine the total price for each transaction:[tt]

Account# SiteID Date Time Qty Price Diff Total
111111 001 4/1/2007 12:20 148.9 2.50 .05 379.70

111112 001 4/1/2007 14:08 89.3 2.50 .10 232.18
111111 002 4/1/2007 14:53 259.3 2.75 .05 726.04
111112 002 4/3/2007 08:25 136.2 2.75 .15 394.98
111112 001 4/25/2007 10:54 145.2 2.25 .15 348.48
111111 001 4/25/2007 11:56 22.0 2.25 .10 51.70
[/tt]

I hope that is helpful in seeing what I need to do. Since Price and Diff vary I need to refer to the history of changes in each to determine the appropriate one for each transaction. That is why simply finding the max for each would not yield the correct figure. That is what I am trying with the subqueries. All the formatting is in there as a result of my attempts to eliminate the error messages and I’m sorry if it is confusing.

qryTransactionFieldsForQueriesWithPriceDateAndDiff is a query that includes only transactions for accounts within a specific date range and I had hoped that by eliminating any nulls the errors would go away (by not including accounts with no transactions for the date range of the report for instance) so I just substituted tblTransaction for it.

Anyway, thanks for the help and if you can glean anything from this latest post I would be very happy if you’d share it with me!

Thanks again, Bear

 

[lespaul]

I think this will do what you want:

SELECT T.Account#, T.SiteID, T.[Date], T.[Time], T.Qty, P.Price, D.Diff, [b]{CalculationforTotal}[/b] As Total 
FROM Transactions T
INNER JOIN Price P On T.SiteID = P.SiteID and T.[Date] = P.PriceDate
INNER JOIN tblAccountDiff D ON T.SiteID = D.SiteID AND T.[Date] = D.[EffectiveDate]

I can't quite figure out the formula you are using to get the Total, but you would need to plug that in above

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins