Invalid Argument

S

steve

I have some update queries that worked until last night. I now have some
that work and some that don't. The following is one that quit working. If
I remove (ORDERS TABLE].DISCONTINUED)=0 it runs ok. DISCONTINUED is a
Yes/No field. I have tried No, 0, <>-1, and <>Yes as criteria and nothing
works.

SELECT [MAIN TABLE].[LAST NAME], [MAIN TABLE].[FIRST NAME], [MAIN TABLE].MI,
[MAIN TABLE].[PATIENT PHONE], [ORDERS TABLE].[DATE REC], [ORDERS
TABLE].[TIME REC], [ORDERS TABLE].[RECEIVED BY], [ORDERS TABLE].[DELIVERY
METHOD], [ORDERS TABLE].[TIME SCHEDULED], [ORDERS TABLE].[ASSIGNED TO],
[ORDERS TABLE].[DATE COMPLETE], [ORDERS TABLE].ORDER, [ORDERS TABLE].[IC
NEEDED], [ORDERS TABLE].EQUIPMENT, [ORDERS TABLE].[EQUIPMENT 2], [ORDERS
TABLE].[EQUIPMENT 3], [ORDERS TABLE].[EQUIPMENT 4], [ORDERS TABLE].[ON
ORDER], [ORDERS TABLE].[PRE AUTH NEEDED], [ORDERS TABLE].[F/U DATE], [ORDERS
TABLE].[GREEN CHART], [ORDERS TABLE].LOCATION, [ORDERS TABLE].DISCONTINUED
FROM [MAIN TABLE] INNER JOIN [ORDERS TABLE] ON [MAIN TABLE].ID = [ORDERS
TABLE].MAIN_ID
WHERE ((([ORDERS TABLE].DISCONTINUED)=0) AND (([MAIN TABLE].COMPANY)=1));

The following still works today:

INSERT INTO [CRME DELIVERY MORNING REPORT TABLE] ( LOCATION, [DATE
SCHEDULED], [LAST NAME], [FIRST NAME], MI, [PATIENT PHONE], [DATE REC],
[TIME REC], [DELIVERY METHOD], [TIME SCHEDULED], [ASSIGNED TO], [DATE
COMPLETE], [IC NEEDED], EQUIPMENT, [EQUIPMENT 2], [EQUIPMENT 3], [EQUIPMENT
4], [ON ORDER], [ORDER], [PRE AUTH NEEDED], [F/U DATE], [GREEN CHART],
[RECEIVED BY] )
SELECT [ORDERS TABLE].LOCATION, [ORDERS TABLE].[DATE SCHEDULED], [Main
Table].[LAST NAME], [Main Table].[FIRST NAME], [Main Table].MI, [Main
Table].[PATIENT PHONE], [ORDERS TABLE].[DATE REC], [ORDERS TABLE].[TIME
REC], [ORDERS TABLE].[DELIVERY METHOD], [ORDERS TABLE].[TIME SCHEDULED],
[ORDERS TABLE].[ASSIGNED TO], [ORDERS TABLE].[DATE COMPLETE], [ORDERS
TABLE].[IC NEEDED], [ORDERS TABLE].EQUIPMENT, [ORDERS TABLE].[EQUIPMENT 2],
[ORDERS TABLE].[EQUIPMENT 3], [ORDERS TABLE].[EQUIPMENT 4], [ORDERS
TABLE].[ON ORDER], [ORDERS TABLE].ORDER, [ORDERS TABLE].[PRE AUTH NEEDED],
[ORDERS TABLE].[F/U DATE], [ORDERS TABLE].[GREEN CHART], [ORDERS
TABLE].[RECEIVED BY]
FROM [Main Table] INNER JOIN [ORDERS TABLE] ON [Main Table].ID = [ORDERS
TABLE].MAIN_ID
WHERE ((([ORDERS TABLE].[ASSIGNED TO])=4) AND (([ORDERS TABLE].[DATE
COMPLETE]) Is Null) AND (([ORDERS TABLE].DISCONTINUED)<>-1) AND (([Main
Table].COMPANY)=2))
ORDER BY [ORDERS TABLE].LOCATION, [ORDERS TABLE].[DATE SCHEDULED];


TIA for any help you can give me on this

Steve
 
J

Jeff Boyce

Steve

Just a thought...

If Access somehow lost a reference, you might see something like this.

Try opening a code module in design mode, clicking Tools | References and
seeing if any show up with "MISSING" prefixed.

Regards

