Problem with NULL fields 3

[albewar]

Hi!
I´m getting crazy with this:
I´m Importing some fields from an Access database table.
It works OK.
My second step is to append the fieds extracted to a new Fox .dbf, so I can work with it from a new program written in VFP.
I´m getting an error with the fields from the MDB that came empty, when traing to "REPLACE NRO WITH import33" on the new dbf:
"Field NRO does not accept null values"

I´m attempting to solve when an incoming value is NULL by several ways, but unsuccessfully.

My last attempt to replace the NULL was:
(Sorry but I´m just a trainee )

IF (rs.Fields(33).value == NULL)
   STORE "NO DISPONIBLE" TO import33
   ELSE
   STORE rs.Fields(33).value TO import33
ENDIF

Thanks a lot!


Alberto

 

[EdLeafe]

VFP has the NVL() function that will handle this for you. Try using:

STORE NVL(rs.Fields(33).value, "NO DISPONIBLE&quot TO import33

I'm also assuming that 'import33' is a memory variable and not a field. If it were a field, you need to use REPLACE to change its value, not STORE.

 

[ramani]

HI

REPLACE nro WITH NVL(rs.Fields(33).value,"NO DISPONIBLE&quot



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

When you ask VFP questions, please add VFP version.

 

[albewar]

Hi again,

Thank you guys!

But...

How do I build the IF comand?

IF <the field imported from the MDB is NULL&quot;> ?????

 STORE NVL(rs.Fields(33).value, &quot;NO DISPONIBLE&quot;) TO ; import33
ELSE 
STORE rs.Fields(33).value TO import33

ENDIF

Thanks again,

Alberto

 

[BPeisch]

The NVL function is a kind of "IF" command all by itself. You should refer to the online help file, but essentially it says use the first value if the first value isn't null, and if it is, use the second value instead.

Also, if you're trying to save this to a field, you need to use the REPLACE command, as Ramani shows. The STORE command will only save values to memory variables.



-BP

http://www.peisch.com

 

[albewar]

Helllo again,

It seems to be working!

I´ve have to convert some data format coming for example in character type to numeric as the final destination table needs. No problem with that.

But there are several fields that came in DATETIME() and the requiered field in the final table should be of the DATE() type.
Is there some way to convert some fields or variables from DATETIME to DATE?

Still Thanking you...



Alberto

 

[AirCon]

Hi Alberto,

You can use TTOD() function:

?TTOD(DATETIME())

-- AirCon --

 

[albewar]

Hi,
Thanks for your suppport!
(VFP 8)

The TTOD() conversion is working fine, up to recno() 124.
Something happen from there. I´ve cheched the MDB table called "pacientes" and I cannot see any difference on the date record type between it and the prior records. The Debug Window shows rs.Fields(8).value as a  DATETIME() type, as the other ones but when runnig it stops at RECORD 124 and give this error:
  "Fuction argument value, type or count is invalid"
If I ignore it, it stops again 4 or 5 records ahead. Could I handle thi with "ON ERROR" and record all the ocurrences without stopping the prg run?

The complete code is:

*CONVERSION DE BASE4.MDB A DBF
* Creo dos tablas temporarias para importar los datos de la tabla pacientes.dbf

* Luego haré lo mismo con la segunta tabla de la .MDB que es pacientes PAMI. Ambas
* tienen un campo relacional que es el Nro. de afiliado, con lo cual finalamente
* ya desde VFP, haremos un join de ambas para generar la tabla definitiva NOVEDADES.DBF
* que es el objetivo final. Con la novedades.dbf hay que generar una aplicación sencilla
* para filtrar todos los pacientes cuya internación estuviera vigente durante un determinado
* mes y generar reportes y estadísticas varias.

SET DEFAULT TO c:\frenopat*SET DATE BRITISH
*SET DEBUG ON 
*SET STEP ON

* rutina de Les
CLEAR
FN = GETFILE("MDB")
IF EMPTY ( FN )
   RETURN
ENDIF
ConnStr = "Driver={Microsoft Access Driver (*.mdb)};Dbq=" + FN + ";Uid=Admin;Pwd=;" 
LOCAL Conn as ADODB.Connection
Conn = CREA ( "ADODB.Connection" )
conn.Open(ConnStr)
LOCAL rs as ADODB.Recordset
rs = Conn.Execute("Select * from Pacientes")
rs.MoveFirst

* Abro la primera tabla temporaria que creé para importar los datos de la tabla pacientes.dbf

USE paci_nov EXCLUSIVE 
DELETE ALL
PACK
APPEND BLANK

DO WHILE NOT rs.EOF
   TRANSFORM(rs.Fields(9).value)
   TRANSFORM(rs.Fields(3).value)
   TRANSFORM(rs.Fields(4).value)
   TRANSFORM(rs.Fields(2).value)
   TRANSFORM(rs.Fields(33).value)
   TRANSFORM(rs.Fields(43).value)
   TRANSFORM(rs.Fields(44).value)
   TRANSFORM(rs.Fields(45).value)
   TRANSFORM(rs.Fields(36).value)
   TRANSFORM(rs.Fields(37).value)
   TRANSFORM(rs.Fields(8).value)
   TRANSFORM(rs.Fields(26).value)

* Los TRANSFORM están completados para los campos requeridos
* ---------------------------------------------------------

* guardo el contenido de los campos en variables en la misma secuencia
* Hace falta ? O directamente se puede hacer un REPLA de cada campo?   
* Parece que con sólo REPLACE va bien
  * STORE rs.Fields(9).value TO import9
  * STORE rs.Fields(3).value TO import3
  * STORE rs.Fields(4).value TO import4
  * STORE rs.Fields(2).value TO import2
  
* Aqui debo detectar si viene un campo .NULL. porque era un campo vacío. Por
* ejemplo porque la operadora olvidó de cargar el domicilio del paciente
* El caso es que más adelante cuando intenta hacer un REPLACE en la tabla temporaria
* si viene un  campo .NULL. da error

* USANDO NVL()  
* Aqui trato de REPLACE directamente a ver si NVL actúa como un "IF" en un campo que
* SE que viene vacío pero no sé si en este caso, si no viene NULL ... un REPLA directo

* Start debug
IF RECNO() = 121
SET ECHO ON
SUSPEND
ENDIF
	
	REPLACE codben WITH INT(VAL(NVL(rs.Fields(9).value,"DATO NO DISPONIBLE")))

	REPLACE t_doc WITH NVL(rs.Fields(3).value,"DATO NO DISPONIBLE")
	
	REPLACE n_doc WITH INT(VAL(NVL(rs.Fields(4).value,"DATO NO DISPONIBLE")))

	REPLACE apenom WITH NVL(rs.Fields(2).value,"DATO NO DISPONIBLE")

	REPLACE calle WITH NVL(rs.Fields(33).value,"DATO NO DISPONIBLE")

	REPLACE nro WITH NVL(rs.Fields(43).value,"DATO NO DISPONIBLE")

	REPLACE piso WITH NVL(rs.Fields(44).value,"DATO NO DISPONIBLE")

	REPLACE dpto WITH NVL(rs.Fields(45).value,"DATO NO DISPONIBLE")

	REPLACE localidad WITH NVL(rs.Fields(36).value,"DATO NO DISPONIBLE")

	REPLACE provincia WITH NVL(rs.Fields(37).value,"DATO NO DISPONIBLE")

	REPLACE f_ingreso WITH NVL(rs.Fields(8).value,"DATO NO DISPONIBLE")
	
	******** HERE IS where the problem take place********
		 STORE NVL(rs.Fields(26).value, "Dato No Disponible") TO diafecha
		 REPLACE f_egreso WITH TTOD(diafecha)
	
				
APPEND BLANK
   rs.MoveNext()
      
ENDDO
LOCAL dr as 
conn.Close()


* Cierro la tabla
USE

Alberto

 

[AirCon]

Hi ALberto,

<<
*** HERE IS where the problem take place ***
STORE NVL(rs.Fields(26).value, &quot;Dato No Disponible&quot TO diafecha
REPLACE f_egreso WITH TTOD(diafecha)
>>

The problem is, if rs.Fields(26).value is NULL, then it stored a STRING value into &quot;diafecha&quot;. TTOD(diafecha) will raise an error because diafecha is a STRING, not a DATETIME.

Try like this:
STORE NVL(rs.Fields(26).value, ctot(&quot;&quot) TO diafecha
REPLACE f_egreso WITH TTOD(diafecha)

Or you can use something like this:
STORE rs.Fields(26).value TO diafecha
diafecha = iif(ISNULL(diafecha), ctod(&quot;&quot, ttod(diafecha))
REPLACE f_egreso WITH diafecha

Now it depends on your need. If you need to store &quot;Dato No Disponible&quot; into the field, I suggest you use a character for &quot;f_egreso&quot; field, and convert the DATE into character

Hope it helps

-- AirCon --

 

[albewar]

Hi AirCon

I´m just beginning to learn a lot after all!

I´ve just noticed my error one hour before I´ve received your post.
Well, sincerely I tried a dozen of combinations with no success. I´ll check it all with your last suggestions and, report.

Kind regards,

Alberto

 

[PaulColes]

Hi Alberto
Sorry I cant help you with your initial problem but I did spot a &quot;top tip&quot; to increase the speed of your code.

replace field1 with blah,field2 with blah,field3 with blah

works out faster than

replace field1 with blah
replace field2 with blah
replace field3 with blah

this is really important when using a REPLACE ALL command as each time it is executed the wholes table is processed