Query not returning records

[Daryl2106]

Hi,



I have what I think is a simple query returning the names of students that have been dismissed since September 2012 using a "WithdrawnDate" field. The query pulls a lot of information from other related tables (about 6 different ones), and has two expressions.

When the criteria is set to either "Is Not Null" or a date range (which is all I need), it does not return the complete set of records based on the data that fits the criteria in the main table?

Any thoughts? Could there be some sort of join preventing all records from being returned?

Thanks in advance.

Daryl

[Rod]

Date comparisons are 'a pain in the neck,' especially so in Access SQL. I suggest you first read Allen Browne's very good treatise on the subject.

http://allenbrowne.com/ser-36.html

If you are using start/end date parameter values by enclosing them in #s then the format must be mdy.

If you are forcing your dates to be the internal numeric format using, say, CDate then the function expects the date to be in the format specified in the Windows Control Panel - not a good plan if you expect to port your db.

Using 'Is Not Nul' may not be the best idea; try the Nz function instead.

Can't help any more without seeing the code. Post as v2007 if you want my help specifically.

[Daryl2106]

Thanks Rod.

I was away for away. I wasn't aware of the confusion dates may cause MS Access; the link was really helpful. I have run into a similair problem with use a combo box with a formatted date field in the record source. When I run the append query I get an "invalid entry" or it doesn't append or something else happens that I didn't expect.

For my purposes I may change the dates to text...I'll have to think about this some more.

Thanks so much for your reply.