Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
-
Weird "Data type mismatch in criteria expression"
I works great, thank you both for helping! Bingo- cocobingo
- Post #11
- Forum: Microsoft: Access Queries and JET SQL
-
Weird "Data type mismatch in criteria expression"
I had tried some queries with Nz(), but not inside the InStr! I'll do it right now, thanks for helping!- cocobingo
- Post #10
- Forum: Microsoft: Access Queries and JET SQL
-
Weird "Data type mismatch in criteria expression"
Well, I could do that, but this query is only meant for verification. So it would be too much pain for the result... In fact, I think that Access cannot tell what is the datatype of my function, so it cannot compare the fields. Even if I force my fields to be strings with CStr(Mid...), it...- cocobingo
- Post #8
- Forum: Microsoft: Access Queries and JET SQL
-
Weird "Data type mismatch in criteria expression"
Yes, here is an example of the a not working query : SELECT Mid(website,InStr(1,website,"www.")+4), Mid(email,InStr(1,email,"@")+1) FROM theTable WHERE (website Is Not Null) AND (email Is Not Null) AND (InStr(1,website,"www.")>0) AND...- cocobingo
- Post #6
- Forum: Microsoft: Access Queries and JET SQL
-
Weird "Data type mismatch in criteria expression"
Oops, I was to quick in answering... it still doesn't work ! :-(- cocobingo
- Post #4
- Forum: Microsoft: Access Queries and JET SQL
-
Weird "Data type mismatch in criteria expression"
Hi nicsin, thanks for answering! Well, the start value in instr is facultative and it defaults to 1 if omitted, but I check this right now and ... It Works !!! Thanks! For your 2nd question, I used +4 because Instr returns the position of the first char, so here it returns the positions of the...- cocobingo
- Post #3
- Forum: Microsoft: Access Queries and JET SQL
-
Weird "Data type mismatch in criteria expression"
Hi All! I have a strange beahaviour here, and I can't find the cause of it. First of all, don't pay attention to the ";" after "www.", it is added by the froum and does not appear in my code. I have a table (ket's say it has 2 fields, to simplify), with a website url...- cocobingo
- Thread
- Replies: 10
- Forum: Microsoft: Access Queries and JET SQL
-
JOINS! not getting correct data from query.
OK I understand it better when I see the tables... swampBooggie gave you the good answer!!! You should check his link again and see at the end of the doc. The SQL you are looking for is at the end of the page, it is called the maxconcat trick. Have fun! ;-) Arnaud -
JOINS! not getting correct data from query.
If you want all auctions for one user, try : SELECT auction.auctionid, book.title, bid.bidusername, bid.bidprice FROM auction INNER JOIN book ON (auction.isbn = book.isbn) LEFT JOIN bid ON (auction.auctionid=bid.auctionid) WHERE sellerusername = '$username' GROUP BY auction.auctionid But it... -
Help with subSelect or JOIN query
Hello! Maybe you could try : SELECT auction.auctionid, auction.bookcondition, book.edition, bid.bidprice FROM auction INNER JOIN book ON (auction.isbn = book.isbn) LEFT JOIN bid ON (auction.auctionid=bid.auctionid) WHERE sellerusername <> '$username' ORDER BY bid.bidprice DESC LIMIT 1 That... -
Proper indexes used but query "Using temporary; Using filesort"
Since your sort is dynamic, it will often be on another field than the one you are grouping by. This always causes a "Using Filesort". See : http://www.mysql.com/doc/en/ORDER_BY_optimisation.html The problem of "Using temporary" seems to be the same as the one I encounter... -
query always creates a disk-based temporary table!
SHOW VARAIBLES does not output anything like BIG_TABLES or SQL_BIG_TABLES. I'll try to ask this in a Lasso forum and see if I get any answer. Thanks for helping! Arnaud -
query always creates a disk-based temporary table!
Well... I thik I have t obe a little more precise. My mySQL server is in fact a distribution called LassoMySQL, that ships with Lasso (a web-oriented scripting language). The doc states that the LassoMySQL distribution is a MySQL version 3.23.54 (the show variables command agrees with that). It... -
query always creates a disk-based temporary table!
Thanks hvass. I checked this page before asking, and indeed, whenever I add an ORDER BY clause on another field than the one I am grouping on, the explain has a "using file sort" comment for the first table. But as you mentionned, the temporary table used for the group by does not... -
query always creates a disk-based temporary table!
Hi! I have a query that as to create a temporary table to execute. The problem is that this table is ALWAYS created on disk! First of all (because that will be your first guess), here are my server-variables: tmp_table_size=512M max_heap_table_size=512M version : 3.23.54-nt OS : win2OOO So...
