Append Query, Default Problem

[Knicks]

I am running an append query from an ODBC data source to a an Access table. My problem is that I have a couple of new or different fields in the Access table. These "text" fields have defaults of either "NO" or "YES". When I run the append, those fields end up with "0" or "-1". It seems to be treating the "yes" or "no" default as boolean.

I could run an update query but I was wondering if anyone else has ever experienced anything like this and if there isn't some smart technique to handle this when it occurs.

Thank you for any suggestions,

 

[vise]

Have you tried making those fields Yes/No fields instead of text?

 

[Knicks]

Yes

It picked up the correct default of "checked" for YES. Unfortunately, I need it as a TEXT field with a "YES" or "NO" option.

I think the ODBC part is dictating the boolean on a text field because of the default option of "YES" (for one field and "NO" for the other field). I think it just may be some weird quirk that requires a work-around update query.

 

[vise]

on the append try:

 iif([Field]=-1,"No","Yes") [\code]
assuming -1 is No.. let me know how it works out.

 

[vise]

iif([Field]=-1,"NO","YES")
[\code]

 

[Knicks]

Hmmm,

Not sure if I can do that, [exclude] is a field that only exists on the appended to table, thus its not part of the original query. Its essentially a field that I'm just trying to initialize with the added rows from the ODBC table going into appended query.

The ODBC table is called financial_transactions. The Access table is called tblImplants. I'm bringing in new records from financial_transactions appended to the tblImplants table. Exclude only exists in the Implants table, I'm just trying to set a default data value of "NO" for this field. The appended records are coming over fine, but its interpretting the default value as "0", because of the default the ODBC seems to be dictating Boolean instead of the Text field it is.

I can run your IF statement on an update query easily and it may be the simplest option at this point...

 

[vise]

Hmm.. Well it seems like you just want a default value.. Why don't you change the default value in tblImplants for [exclude]. (Right click on the table and choose Design View) and for the default value make it NO. Then Click the "lookup" tab and Change display control to "Combo Box" , Row Source Type to Value List, Row Source to YES;NO. Then close the table. Save. and Voila

 

[vise]

also make sure that the data type is Text for [exclude]

 

[Knicks]

Yes,

I did all that. The problem is the value when the appended records comes in is either -1 or 0, depending upon whether my default value is YES or NO. And this for a TEXT type field!

 

[vise]

Can you post your append query code.

 

[Knicks]

OK, this is not for the faint hearted.