Jeff Boyce
Microsoft Office/Access MVP


steve said:
I have some update queries that worked until last night. I now have some
that work and some that don't. The following is one that quit working. If
I remove (ORDERS TABLE].DISCONTINUED)=0 it runs ok. DISCONTINUED is a
Yes/No field. I have tried No, 0, <>-1, and <>Yes as criteria and nothing
works.

SELECT [MAIN TABLE].[LAST NAME], [MAIN TABLE].[FIRST NAME], [MAIN
TABLE].MI, [MAIN TABLE].[PATIENT PHONE], [ORDERS TABLE].[DATE REC],
[ORDERS TABLE].[TIME REC], [ORDERS TABLE].[RECEIVED BY], [ORDERS
TABLE].[DELIVERY METHOD], [ORDERS TABLE].[TIME SCHEDULED], [ORDERS
TABLE].[ASSIGNED TO], [ORDERS TABLE].[DATE COMPLETE], [ORDERS
TABLE].ORDER, [ORDERS TABLE].[IC NEEDED], [ORDERS TABLE].EQUIPMENT,
[ORDERS TABLE].[EQUIPMENT 2], [ORDERS TABLE].[EQUIPMENT 3], [ORDERS
TABLE].[EQUIPMENT 4], [ORDERS TABLE].[ON ORDER], [ORDERS TABLE].[PRE AUTH
NEEDED], [ORDERS TABLE].[F/U DATE], [ORDERS TABLE].[GREEN CHART], [ORDERS
TABLE].LOCATION, [ORDERS TABLE].DISCONTINUED
FROM [MAIN TABLE] INNER JOIN [ORDERS TABLE] ON [MAIN TABLE].ID = [ORDERS
TABLE].MAIN_ID
WHERE ((([ORDERS TABLE].DISCONTINUED)=0) AND (([MAIN TABLE].COMPANY)=1));

The following still works today:

INSERT INTO [CRME DELIVERY MORNING REPORT TABLE] ( LOCATION, [DATE
SCHEDULED], [LAST NAME], [FIRST NAME], MI, [PATIENT PHONE], [DATE REC],
[TIME REC], [DELIVERY METHOD], [TIME SCHEDULED], [ASSIGNED TO], [DATE
COMPLETE], [IC NEEDED], EQUIPMENT, [EQUIPMENT 2], [EQUIPMENT 3],
[EQUIPMENT 4], [ON ORDER], [ORDER], [PRE AUTH NEEDED], [F/U DATE], [GREEN
CHART], [RECEIVED BY] )
SELECT [ORDERS TABLE].LOCATION, [ORDERS TABLE].[DATE SCHEDULED], [Main
Table].[LAST NAME], [Main Table].[FIRST NAME], [Main Table].MI, [Main
Table].[PATIENT PHONE], [ORDERS TABLE].[DATE REC], [ORDERS TABLE].[TIME
REC], [ORDERS TABLE].[DELIVERY METHOD], [ORDERS TABLE].[TIME SCHEDULED],
[ORDERS TABLE].[ASSIGNED TO], [ORDERS TABLE].[DATE COMPLETE], [ORDERS
TABLE].[IC NEEDED], [ORDERS TABLE].EQUIPMENT, [ORDERS TABLE].[EQUIPMENT
2], [ORDERS TABLE].[EQUIPMENT 3], [ORDERS TABLE].[EQUIPMENT 4], [ORDERS
TABLE].[ON ORDER], [ORDERS TABLE].ORDER, [ORDERS TABLE].[PRE AUTH NEEDED],
[ORDERS TABLE].[F/U DATE], [ORDERS TABLE].[GREEN CHART], [ORDERS
TABLE].[RECEIVED BY]
FROM [Main Table] INNER JOIN [ORDERS TABLE] ON [Main Table].ID = [ORDERS
TABLE].MAIN_ID
WHERE ((([ORDERS TABLE].[ASSIGNED TO])=4) AND (([ORDERS TABLE].[DATE
COMPLETE]) Is Null) AND (([ORDERS TABLE].DISCONTINUED)<>-1) AND (([Main
Table].COMPANY)=2))
ORDER BY [ORDERS TABLE].LOCATION, [ORDERS TABLE].[DATE SCHEDULED];


TIA for any help you can give me on this

Steve
Click to expand...
 
S

steve

Jeff,

Thanks for the help. I found out that I had corruption in the database.
After I found it and deleted the records, everything works as before.

Again Thanks.

