Checking if field is empty 1

[nycdata]

I have to search a table and see if a field is empty, and if it is, then copy contents of another field and paste it onto the empty field.

Any help?

Would appreciate any kind of help.

 

[PHV]

An update query ?
UPDATE theTable
SET field1=field2
WHERE Len(Trim(Nz(field1, "")))=0;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[scriverb]

Give this SQL a try:

Update [i][red]yourtablename[/red][/i] as A Set A.[[i][red]yourEmptyFieldName[/red][/i]] = A.[[i][blue]yourCopyFieldName[/blue][/i]] 
WHERE A.[[i][red]yourEmptyFieldName[/red][/i]] Is Null;

Post back with questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[nycdata]

That will work im sure, but im trying to run queries in the middle of code. I am using recordset for this one and it doesnt work. Is there another regular code like dlookup or something other than a query?

 

[PHV]

Take a look at the DoCmd.RunSQL method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[scriverb]

You can execute a query also by using the DoCmd.OpenQuery method. This command executes the query in the same way that the Docmd.RunSQL does within code. Advantage being a saved query is compiled.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[nycdata]

Whenever I run a query I have to createdef a query. I hate doing that. Is there a way to run sql without create def?

I tried docmd.runsql and docmd.openquery but never succeeded.

Any short example of these two that i can test and go from there?

 

[scriverb]

If you create a query in design like I demonstrated above and name and save it you can execute it with the following:

DoCmd.OpenQuery "qryYourQueryName"

If you create the SQL string in code and store it in a variable you can execute it in the following manner:

Dim strSQL as String
strSQL = "Update . . . . . "
DoCmd.RunSQL strSQL

The string variable is not necessary but makes it easier to build the SQL string. The following is an example of this method:

DoCmd.RunSQL "Update. . . . .."

Post back if you have any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[nycdata]

Thanks you all..that should help!