Hi all, the following query: IN

[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. Also, its beens suggested that I try nz to turn null to zero but im not looking to have zero in the field - i would like it to just remain blank if not data is recieved. Any ideas?

 

[nomus1]

Try using the NZ, but don't put anything in the else position.

(nz('#form.strProdID#'), nz('#form.PubID#'), nz('#form.AuthorID#'), nz('#form.memDescription#'), nz('#form.strKeywords#'))

 

[igk]

Is every field that you are updating a date field? If not, don't use '# text #' use '" & text & "'