Inserting blank in number field using SQL

[LARiot]

Hi,

What's the syntax for using an INSERT INTO statement for a numeric field that is sometimes blank? With the text fields it's no problem because it's surrounded with quotes. Currently the statement looks like:

UPDATE tblCanada SET [ProductSource] = 'E!', [Series Title] = 'Celebrity Profile', [Ep #] = 'CPRO-0027', [Episode Title] = 'Sharon Stone', [Genre] = 'E! Clip Shows', [Type] = '2nd Run', [CommercialRunTime] = 60, [License Term Start Date] = '9/1/2007', [License Term Expiration Date] = '8/31/2008', [First Available Date] = '10/28/1998', [Rights Expiration Date] = '10/27/2008', [License Fee] = , [Selected] = '', [Delivered] = '', [Quantity] =  WHERE [Series Title] = 'Celebrity Profile' AND [Episode Title] = 'Sharon Stone' AND [Ep #] = 'CPRO-0027'

The problem is with the ... [License Fee] = , ...

Thanks for looking at this.

 

[LARiot]

Sorry, I meant update.

 

[PHV]

Anumeric field can't be blank but either 0 (zero) or null.
Have you tried this ?
..., [License Fee] = Null, ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CaptainD]

0 with no '

But you are hard coding everything. I assume at some point you are going to get the value from somewhere else.

 

[LARiot]

PHV,

Thanks, worked perfectly.

I ran the variable through:

Public Function Bz(vPossibleBlank As Variant) As Variant

    If IsNull(vPossibleBlank) = True Then
        Bz = "Null"
    ElseIf vPossibleBlank = "" Then
        Bz = "Null"
    Else
        Bz = Trim(vPossibleBlank)
    End If

End Function

CaptainD, the values weren't hardcoded. The SQL statement was the result of print sSQL in the debug window.

 

[PHV]

A shorter way:
Public Function Bz(vPossibleBlank As Variant) As Variant
If Trim(vPossibleBlank & "") = "" Then
Bz = "Null"
Else
Bz = Trim(vPossibleBlank)
End If
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[LARiot]

Thanks PHV. You're help is always appreciated.