INSERT INTO tblImplants ( account_number, member_code, name, service_code, transaction_date, Provider_name, Row_Number, PatientProviderCode )
SELECT qryImplantsNames.ACCOUNT_NUMBER, qryImplantsNames.MEMBER_CODE, qryImplantsNames.Name, dbo_FINANCIAL_TRANSACTIONS.SERVICE_CODE, dbo_FINANCIAL_TRANSACTIONS.TRANSACTION_DATE, dbo_FINANCIAL_TRANSACTIONS.PROVIDER_NAME, dbo_FINANCIAL_TRANSACTIONS.ROW_NMBR, qryImplantsNames.PROVIDER_CODE
FROM (dbo_FINANCIAL_TRANSACTIONS INNER JOIN qryImplantsNames ON (dbo_FINANCIAL_TRANSACTIONS.MEMBER_CODE = qryImplantsNames.MEMBER_CODE) AND (dbo_FINANCIAL_TRANSACTIONS.ACCOUNT = qryImplantsNames.ACCOUNT_NUMBER)) LEFT JOIN qryImplantRowNumberMax ON dbo_FINANCIAL_TRANSACTIONS.ROW_NMBR = qryImplantRowNumberMax.Row_Number
WHERE (((dbo_FINANCIAL_TRANSACTIONS.SERVICE_CODE)="05982y" Or (dbo_FINANCIAL_TRANSACTIONS.SERVICE_CODE)="06010z" Or (dbo_FINANCIAL_TRANSACTIONS.SERVICE_CODE)="06199u" Or (dbo_FINANCIAL_TRANSACTIONS.SERVICE_CODE)="06059A" Or (dbo_FINANCIAL_TRANSACTIONS.SERVICE_CODE)="90880" Or (dbo_FINANCIAL_TRANSACTIONS.SERVICE_CODE)="90880a" Or (dbo_FINANCIAL_TRANSACTIONS.SERVICE_CODE)="09310I" Or (dbo_FINANCIAL_TRANSACTIONS.SERVICE_CODE)="00160I" Or (dbo_FINANCIAL_TRANSACTIONS.SERVICE_CODE)="06058A" Or (dbo_FINANCIAL_TRANSACTIONS.SERVICE_CODE)="06062A" Or (dbo_FINANCIAL_TRANSACTIONS.SERVICE_CODE)="06065A" Or (dbo_FINANCIAL_TRANSACTIONS.SERVICE_CODE)="06066A" Or (dbo_FINANCIAL_TRANSACTIONS.SERVICE_CODE)="06067A" Or (dbo_FINANCIAL_TRANSACTIONS.SERVICE_CODE)="06053A" Or (dbo_FINANCIAL_TRANSACTIONS.SERVICE_CODE)="06054A" Or (dbo_FINANCIAL_TRANSACTIONS.SERVICE_CODE)="06055Y" Or (dbo_FINANCIAL_TRANSACTIONS.SERVICE_CODE)="06056A" Or (dbo_FINANCIAL_TRANSACTIONS.SERVICE_CODE)="06057A" Or (dbo_FINANCIAL_TRANSACTIONS.SERVICE_CODE)="06068A" Or (dbo_FINANCIAL_TRANSACTIONS.SERVICE_CODE)="06069A" Or (dbo_FINANCIAL_TRANSACTIONS.SERVICE_CODE)="06072A" Or (dbo_FINANCIAL_TRANSACTIONS.SERVICE_CODE)="06075A" Or (dbo_FINANCIAL_TRANSACTIONS.SERVICE_CODE)="06076A" Or (dbo_FINANCIAL_TRANSACTIONS.SERVICE_CODE)="06077A" Or (dbo_FINANCIAL_TRANSACTIONS.SERVICE_CODE)="06078A" Or (dbo_FINANCIAL_TRANSACTIONS.SERVICE_CODE)="06079A" Or (dbo_FINANCIAL_TRANSACTIONS.SERVICE_CODE)="06240Z" Or (dbo_FINANCIAL_TRANSACTIONS.SERVICE_CODE)="06040Y" Or (dbo_FINANCIAL_TRANSACTIONS.SERVICE_CODE)="06050Z") AND ((dbo_FINANCIAL_TRANSACTIONS.TRANSACTION_DATE)>#1/1/2004#) AND ((dbo_FINANCIAL_TRANSACTIONS.ACCESS_CODE)="IN") AND ((qryImplantRowNumberMax.Row_Number) Is Null));

 

[vise]

This is very strange. I created a sample database and did something similar to yours. It works..
It will only work for anything that you are going to append from now on.. so if you had -1's from some previous experiment.. then it wont change those back. You will have to run an update query for that table to change the -1's to NO.

Just to recap..

The field in tblImplants -> [exclude] has the following settings in the table:
GENERAL TAB
Field Size: 50, Default Value: "NO", Required: No, Allow Zero Length: Yes, Indexed: No, Unicode Compression: Yes, IME MOde: No Control, IME Sentence Mode: None

LOOKUP TAB
Display Control: Combo Box, Row Source Type: Value List, Row Source: YES;NO, Bound Column: 1, Column Count: 1, Column Heads: No, List Rows: 8, List Width: Auto, Limit to List: No

 

[Knicks]

I just went with the basic TEXT field as per your General Tab, with a default value of "NO".

Do you really think it matters with the lookup tab portion? I handle that portion at the form level as I tend to gain better control of error messages at the form level than table level.

I think its the ODBC portion of the database that is changing the data type upon appending, but let me do one last test and make it a lookup tab...

 

[vise]

It doesn't make a difference.. I'm just trying to make it easier when putting it on a form. As for why it put -1 instead of NO.. I am completely out of ideas, unless you are updating that table from somewhere else or have some hidden joins going on, it should work.

 

[vise]

Just to make sure, you aren't appending to the [exclude] column ever right? I didn't see it in your append query code. So I don't think there is a way for it to change the data type..

 

[Knicks]

I tested with the lookup portion filled in. Didn't matter, the NO is being changed to "0".

I think the difference is the table the records are coming from is ODBC and somehow its effecting a field that it doesn't even contain - a glitch or a bug type of action.

 

[vise]

Sorry I couldn't help. It works fine with a sample database I put together. I would suggest creating another table (tblImplants2) and trying again with an append query to that table. Maybe the access gods will come through

 

[Knicks]

Thanx for helping,

I might just try that. I have a work-around with the update query. I bet if I'd used French with OUI for yes and NON for no, it would of taken