Replacing data with something else

[Guest]

I’m trying to use queries to replace dates in a field with a fixed answer
“yes†and to replace blank spaces with “noâ€.
I’m also trying to replace any fields with an integer 1> into “no†and 1=<
into a “yesâ€.

The problem is I’m not sure if it’s possible to do these using queries or if
I need to use SQL statements instead. And if I do, how do I do this using
Access?

 

[Guest]

Queries are SQL statements; its just a different way of viewing the same
thing.
You can write queries in SQL directly if you want.
Your query should be able to be written either way.
Are you trying to update the table or just query the table?
If you are trying to update the table, you don't want to put data into a
column which conflicts with its data type.

-Dorian

 

[Guest]

If the data type of the field you are trying to change is a date/time data
type, you will not be able to do this. In the second case, the data type of
the field containing intergers will not accept "yes" or "no".
In both of the cases, you would need either a new field added to the table,
or you could create a copy of the current table structure (copy structure
only) and change the field types in the new table. In the first case, you
could then use an Update query where you would update the new fields with
criteria that would give you the value you need. let's take the date field
for example. The Update To would be something like:
IIf(IsNull([YourDateField], "No", "Yes")
In the second case, you would use an append query with the old table as the
source and the new table as the destination.

 

[John Spencer]

Permanently? or do you just want to display the information in a query?

First, you can't store anything in a datetime field other than a date and/or
time or null
And you can't store anything in a number field other than numbers or null

So if you want to do this permanently, you will need to add a text field (or
a yes/no) field to your table and then update the field.

Calculated date field:
IIF(DateField is Null,"No","Yes")

Calculated number field:
IIF(Nz(NumberField,0)>1,"No","Yes")

You can use an UpDate query to permanently store the values, but I suggest
you consider backing up your data before making the change since the change
cannot be undone.

 

[Guest]

OK,

I tried creating another field and use an update query. I also use the
formula
'IIF(DateField is Null,"No","Yes")'
This worked after making some adjustments, however i couldn't get the second
formula for changing the numerical value to a 'yes' and 'no' value!

So far i manage to get the query to change the value to a 'Yes' if the field
'Is Not null' however I'm having problems with 'Is Null' = 'No'.
i tried creating another query however it won't let me create duplicate
queries using the same field!

 

[John Spencer]

Hard to say. Can you post the exact SQL you are trying to use AND the error
message? I would expect to see the following.

UPDATE SomeTable
SET SomeYesNoField = (Nz([SomeTable].[SomeNumberField],0) <=1)

The NZ will take care of any Null (blank) fields.

Since your number field is a number, you can store the data in that field if
you wish, but your original data will be gone
UPDATE SomeTable
SET [SomeNumberField]= (Nz([SomeTable].[SomeNumberField],0) <=1)