VBA routine to insert updated values into new table

[skydivetom]

Good morning:



I need some assistance with creating a) a VBA routine or b) an UPDATE query OR b) a MAKE TABLE query. Attached DB contains table [T_SAMPLE_DATA] including 15 *sample* (random) records.

Additional background:
- FIELD_1 will contain distinct string values for each record.
- FIELD_2 will only be partially populated for each record.

Update/Make Table Process:
- I need to end up with a new/updated table "T_TEST" where...
a. All values in FIELD_1 = "Value" AND...
b. All non-null values in FIELD_2 = "NotNull" AND...
c. All null values in FIELD_2 = "Null"

Please see table [T_TEST_WhatItNeedsToLookLike] illustrating how [T_TEST] should look like once created from [T_SAMPLE_DATA].

My question:
- What does the VBA (or query) look like to accomplish this task?

[skydivetom]

Below works just fine in VBA routine:

UPDATE T_NEW SET T_NEW.FIELD_1 = 'Value';
UPDATE T_NEW SET T_NEW.FIELD_2 = 'NotNull' WHERE (((T_NEW.FIELD_2) Is Not Null));
UPDATE T_NEW SET T_NEW.FIELD_2 = 'Null' WHERE (((T_NEW.FIELD_2) Is Null));

[orange]

TRY

SELECT  "Value" as Field_1
,iif ( T_SAMPLE_DATA.field_2 is null,"Null","NotNull") as field_2
INTO NewTable_Tom
FROM T_SAMPLE_DATA;

[skydivetom]

Orange -- wow, that's a nice variation. Thank you for assisting w/ streamlining the process.

Cheers,
Tom

[orange]

You're welcome Tom.