Help with query

[Sinoe]

I cannot get my query to return result when I enter the month of "June" as search criteria. June is listed in the table and query, every other month is returned except June.

I have attached two example for your reference.

Thanks.

 

[CJ_London]

only one example and so cut down to not be helpful - no criteria showing- suggest post your sql instead

but before you do -is June actually text or a number (6)?

 

[Sinoe]

Thanks CJ_London. Sorry about the sparse info. "June" is actual text in the table. Here is the SQL: Disclaimer: I do not write SQL statements. This was copies from the SQL View in Access.

Thanks.

SELECT TblCarriersMain.CarrierName, TblCarriersMain.CarrierIDNumber, TblCMSMonthlyReport.[FileReceived?], TblCMSMonthlyReport.DateFileReceived, TblCMSMonthlyReport.PaymentMonth, TblCMSMonthlyReport.RestatementMonth, TblCMSMonthlyReport.Notes, TblCMSMonthlyReport.Attachment
FROM TblCarriersMain INNER JOIN TblCMSMonthlyReport ON TblCarriersMain.CarrierIDNumber = TblCMSMonthlyReport.CarrierName
WHERE (((TblCMSMonthlyReport.PaymentMonth)="June"));

 

[CJ_London]

all the query builder does is build the sql string.

I can't see anything wrong with your query, the implication is if no records are returned then there are no records for June - I presume the field value is spelt 'June' and not say ' June' (i.e. with a space at the beginning or end?)

 

[Sinoe]

Thanks again CJ_London,

That's what is so strange about the whole thing. The field value is obtained from a field list and I can assure that there is no space in the name "June". I will double check but I 99.99% certain.

I really appreciate your taking the time to restore my sanity. I thought that I was going crazy there for a while.

 

[CJ_London]

And you are happy it works with 'September', 'January' etc

 

[Sinoe]

Yes. I can enter all the other months in the criteria and get results but somehow "June" is returning nothing. Strange. Really strange

 

[MSAccessRookie]

To expand on what CJ_London said, try a wildcard compare using the Like statement, and see if you can locate the word June that way.

I believe that the appropriate syntax for you would be '*June*' (or '*une*', or '*Jun*'). If you are unaware of how to make the suggested changes, I hope that the link below is helpful.

-- Rookie

http://www.techonthenet.com/access/queries/like.php

 

[Sinoe]

Thanks Rookie,

The Wild Card Compare "Like" statement did the trick. Still baffled as to why it wouldn't return any value when "June" was selected.

Thanks again.

 

[MSAccessRookie]

Thanks Rookie,

The Wild Card Compare "Like" statement did the trick. Still baffled as to why it wouldn't return any value when "June" was selected.

Thanks again.

I am glad that I was able to assist. The only thing that I can think is that there are Null or other non-visible characters before or after June in the strings. Perhaps you might want to check the length of the strings to see if they are greater than 4 characters.