Date search key not found in any record

KMBSNT

Registered User.
Local time
Yesterday, 20:05
Joined
May 30, 2013
Messages
10
I have a query using Access 2003 accessing invoice information from a table linked from our point of sale system. The query runs perfectly displaying all info including dates if there are no specific dates indicated in the criteria. If I select a date in the criteria field I get the message "the search key was not found in any record". I used Access 97 for years and had no problem with this query. I recently changed to Access 2003 because we upgraded all computers to Windows 7. Is the search criteria format different in 2003 vs 97? Any suggestions would be appreciated.
 
Do you want to post your query? I could take a look and see if there is anything odd in the syntax.
 
The query I used to select a specific date is mm/dd/yyyy.
 
Is the point of sale system also Access or is it Sql Server or Oracle...?
Is it the same format you are using to search?

If you simply print a record from the POS do you see a Date ?
Can you make that the Date you are searching for just to show you can find it?
 
Yes, I can print a date from the POS database and it prints mm/dd/yyyy. I can also run the query without specifying a date and it will print all records with dates back to the implementation date of the POS system, but if I select one of the dates and put it in the query to only select that date I get the "search key was not found in any record" This worked fine until I switched from Access 97 to Access 2010 and got new computers with Windows 7. One thing that I just thought of is the new computers are 64 bit but I am not sure if the Access 2010 was for 32 bit or 64 bit or if this would make a difference and I can not check this until Monday.
 
If you have installed Access 2010 64 bit that could be an issue. You can certainly run 32 bit applications on a 64 bit computer.
However, from what I have read, there is little need to install Access (or Office) 64 bit. It seems the only benefit is the ability to work with very large spreadsheets. The advice given by many experienced users is to install Acc2010 32 bit version.

See post #7 here for some more info
http://www.access-programmers.co.uk/forums/showthread.php?t=243843

Pat H has experience and has commented on the 64 bit version on Acc2010.

You may find other comments re 64 bit /32 bit via search
 
You asked if there is a difference between 97 and 2003 and the answer is NO.

You started with 2003 now you are talking 2010. Can you explain.

Do you want to post your query? I could take a look and see if there is anything odd in the syntax.

This question was asked previously. You need to go to the SQL view of the query and do a copy past. Without out that it is a bit hard to help.
 
I am sorry for the confusion. The issue involves to 2003. If I run the query without a specific date in the date field it runs perfectly and lists all transactions for all dates. If I select a specific date I get the message that the search key was not found in any record. Below is the SQL view of the query.
SELECT PRODUCT.VENDOR, INVOICE.SKU, INVOICE.DATE, PRODUCT.DESC, Sum(INVOICE.QTY) AS SumOfQTY, PRODUCT.QTY, PRODUCT.GROUP, PRODUCT.COMMITTED, PRODUCT.COST
FROM INVOICE LEFT JOIN PRODUCT ON INVOICE.SKU = PRODUCT.SKU
GROUP BY PRODUCT.VENDOR, INVOICE.SKU, INVOICE.DATE, PRODUCT.DESC, PRODUCT.QTY, PRODUCT.GROUP, PRODUCT.COMMITTED, PRODUCT.COST
HAVING (((INVOICE.SKU)>"") AND ((INVOICE.DATE)=#5/25/2013#))
ORDER BY PRODUCT.VENDOR, INVOICE.SKU;
 
The first suggestion is to change the name DATE to MyDate. Date is a reserved word and may be causing some problems.

The other thing I would suggest is to change from all upper case to mixed case. There are many members who simply will not read ALL UPPER CASE as it tends to blend together and makes reading very difficult.

BTW You can search the net for Access Reserved words. I would be good for you to get to know them, or at least the main ones.
 
Thanks for the comment but I am not sure I understand your suggestion. The word DATE is not my wording but this is the word that is in the point of sale data base. All I have done with Access is select the date field I want from the POS data. As I said in an earlier post, the query runs fine as long as I do not specify a date and this query works perfectly in 97 but not in 2003.
 
Have you tried using a WHERE statement instead of HAVING?

SELECT PRODUCT.VENDOR, INVOICE.SKU, INVOICE.DATE, PRODUCT.DESC, Sum(INVOICE.QTY) AS SumOfQTY, PRODUCT.QTY, PRODUCT.GROUP, PRODUCT.COMMITTED, PRODUCT.COST
FROM INVOICE LEFT JOIN PRODUCT ON INVOICE.SKU = PRODUCT.SKU
GROUP BY PRODUCT.VENDOR, INVOICE.SKU, INVOICE.DATE, PRODUCT.DESC, PRODUCT.QTY, PRODUCT.GROUP, PRODUCT.COMMITTED, PRODUCT.COST
WHERE (((INVOICE.SKU)>"") AND ((INVOICE.DATE)=#5/25/2013#)
ORDER BY PRODUCT.VENDOR, INVOICE.SKU;

Also, what is INVOICE.SKU, a number or text? I don't understand >"" as having meaning.
 
INVOICE.DATE

It looks to me that you are using the word Date in the SQL supplied.

I could be wrong because of the upper case usage.

Did you design this in the query grid. If not it might be a good thing to try.

Have you tried using a WHERE statement instead of HAVING?

As billmeye suggested. I believe the query grid would change this.
 
The Where clause comes before the Group by,

Have tried running without the criteria on the SKU.?.

Brian
 
The Where clause comes before the Group by,

Have tried running without the criteria on the SKU.?.

Brian

I never thought of that. Good catch.

I wonder how may people with similar problem bother to reduce the SQL until it works, or use Debug.Print and view it in the ? window. I forgot what they call it.
 
Last edited:
I think it is called the "Immediate" window.

It is opened by "Ctrl G "

I had to check otherwise I would not be able to sleep.

Happy 4th June everybody.
 
I removed all fields from the query except date and still get the "search key not found in any record" message. I also created other queries using different tables and fields and have the same result. Is it possible there is a conflict between Access 2003 and Windows 7 running on a 64 bit computer?
 
This could be a BUG.

Was this Database originally written with User Level Security.

If so you need to upgrade to Microsoft Jet 4.0 SP5.

Even if it was not written with ULS I would still upgrade.
 
i must say I always associate the message - "the search key was not found " with corruption of some sort. I would copy the dbs, then try compact and repair.

open the table and see if you can see any "funny" records.
 
I would suggest the upgrade before doing what Dave said.

But certainly follow his advice after the upgrade.

Look what I found.

http://support.microsoft.com/kb/301474

I should have done this ages ago, But age has done something to my memory I think but I can't remember for sure. :)
 
I will try these suggestions tonight - hope this solves the problem.
 

Users who are viewing this thread

Back
Top Bottom