Invalid Argument

[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

 

[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

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

 

[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

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