Query will not open in Design Mode - Invalid use of Null

[Bill.Carlson]

Access 2007,

I've created the following query from two tables:
SELECT tblTrnsctn.TxnDate, tblTrnsctn.Amount, tblCoa.AcntNmbr,
tblCoa.AcntDscriptn, tblCoa.LeadSheet, tblCoa.qbListID
FROM tblTrnsctn LEFT JOIN tblCoa ON tblTrnsctn.AccountRefListID =
tblCoa.qbListID;

This query seems to run fine, but once I save the query, I no longer
can open the query in Design Mode to edit - I can view in sql mode. I
modified the above query to return records that do not contain any
null values in any fields.

When I try to view the query in Design Mode - 'Invalid use of Null'
error message is returned.

I've searched this group, checked there were not any missing Visual
Basic References -- other posts seem to deal with the error message
upon the running of the query not at the design mode stage.

This is a simple query that we've used in the past without problems --
only apparant changes to our enviornment has been upgrading to Office
2007 and Windows Vista; and, the source of our data is Quickbooks 2007
accounting data -- I've had prior problems with Quickbooks date field
- the date being text rather than a numerical value; but I've exported
the query results to excel and the date information returns as
numerical and sorts properly by date - seems to look good.

As always, help is much appreciated,

Bill Carlson

 

[Carl Rapson]

Access 2007,

I've created the following query from two tables:
SELECT tblTrnsctn.TxnDate, tblTrnsctn.Amount, tblCoa.AcntNmbr,
tblCoa.AcntDscriptn, tblCoa.LeadSheet, tblCoa.qbListID
FROM tblTrnsctn LEFT JOIN tblCoa ON tblTrnsctn.AccountRefListID =
tblCoa.qbListID;

This query seems to run fine, but once I save the query, I no longer
can open the query in Design Mode to edit - I can view in sql mode. I
modified the above query to return records that do not contain any
null values in any fields.

When I try to view the query in Design Mode - 'Invalid use of Null'
error message is returned.

I've searched this group, checked there were not any missing Visual
Basic References -- other posts seem to deal with the error message
upon the running of the query not at the design mode stage.

This is a simple query that we've used in the past without problems --
only apparant changes to our enviornment has been upgrading to Office
2007 and Windows Vista; and, the source of our data is Quickbooks 2007
accounting data -- I've had prior problems with Quickbooks date field
- the date being text rather than a numerical value; but I've exported
the query results to excel and the date information returns as
numerical and sorts properly by date - seems to look good.

As always, help is much appreciated,

Bill Carlson

You say you modified the query to not return null values - what does the
query look like now, with those modifications?

Carl Rapson

 

[Bill.Carlson]

You say you modified the query to not return null values - what does the
query look like now, with those modifications?

Carl Rapson- Hide quoted text -

- Show quoted text -

the above sql statement is the current statement -- I removed two
fields that contained some null items. The present query returns 1300
records, all fields returning data.

bill

 

[Guest]

Are you sure neither of the join columns contains a Null?

-Dorian

 

[Bill.Carlson]

Are you sure neither of the join columns contains aNull?

-Dorian







- Show quoted text -

Hi Dorian, neither of the join columns contain a null. I've stripped
my data down for testing. I have 3 records in Copy of tblCoa and 4
records in tblTestTrnsctn.

The query runs fine, the query will toggle back and forth from design
view, sql view and dataset view prior to saving the query (qryTest).
After saving the query, 'Invalid use of Null' dialog box appears when
trying to toggle to design view.

sql text:

SELECT tblTestTrnsctn.TxnDate, tblTestTrnsctn.Amount, [Copy Of
tblCoa].AcntNmbr, [Copy Of tblCoa].qbListID,
tblTestTrnsctn.AccountRefListID
FROM tblTestTrnsctn LEFT JOIN [Copy Of tblCoa] ON
tblTestTrnsctn.AccountRefListID = [Copy Of tblCoa].qbListID;

Data:
TxnDate Amount AcntNmbr qbListID AccountRefListID
12/31/2002 100 1A0001.Oba 50000-1043460729 50000-1043460729
12/31/2002 -100 2A5001.Oba 40000-1043460729 40000-1043460729
12/31/2002 -0.66 1A0001.Oba 50000-1043460729 50000-1043460729
12/31/2002 0.66 4A1001.Oba C0000-1043460729 C0000-1043460729

Thanks for the continuing help

Bill