data mismatch error when field is blank

[leadman]

Hi all,
the following query:

INSERT INTO
Ccdatabase (strProdID, PubID, AuthorID, memDescription, strKeywords)
VALUES ('#form.strProdID#', '#form.PubID#', '#form.AuthorID#', '#form.memDescription#', '#form.strKeywords#')

has been returning the following error if either the PubID or AuthorID is passed a zero length string:

ODBC Error Code = 22005 (Error in assignment)

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression

If I put the number 1 (or any other number) in for these variables the query works fine but id like it to work whether or not it gets a value for these from the form. the fields. The datatype for both is set to long integer and i have "no" for required. Any ideas?

 

[Databaseguy]

Have you tried the NZ Function?

Tyrone Lumley
augerinn@gte.net

http://server105.hypermart.net/databaseguy

 

[leadman]

the what?

 

[Databaseguy]

NZ (Null to Zero.

If "YourField" is null, then
nz([YourField]) will return 0. Tyrone Lumley
augerinn@gte.net

http://server105.hypermart.net/databaseguy

 

[leadman]

that may help - but cant i just have the cell empty if the field is blank?