Steve
steve said:
I have some update queries that worked until last night. I now have some
that work and some that don't. The following is one that quit working. If
I remove (ORDERS TABLE].DISCONTINUED)=0 it runs ok. DISCONTINUED is a
Yes/No field. I have tried No, 0, <>-1, and <>Yes as criteria and nothing
works.

SELECT [MAIN TABLE].[LAST NAME], [MAIN TABLE].[FIRST NAME], [MAIN
TABLE].MI, [MAIN TABLE].[PATIENT PHONE], [ORDERS TABLE].[DATE REC],
[ORDERS TABLE].[TIME REC], [ORDERS TABLE].[RECEIVED BY], [ORDERS
TABLE].[DELIVERY METHOD], [ORDERS TABLE].[TIME SCHEDULED], [ORDERS
TABLE].[ASSIGNED TO], [ORDERS TABLE].[DATE COMPLETE], [ORDERS
TABLE].ORDER, [ORDERS TABLE].[IC NEEDED], [ORDERS TABLE].EQUIPMENT,
[ORDERS TABLE].[EQUIPMENT 2], [ORDERS TABLE].[EQUIPMENT 3], [ORDERS
TABLE].[EQUIPMENT 4], [ORDERS TABLE].[ON ORDER], [ORDERS TABLE].[PRE AUTH
NEEDED], [ORDERS TABLE].[F/U DATE], [ORDERS TABLE].[GREEN CHART], [ORDERS
TABLE].LOCATION, [ORDERS TABLE].DISCONTINUED
FROM [MAIN TABLE] INNER JOIN [ORDERS TABLE] ON [MAIN TABLE].ID = [ORDERS
TABLE].MAIN_ID
WHERE ((([ORDERS TABLE].DISCONTINUED)=0) AND (([MAIN TABLE].COMPANY)=1));

The following still works today:

INSERT INTO [CRME DELIVERY MORNING REPORT TABLE] ( LOCATION, [DATE
SCHEDULED], [LAST NAME], [FIRST NAME], MI, [PATIENT PHONE], [DATE REC],
[TIME REC], [DELIVERY METHOD], [TIME SCHEDULED], [ASSIGNED TO], [DATE
COMPLETE], [IC NEEDED], EQUIPMENT, [EQUIPMENT 2], [EQUIPMENT 3],
[EQUIPMENT 4], [ON ORDER], [ORDER], [PRE AUTH NEEDED], [F/U DATE], [GREEN
CHART], [RECEIVED BY] )
SELECT [ORDERS TABLE].LOCATION, [ORDERS TABLE].[DATE SCHEDULED], [Main
Table].[LAST NAME], [Main Table].[FIRST NAME], [Main Table].MI, [Main
Table].[PATIENT PHONE], [ORDERS TABLE].[DATE REC], [ORDERS TABLE].[TIME
REC], [ORDERS TABLE].[DELIVERY METHOD], [ORDERS TABLE].[TIME SCHEDULED],
[ORDERS TABLE].[ASSIGNED TO], [ORDERS TABLE].[DATE COMPLETE], [ORDERS
TABLE].[IC NEEDED], [ORDERS TABLE].EQUIPMENT, [ORDERS TABLE].[EQUIPMENT
2], [ORDERS TABLE].[EQUIPMENT 3], [ORDERS TABLE].[EQUIPMENT 4], [ORDERS
TABLE].[ON ORDER], [ORDERS TABLE].ORDER, [ORDERS TABLE].[PRE AUTH NEEDED],
[ORDERS TABLE].[F/U DATE], [ORDERS TABLE].[GREEN CHART], [ORDERS
TABLE].[RECEIVED BY]
FROM [Main Table] INNER JOIN [ORDERS TABLE] ON [Main Table].ID = [ORDERS
TABLE].MAIN_ID
WHERE ((([ORDERS TABLE].[ASSIGNED TO])=4) AND (([ORDERS TABLE].[DATE
COMPLETE]) Is Null) AND (([ORDERS TABLE].DISCONTINUED)<>-1) AND (([Main
Table].COMPANY)=2))
ORDER BY [ORDERS TABLE].LOCATION, [ORDERS TABLE].[DATE SCHEDULED];


TIA for any help you can give me on this

Steve
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Weekly Running Totals 3
My query only shows results where all fields are have data entered 1
Xtab criteria problem? 5
Error Message: Could Refer to more than one table 3
Specified field could refer to more than one table error message 1
Specified field could refer to more than one table error message 2
Order, Order 2
Alert message based on record selection 11

Top