Fill in the Blanks

[voskouee]

I have a table with three columns

Column A, Column B, Column C

in these 3 columns i have different values and in two fo these columns there are blanks. I want to search for this blanks and fill them in with a static value. But only the two columns.
How can i do this? Update Queries?

Thanks you

 

[GaryPanic]

quite simple

column a vale x
column b blank
column c blank

set your qry to find x
this will show all of the columns
A= x
b =blanks and no blanks
c =blanks and no blanks
same qry
go to b on your qry and do is null and run this should do you blanks or even easier sort assending this should put the blanks at the top of you table you can then either enter in "raw" data or play with the qry find the first value and do < than the value

repeat on column c
probably better ways of doing this - depends upon your data and how much is blanks and if its a one off operation
if its going to be a regular operation then an update qry will probably suit this better

 

[voskouee]

i think i didnt explain correct.. this the table i have. on the debit and credit columns u see there are blanks... i want somehow to search for these blanks and fill them with the value "\fp".


Date Org FACCT CSUB P/S SOBP Cntry Debits Credits
25-Dec-06 632120600 52156000 RN00788788 2400 USD
25-Dec-06 632120600 52156000 RN00788788 6000 USD
25-Dec-06 632120600 52156000 RN00788788 3000 USD
25-Dec-06 632120600 52156000 RN00788788 3000 USD
25-Dec-06 632220600 52159020 RN00788788 2400 USD
25-Dec-06 632220600 52159020 RN00788788 3000 USD
25-Dec-06 632220600 52159020 RN00788788 6000 USD
25-Dec-06 632220600 52159020 RN00788788 3000 USD



eventually it should look like this:

Date Org FACCT CSUB P/S SOBP Cntry Debits Credits
25-Dec-06 632120600 52156000 RN00788788 2400 \fp USD
25-Dec-06 632120600 52156000 RN00788788 6000 \fp USD
25-Dec-06 632120600 52156000 RN00788788 3000 \fp USD
25-Dec-06 632120600 52156000 RN00788788 \fp 3000 USD
25-Dec-06 632220600 52159020 RN00788788 \fp 2400 USD
25-Dec-06 632220600 52159020 RN00788788 3000 \fp USD
25-Dec-06 632220600 52159020 RN00788788 \fp 6000 USD
25-Dec-06 632220600 52159020 RN00788788 \fp 3000 USD

Thanks for the reply

 

[Moniker]

Make a backup of your table first.

Make a new query. Add your backup table to it. In Design View, drag all your fields (not the asterisk) into the editor columns. In the column for Debits in the Criteria row, put Is Null. In the column for Credits in the next Criteria row down, put Is Null. Change the query type to an Update Query. In the Update To row for both Debit and Credit, put /fp. Run the query.

Note that if Debit and Credit are numeric data types, you can't update them to "/fp" as that's a Type Mismatch. You'd either need to change their data type to Text (not recommended since you probably want to perform math at some point on those fields), or you want to come up with a numeric equivalent of /fp, like updating Debit to -9999 and Credit to +9999.

 

[voskouee]

Not working

i have tried what you instructed me but it doesnt work. it says that there are 0 rows to be updated. i am sending the table and the query now to have a look if you can.

Also when i open the query in design i see olny the fields that have the Is NULL criteria.

thank you so much..

 

[Bat17]

Just do one column at a time, but Why do you want to do it?
You will be adding false data that may screw you up latter!
If you just need it for display then use the 'format' in a control to show your alternate text, something like this should do it.

#;#;#;"\FP"


HTH

Peter