Field criteria: Is Null; there are Null values in that field; no records are returned (1 Viewer)

[Colin@Toyota]

I think the title pretty much sums it up....

I have a query where data is first sorted by user input; first field's criteria: [fieldname], then by another field's criteria: Is Null.

I know there are records containing null values in the second field, as I have run a select query with the criteria: Like "*", to make sure they are null, and not zero-length-strings.

The query is refusing to return any results...

Any ideas?

 

[KenHigg]

So when you take the Is Null criteria off of the second column it returns rows?

 

[Colin@Toyota]

So when you take the Is Null criteria off of the second column it returns rows?

I'll check

[edit]

No, it doesn't. Now isn't that strange...

I should probably give some background info... This is an update query. I ran it the first time, and it set all the blanks to the number I input, regardless of the sort criteria on the other field. So I ran a query that reversed it. The criteria for the second field became the number I had just updated the blanks to, and I set the update to: =""

Now it wont return any values, no matter what I do.

[edit #2]

Here is the SQL:

UPDATE tblSETRs INNER JOIN tblInvoices ON tblSETRs.SETRID = tblInvoices.SETRID SET tblInvoices.[TMC Invoice] = [TMC Invoice #]
WHERE (((tblInvoices.[TMC Invoice]) Is Null) AND ((tblSETRs.SETR)=[SETR]));

 

[Colin@Toyota]

For some reason the query seems to be ignoring the first criteria, and updating EVERY record with blanks in that field...

 

[KenHigg]

I hate to but I have to bug out for a while... Maybe someone else can pick here and help out.

 

[Colin@Toyota]

Thanks anyways...

 

[boblarson]

Have you tried this:

UPDATE tblSETRs INNER JOIN tblInvoices ON tblSETRs.SETRID = tblInvoices.SETRID SET tblInvoices.[TMC Invoice] = [TMC Invoice #]
WHERE ((((tblInvoices.[TMC Invoice]) Is Null) OR tblInvoices.[TMC Invoice]="") AND ((tblSETRs.SETR)=[SETR]));

 

[CraigDolphin]

Colin,

I grabbed the copy of your old SETR Invoicing Assistant db from the thread where I intially helped you with a combo box problem.

I applied the sql of your query to the tables in there (had to change the fields that your sql was using to join the two tables so that the key data matched up) and it returned 26 records.

And looking at it in the design grid, it looks as if you're typing in a number (parameter) to use for the new value in the TMC Invoice field of your tblInvoices wherever there is no value set. What is the point of the join to the SETR table in this situation since your are only limiting this to records where the SETR = the SETR?

What does this do for you that a simple ...
UPDATE tblInvoices SET tblInvoices.[TMC Invoice] = [TMC Invoice #]
WHERE (((tblInvoices.[TMC Invoice]) Is Null));
...does not?

 

[Colin@Toyota]

Have you tried this:

UPDATE tblSETRs INNER JOIN tblInvoices ON tblSETRs.SETRID = tblInvoices.SETRID SET tblInvoices.[TMC Invoice] = [TMC Invoice #]
WHERE ((((tblInvoices.[TMC Invoice]) Is Null) OR tblInvoices.[TMC Invoice]="") AND ((tblSETRs.SETR)=[SETR]));

Yes, I have tried this as well. It still ignores the criteria that filters only the records I want to update.

What is the point of the join to the SETR table in this situation since your are only limiting this to records where the SETR = the SETR?

The reason I have the two parameters is that not ALL the vendor invoices under ALL the SETRs are going to be assigned to the one TMC invoice. Each TMC invoice will contain up to 4 SETRs with all their unassigned vendor invoices.

This is why originally I wanted this query to be applied to the results of the queries that the report is based on. The report displays all the vendor invoices that have no TMC Invoice number, and I wanted to use the label on the report displaying the SETR # to pre-filter the results that would then have the TMC Invoice # applied to them.

 

[CraigDolphin]

two parameters

I only see one parameter [TMC Invoice #]. You have two criteria (one being the parameter for one field, and the other [SETR] being both a field in the table and its own criteria. The criteria you have in the SETR column equates to 'does this field = this field?' The answer, of course, is always yes.

If you intend to have user input a SETR number and a TMC Invoice number it should have something like [Input SETR #] as the criteria in the SETR field rather than the field's name.

If the criteria comes from a control on a report it might look something like:
Reports!ReportName!ControlName

This query works fine in the version of your old db that I'm using.

 

[Colin@Toyota]

The criteria you have in the SETR column equates to 'does this field = this field?' The answer, of course, is always yes.

I thought about that when I got home on the weekend. For some reason I thought I was using the "SETR_ID" field... not the "SETR" field. For some reason it didn't click until I got home and had eaten dinner. I have changed the criteria to [Enter the SETR #], and it now works perfectly.

Hope you got a chuckle out of it, as I know I did.